Split Text String at specific character

split-text-string-at-specific-character

Learn how to split a text string at specific character in Excel using the LEFT, RIGHT, LEN, and FIND functions.

This article will use regular string manipulation formulas to split text at the specific character. 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)

split-the-text-part-of-a-string-using-a-delimiter

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)-1

Apply 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))

split-the-numeric-part-of-a-string-at-specific-character

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) = 412

Split 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.

textleft-and-textright-udf-functions

Syntax:

=TEXTLEFT(cell, delimiter)
=TEXTRIGHT(cell, delimiter)

The first argument is the cell reference; the second is the delimiter.