When using MS Excel’s Text to column feature, you can tell Excel what to use as a delimiter in order to split text into columns. The one problem is that for the custom delimiter you are only allowed to enter 1 character. What happens if you need to use multiple characters as delimiters, for example the columns must split at the word “CODE:”?
As shown below, this single column has information in it and we would like to split it into 2 columns so that anything after the word “CODE:” is in its own column. We need to use multiple characters as delimiters.
If you try to use the Text to Column tool, you will see that you cannot enter the word “CODE:” on the ‘Other’ space. It will only allow one character. You could try just “:” but elsewhere in this data set we have standalone “:” so we want to use the multiple characters of “CODE:”.
We can trick Excel into doing this.
Replace multiple characters with a single character
The trick is to replace the multiple characters with a single character using the FIND/ REPLACE tool. You need to be careful though in choosing your character. We like to use the “|” character but it is sometimes used already. So before you decide on a character, use FIND on the dataset and see if it finds the character you want to use. We normally try the following characters – |,^,#. Key is that it must not be in the dataset.
Using FIND/ REPLACE, replace the multiple character (“CODE:”) in this example with your single character (“|” is what we will use).
As a result your column now looks like this.
Run the Text To Columns with your custom delimiter
You can now run the Text to Column in the normal way, but use your custom character as a delimiter.
So highlight the column and click on the DATA ribbon, then Text to Columns, choose Delimited and then click Next.
In the next screen, click on the ‘Other’ option, in the blank space put your custom character (“|” in our case), and you can click ‘Finish’ (there are some other articles about the next screen to keep leading zeros in Text to Columns).
The end result will be your single column, converted so that the text is in multiple columns but based on multiple characters instead of just a single character.