Villes Productivity Tips – divide text in Excel | There are lots of occasions when it may be good to separate text in lists. In the example below, a poor fellow has encountered a malicious virus that renamed all the users files and added a new file extension, which means that the files are unreadable unless they are renamed. (This is a real scenario encountered by one of my customers).
Something also worth mentioning is that if you saved your files in SharePoint or OneDrive, you can now stop reading and just file a case for Microsoft through the Office admin portal.
And you don’t need to be a master of Excel to be able to perform this action.
I have made up 2 lists as below. Before the attack to the right and after the attack to the left, there is a unique set of characters after the extension. The only common thing is that the new ending is also delimited with one point.
If you would export your own list of files on eg. a server, it might look like the right column below.
Create a table of data (select all cells with the values, select “make to table” from the menu to the right).
Then go to the Data tab.
Choose to get and transform from table. You only need to select any one of the cells in the table. Which one does not matter because Excel itself understands that you want to retrieve all data from the whole table.
In the new window, select the column you want to divide. Select “After delimiter”.
Select custom delimiter and fill in a point.
Select the “Limiter in the far right” option and press OK. (Here you can also put the far left or at each occurrence.)
Select “Save and load to” at the top left.
This will create a new spreadsheet in your Excel file. The one column contains the junk you should remove, the other contains the old filename as you want it to look when it’s done.
Now you have an excellent input list for a Powershell script that can go through file for file and rename these to their original name again.
This can be used for everything where you want to differentiate text content based on certain types of values.
One ending tip:
If you have a list of first and last names, but just want to keep the first names, you can select the entire list, press CTRL + H to find Search and Replace, fill in a space and an asterisk (*) in the Search for field and then leave replace with the field blank. Search and replace all so you delete all spaces including everything thereafter.
Would you rather keep all surnames put the asterisk in front of the space.
