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

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.

Syntax:

```
=TextLeft(cell, delimiter)
=TextRight(cell, delimiter)
```

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

**Related Formulas and resources:**