Learn how to split a text string at specific character in Excel using the LEFT, RIGHT, LEN, and FIND functions.
This article will show various methods to split text at the specific character using regular string manipulation formulas. Furthermore, you will learn how to use the TEXTLEFT and TEXTRIGHT user-defined functions.
Today’s tutorial is a part of our definitive guide on Excel Formulas.
Generic formula to split a text string
First, we’ll use the old-fashioned way with built-in Excel functions.
=LEFT(text, FIND("specific_character",text)-1)Explanation
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. For example, we can split a text string using the LEFT, RIGHT, LEN, and FIND functions if the cell contains various delimiters and a number section.
In the example, the formula looks like this:
=LEFT(A2,FIND("?",A2)-1)
Evaluate the formula from the inside out:
=FIND("?", A2)The formula above uses the FIND function and locates the first question mark in cell B3.
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.
=FIND("?", A2)-1Apply the LEFT function to extract the text in A2, starting from the left:
=LEFT(A2, FIND("?", A2)-1)The result is “John”.
How to extract numbers from a text string?
We’ll use the RIGHT and the FIND function to split the numbers from the text string.
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.
LEN(B3)-FIND("?",B3))- position of the question mark is 5
- the total length of the text is 8
=RIGHT(B3, LEN(B3)-FIND("?",B3))
This formula returns with 3 (8-5), so we have only one operation. Now the RIGHT function will strip three characters from the right of the text.
=RIGHT(B3, 3) = 412Split text string at a specific character using TEXTLEFT and TEXTRIGHT
The TextLeft and TextRight functions are useful if we want to split the text and the numeric parts of a string. Our user-defined function library, the DataFX function add-in, supports both functions.
Syntax:
=TextLeft(cell, delimiter)
=TextRight(cell, delimiter)
The first argument is the cell reference; the second is the delimiter.
Related Formulas and resources:
