Extract text before or after a character in Excel

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:

1. Apply the =FIND(“,”, A1)-1 formula to return the proper length without delimiter.
2. Extract the text before the given position using the LEFT function.
3. 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:

1. Apply the =FIND(“,”, A1)-1 formula to return the proper length without delimiter
2. Get the total length of the text using the LEN function
3. Use the RIGHT function to return the characters after the comma
4. 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

Download the practice file.