Using multiple characters as delimiters in Excel Text to Column

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.

Using multiple character as delimiters in Excel Text to Column

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.

Using multiple character as delimiters in Excel Text to Column

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.

Using multiple character as delimiters in Excel Text to Column

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.

Remove characters in excel cells

Data cleanup/ scrubbing tips and tricks

Excels new (free) Get Data and Transform is

Understand how Flash Fill could have done this

CSV losing leading zeros in Excel

Line break as the delimiter in Text to Columns in Excel