Excel substring: How to extract text from cell

Learn how to use the Excel SUBSTRING function to extract text before or after a character and find a substring between two characters.

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 text. We use built-in Excel functions and user-defined functions.

Table of contents:

1. What is a substring?
2. How to extract a substring of a certain length
   • Extract a substring from the beginning of the string (LEFT function)
   • Extract text from the end of a string (RIGHT function)
   • Extract text from the middle of the string (MID Function)
3. How to extract text before or after a character?
   • Extract text before a character
   • Extract text after a character
   • How to extract text between two instances of a character
4. How to find substring in Excel
   • Microsoft 365 functions to extract text
5. Extract a substring before a delimiter (TEXTBEFORE)
6. Extract a substring after a delimiter (TEXTAFTER)
7. Excel SUBSTRING Function (VBA Solution)
8. Extract text in Excel using pattern recognition (Flash Fill)
9. Conclusion

What is a substring?

First, let us understand what we mean by a substring. In other words, it is a part of a complete text string. For instance, if you enter “BB-246” into a cell, you have an alphanumeric string, and “BB” is part of that entry.

Excel does not have a native SUBSTRING function. Instead, it provides built-in text functions: LEFT, RIGHT, and MID. These basic string manipulation functions are vital for extracting a part of your text with a specific length. Furthermore, Excel comes with FIND and SEARCH functions for finding text parts before or after a certain character. You can extract numbers from text, replace one part with another, or search for text with a partial match. Our detailed guide will show you how to use these formulas for various tasks.

Understanding the Excel substring functions becomes simpler through practical examples, so let us dive into the examples.

How to extract a Substring of a certain length

Microsoft Excel offers various text functions for pulling out text segments of a chosen size from a cell. Each formula works based on the starting point of extraction you need.

Extract a substring from the beginning of the string (LEFT function)

You can use the LEFT function to extract characters from the left. Suppose you have a cell B3 with the text string and want to extract the first five characters.

How to extract a substring from the beginning of the string:

1. Select cell C3.
2. Enter the formula: =LEFT(B3, 5).
3. The formula extracts text from the left and takes five characters from the beginning.
4. Press Enter
5. The formula returns the first five characters from cell B3.

You can copy this formula if you have a list of text strings in column A. The LEFT function will extract the first five characters from each cell in your designated range. For example, if cell B3 contains the text “ExcelFun“, using the formula =LEFT(B3, 5) in C3 will result in “Excel“, which are the first five characters of “ExcelFun“.

Extracting text from the end of a string (RIGHT function)

Extracting a substring from the end of a string in Excel is straightforward. This action is useful for getting specific characters from the end of data in a cell. Let us say you have text in a cell (we will refer to it as B3), and your task is to extract text from the right of the string.

Steps to extract a substring from the end of a string:

1. Select cell C3.
2. Type the formula: =RIGHT(B3, 3).
3. Press Enter.
4. The formula extracts the last three characters.

If you are working with a list of text strings, you can apply the formulas to the entire column. The RIGHT function will extract the last three characters from every adjacent cell. For example, if “ExcelFun” has cell B3 and you type =RIGHT(B3, 3) into C3, the formula will return “Fun”.

Extract text from the middle of the string (MID function)

We use the MID function to extract text from the middle of a string in Excel. Using this function, you can get a substring from the middle of any text based on your specified starting point and the number of characters you want to extract.

Steps to extract a substring from the middle of a string:

1. Click on cell C2.
2. Enter the formula, =MID(B2, 2, 4).
3. Press Enter. Excel will then display the substring extracted from the middle of your original text in the cell you selected (B2).

Explanation: The MID function takes the original text, the start number, and the number of characters you want. So, if your text is in cell B2 and you wish to start extracting from the 2nd character and want four characters. The MID function provides a powerful way to extract needed text portions from the middle of your data, helping to parse strings.

How to extract text before or after a character?

The LEFT, RIGHT, and MID functions efficiently handle consistent text lengths in the examples provided. However, handling various lengths of text requires more advanced formulas. We will use the SEARCH function to locate the position of the given character. The FIND function serves this purpose well for those needing to differentiate uppercase from lowercase.

Extract text before a character

Want to extract specific parts of a text string in Excel? No problem! You can use a simple formula based on the LEFT and FIND functions.

Steps to extract text before a specific character:

1. Select cell A2
2. Type the formula: =LEFT(A1, FIND(“,”, A1)-1)
3. Press Enter
4. The formula returns the text before a specific character

In the example, we have a comma-delimited string in cell C3, and you want to extract the first word before the delimiter. The part of characters you want to extract is “apple”.

Formula:

=LEFT(C3,FIND(“,”,C3)-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 start position of the substring:

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

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

Extract text after a character

Excel uses the RIGHT, LEN, and FIND functions to get a substring after a character.

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

1. Select cell A2
2. Type the formula: =RIGHT(A1,LEN(A1)-FIND(“,”,A1)-1)
3. Press Enter
4. The formula returns the text after a specific character

In the example, you have a string in cell C3, and you want to extract the text after the first character (delimiter).

Formula:

=RIGHT(C3, LEN(C3)-FIND(“,”,C3)-1)

Evaluate the formula from the inside out:

1. RIGHT(C3, n) returns the rightmost n characters from the text in cell C3.
2. The LEN function gets the total length of the text.
3. FIND(“,”, C3) searches for the first occurrence of a comma in the text in cell C3 and returns its position (i.e., its character number counting from the start of the string). If the comma is not found in the entire string, the function returns an error.

How to extract text between two instances of a character

If you are dealing with text and need to extract a substring between two specific characters (delimiters), you can use a combination of the MID and SEARCH functions in Excel. The SEARCH function is useful as it allows for case-insensitive searches.

Steps to extract text between two characters:

1. Click cell C2
2. Type the formula: =MID(B2, SEARCH(“-“, B2) + 1, SEARCH(“-“, B2, SEARCH(“-“, B2) + 1) – SEARCH(“-“, B2) – 1)
3. Press Enter.

Explanation: This uses the MID and SEARCH functions. The MID function extracts characters from the middle of a string, starting from a position you specify. The SEARCH function locates one text string within a second text string and returns the starting position of the first text string. We use it to find the character’s position before and after the text you want to extract.

1. First, the formula finds the position of the first hyphen with SEARCH(“-“, B2). After that, it identifies the position of the second hyphen with SEARCH(“-“, B2, SEARCH(“-“, B2) + 1). This part instructs Excel to look for another hyphen occurrence after finding the first.
2. Finally, the MID function extracts the text starting from one character after the first hyphen’s position to the second hyphen’s position. Change the arguments in the SEARCH function. You can use the formula to extract text located between various characters. So, it is a powerful tool for text string manipulation in Excel.

How to find substring in Excel

In Excel, if your task is to discover whether a specific substring exists within a cell, you can use the ISNUMBER and SEARCH functions. The ISNUMBER function checks whether the SEARCH function returns a numeric value (indicating the position of the substring), confirming the substring’s presence.

Steps to find a substring in Excel:

1. Select cell C2
2. Enter the formula: =ISNUMBER(SEARCH(“apple”, B2))
3. Press Enter

Explanation: The SEARCH function locates the substring (“apple”) within the main string (B2’s content). It returns the starting position of the substring if found. For example, if B2 contains “I have an apple,” SEARCH will return the position where “apple” starts. ISNUMBER evaluates whether the value returned by SEARCH is a number.

Since SEARCH returns the numeric position of the starting point of the substring, ISNUMBER would return TRUE if “apple” is indeed a part of the text in B2. If “apple” isn’t found, SEARCH triggers an error, leading ISNUMBER to return FALSE, indicating the substring is not found.

Microsoft 365 functions to extract text

If you are a Microsoft365 subscriber, you can use the latest string manipulation functions like TEXTAFTER and TEXTBEFORE. A huge advantage of using these functions is the “instance_num” argument. You can easily extract the 2nd, 3rd, or nth instances.

Extract a substring before a delimiter (TEXTBEFORE)

Extracting text before a specific delimiter in a string is a common task when managing Excel data. The TEXTBEFORE function simplifies this process by directly fetching the text segment you need. In the example, you extract a part of a text cell B2.

Extract text before a character using the TEXTBEFORE function:

1. Select cell C2
2. Enter the formula: =TEXTBEFORE(B2, “is “)
3. Press Enter
4. The text segment before the ‘is’ substring in the text appears in cell C2.

Explanation: The TEXTBEFORE function is handy when data structures are consistent and you must extract specific information quickly. For example, getting usernames from email addresses, retrieving names before a certain symbol in a guest list, or even extracting dates from a log – each formula is simpler through TEXTBEFORE.

Extract a substring after a delimiter (TEXTAFTER)

TEXTAFTER is handy for parsing strings where you must retrieve everything following a particular delimiter. If you have a text in cell B2 like “Today is Monday” and want to extract everything after “Monday”, use the following formula.

Extract text after a character using the TEXTAFTER function:

1. Select cell C2
2. Enter the formula: =TEXTAFTER(B2, “is “)
3. Press Enter
4. The text that follows the delimiter (“is”) will appear in cell C2.

The TEXTAFTER function simplifies string manipulation formulas in Excel, saving time and effort, especially when working with large datasets or repetitive tasks.

Excel SUBSTRING Function (VBA Solution)

Creating a custom user-defined function in Excel can provide more flexibility, such as a SUBSTRING function that might not be readily available.

Below is how to create a SUBSTRING function in VBA and use it in your Excel workbook.

1. Open VBA Editor: Open the VBA Editor in Excel. You can do this by pressing Alt + F11 on your keyboard.
2. Insert a New Module: In the VBA Editor, right-click on any existing project in the “Project – VBA Project” pane. Select Insert > Module. This action will create a new module where you can write your code.
3. Copy the Function
4. Save and Close the VBA Editor: After you’ve written your function, you can save the module by clicking the save icon or going to File > Save. Then, you can close the VBA Editor and return to Excel.
5. Use the SUBSTRING Function in Excel

Now, your SUBSTRING function is available just like any other Excel function. Go to a cell and type =SUBSTRING. Add the arguments (the string, the start position, and the length of the substring you want), and Excel will return the substring based on your input.

Source code:

Function SUBSTRING(str As string, start As Integer, length As Integer) As String
    ' Check if the start position is within the string
    If start <= 0 Or start > Len(str) Then
        SUBSTRING = "Start position is out of range"
        Exit Function
    End If
    
    ' Adjust length if it extends beyond the string's actual length
    If start + length - 1 > Len(str) Then
        length = Len(str) - start + 1
    End If
    
    ' Extract the substring from the string
    SUBSTRING = Mid(str, start, length)
End Function

Extract text in Excel using pattern recognition (Flash Fill)

Extracting text in Excel 2013, 2016, 2019, or Microsoft 365 using the Flash Fill function is easy. Flash Fill can separate data in cells, especially when dealing with repetitive patterns. Enter the part of the characters you want to extract. Flash Fill recognizes the patterns in your data. The main advantage is that it uses auto-completes.

Why should you use Flas Fill to extract text?

1. Quick result without using formulas: If you want to split names into different cells, use formulas, functions, or VBA macros to parse the names. With the Flash Fill feature, Excel identifies patterns when you start typing in the next column and automatically replicates them in real-time.
2. Easy-to-use: Flash Fill is straightforward for Excel users uncomfortable with complex formulas. You start by manually entering the required output of one or two cells, and Flash Fill recognizes the pattern and “figures the output out” without requiring additional steps.
3. Time-saving and efficient: The function saves time if you have a huge amount of data that contains names. Instead of manually copying parts of the names, Flash Fill does the job with a few clicks, performing the task ASAP.
4. Real-time preview: We love this feature! The immediate feedback (live preview) lets you see the possible result. This way, you can check the result before finalizing the action.

How to use Flash Fill to extract text from a string?

1. Create a new column and enter an example.
2. Flash Fill recognizes the pattern and shows a live-preview.
3. Press Enter.
4. Excel will fill the remaining cells based on your criteria.

Wrapping things up

When you are dealing with large volumes of text data, the ability to dissect and extract specific information is crucial. In data cleansing Excel, users often encounter datasets cluttered with unnecessary characters or combined information in a single cell. Substring functions are lifesavers in cleaning out irrelevant parts and extracting the essential text parts, contributing to data accuracy.

Additional resources:

• How to separate first and last names in Excel
• How to extract number from string in Excel
• How to extract word from string in Excel: first, last, or nth