Learn how to split a text string at specific character in Excel! Apply the LEFT, RIGHT, LEN and FIND functions or use the TextLeft and the TextRight functions.
Formulas to split a text string
First, we’ll use the old-fashioned way and the generic formula:
If you want to split a text string at every single special character (underscore, question mark, space, etc.), we’ll use a formula.
The formula contains three regular excel functions. Using the LEFT, RIGHT, LEN, and FIND functions, we can split a text string if the cell contains various delimiters and a number section.
In today’s example, the formula looks like this:
If we want to split the text string and get the numbers to apply the formula:
How to evaluate the formula?
Examine the formula from the inside out! The first part of the formula
uses the FIND function and locate the first question mark in cell A2.
Because we have an extra character (?), we subtract 1, and we’ll get the text. In this case, the FIND function returns 5, and we need to split the first 4 characters.
Apply the LEFT function to extract the text in A2, starting from the left:
The result is: John
Tip: You can use another specific character, like “#”,” _”, or something else. You only need to replace the second parameter of the FIND function.
Let us see how to extract the numbers!
To split the numbers from the text string, we’ll use the RIGHT and the FIND function.
As we mentioned above the FIND locates the position of the question mark. After that, subtract this number from the total length of cell A2.
- position of the question mark = 5
- the total length of the text = 8
This formula returns with 3 (8-5), so we have only one operation left. Now the RIGHT function will strip 3 characters from the right of the text.
The result: 412
A better solution to split text string at a specific character
As usual, we can apply user-defined functions too.
The TextLeft and TextRight functions are useful if we want to split the text part and the number part of a string.
The syntax is simple:
=TextLeft(cell, delimiter) =TextRight(cell, delimiter)
The first argument is the cell reference; the second is the delimiter. You can find these useful formulas in our free Excel add-in.