Learn how to **extract text before or after a character in Excel** using the FIND, LEN, LEFT, and RIGHT functions.

With the help of **string manipulation formulas**, you can perform various actions in Excel. This article will show you how to get a part of text from a cell containing a long text. We’ll use **built-in Excel functions** and **user-defined functions** too.

## How to Extract Text Before Character using the FIND, LEN, and RIGHT Functions

Here are the steps to extract a text before a character:

- Apply the =FIND(“,”, A1)-1 formula to return the proper length without delimiter.
- Extract the text before the given position using the LEFT function.
- Formula: =LEFT(A1, FIND(“,”, A1)-1)

### Explanation

Evaluate the formula from the inside out. First, you need to use the FIND** **function to find the position of the delimiter. In the example, cell C3 contains the text string. To find the comma position in the text, use the following formula:

`=FIND(",",C3) = 6`

Because you don’t need to extract the delimiter, subtract the total length with one character:

`=FIND(",",C3)-1 = 5`

The LEFT function uses two arguments to return a specified number of characters from the start of a text string.

`=LEFT(C3,FIND(",",C3)-1) = LEFT(C3,5)`

## How to Extract Text After Character using the FIND, LEN, and RIGHT Functions

Here are the steps to extract a text after a character:

- Apply the =FIND(“,”, A1)-1 formula to return the proper length without delimiter
- Get the total length of the text using the LEN function
- Use the RIGHT function to return the characters after the comma
- Formula: =RIGHT(A1,LEN(A1)-FIND(“,”,A1)-1)

### Explanation

Some text and a descriptive picture.

### Microsoft 365 functions to extract text

If you are a Microsoft365 subscriber, you can use the latest string manipulation functions like TEXTAFTER and TEXTBEFORE.

```
=TEXTBEFORE(text,delimiter,instance_num,match_mode,match_end,if_not_found)
=TEXTAFTER(text,delimiter,instance_num,match_mode,match_end,if_not_found)
```

A huge advantage of using these functions is the “**instance_num**” argument. You can easily extract the 2nd, 3rd, or nth instances.

### User-defined functions

To support non-Microsoft365 users, here is the equivalent code (**user-defined function**) to extract text before a character. To implement the new function, press Alt + F11 to open the VBA editor. Create a new module, paste the code, and save the Workbook in an xlsm format.

```
Function TEXTBEFORE_365(text As String, delimiter As String, Optional instance_num As Long = 1, Optional match_mode As Long = 0, Optional match_end As Long = 0, Optional if_not_found As Variant) As Variant
Dim arr As Variant
Dim count As Long
If Len(text) = 0 Or Len(delimiter) = 0 Then
TEXTBEFORE_365 = if_not_found
Exit Function
End If
If match_mode = 1 Then
text = UCase(text)
delimiter = UCase(delimiter)
End If
arr = Split(text, delimiter)
count = UBound(arr)
If match_end = 1 Then
If Right(text, Len(delimiter)) = delimiter Then
count = count + 1
End If
End If
If instance_num > 0 And instance_num <= count Then
TEXTBEFORE_365 = arr(instance_num - 1)
ElseIf instance_num < 0 And (-instance_num) <= count Then
TEXTBEFORE_365 = arr(count + instance_num)
Else
TEXTBEFORE_365 = if_not_found
End If
End Function
```