Split Text based on Character/s in Excel

Splitting of texts is one of basic tasks in excel. We often get need of splitting names, ids, address etc. from a concatenated text. To do so we target a separator text in the string and use to split each segment of string. In this article, we will learn how to split text based on one or more characters in the string.

Split text string at specific character using formula

We can split texts at specific character using function LEFT, RIGHT, LEN and FIND. There will be of course at least two parts of text split. First, the text before specific text (text on left of the character) and second, the text after the specific text (text on the right of the character).

Split text on the left of string

Let's see how we can split text on the left of the characters.

Generic Formula

=LEFT(string,FIND("specific character", string)-1)

String: The string from which you want to split text. This can be hardcoded value, a cell reference, or a formula.

Specific character: The character/s at which you want to split text. This can be hardcoded value, a cell reference, or a formula.

Let’s see an example to make things clear.

Example: Split Text on the left of string based on a character

Here I have this simple data set. In range A2:A1, we names and codes concatenated with comma (","). The name is on left of comma and I want split it from each text.

Apply above generic formula here to get text on the left of the comma in string. Copy it in B2 and drag down.

=LEFT(A2,FIND(",",A2)-1)

You can see that each name is extracted from the string precisely.

How it works?

As we know that LEFT function extracts given number of characters from given string. Now let's break down the formula inside out.

FIND(",",A2): Here FIND function looks for position of search string comma (",") in cell A2 (Georgina Schalk,30). It returns 16. Now the formula is =LEFT(A2,16-1).

Since we don't want comma (",") to be part of the extracted string, we subtract 1 from 16. (if the target characters were 2 then we would subtract 2 and so on. In short, we subtract length of the search string from the position of the search string to not include it in the result).

Finally, we will have LEFT(A2,15) and we get name extracted (15 characters from left).

Split text on the right of string

We learned how to get text on left of a specific character. Now, let's see how we can split text on the right of the specific character/s.

Generic Formula

=RIGHT(string,LEN(string)-FIND("specific character/s", string))

String: The string from which you want to split text. This can be hardcoded value, a cell reference, or a formula.

Specific character: The character/s at which you want to split text. This can be hardcoded value, a cell reference, or a formula.

Let’s see an example to make things clear.

Example: Split Text on the right of string based on a character

We will use the same data that we used in above example. This time we need to extract codes from the string. The codes are on right of the comma (","). Lets use above generic formula to split text from right.
In cell C2, write this formula and drag down to get each value.

=RIGHT(A2,LEN(A2)-FIND(",",A2))

How it works:

Let's break it down from inside. The LEN function returns the number of characters in the string.

LEN(A2) returns 18. The FIND function returns 16, as it does previously. Now the formula solves to RIGHT(A2,18-16), which again resolves to RIGHT(A2,2).

Now as we know that RIGHT function returns the given number of characters from right of a given string, hence we get 30 (2 characters from right).

So we have split text at specific text in a string using text excel function. It is dynamic. Whenever you will enter a new data in column, you will get extracted text in adjacent columns (if you copy formula in adjacent cell). Using this method you will have original text intact. If you want, you can value paste of output and delete the source data.
Split text at specific character/s using Text To Column Feature
Using above formula, we can only separate text on the first appearance of specific text in string. If there were multiple commas at random places in text, above formula would be useless. We will have to use more formulas and that would be tedious. How do we split text on each appearance of a specific character?

There's a built in feature to split text into columns. It is called Text to Column feature. Using this feature you can split original text into multiple columns, on each appearance of the separator.

Let's see how you can use it split text based on a specific character.

First thing, have enough empty columns so the right of the source text. If you have data in columns to the right of source text, they may get replaced. It is better to copy the source data in new sheet. In this way, you will have your source data intact and no other data will be affected.

Select the data that you want to split.
Press ALT>A>E. This will open Convert Text to Column Wizard box. You can also go to Data> Text to Column, in the ribbon tab.

Select Delimited and click on next.
There are few common separator option available to select, like Tab, Semicolon, Comma, Space and Other. You can select one or more separators.

Here we will split text using comma separator. We can select the comma check box, but here we will use the Other option. In other option, you can write any text to split text at. We will write a comma.

Once you write comma in the text box, excel will preview the output before you finish.

Hit finish button. Its done. Your data is splitted at specified character.

If we had used multiple separator, like space and comma both then we would have split first and last names too.

So yeah guys this how we split text based on a character or text in the string in excel. Both of the methods have their own advantages and disadvantages. In first method you create an dynamic separator that you can use multiple times by just changing the data. But when you have to split text into too many parts, excel formula method can be complex and tedious. On the other hand Text to Column method is good on this part but it is not dynamic.