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