Table of Contents
To extract a substring in Excel means to isolate a specific set of characters from a larger string of text. This can be useful for organizing data or performing calculations. To do so, the user must use the “LEFT”, “RIGHT”, or “MID” functions, which allow them to specify the starting position and number of characters to be extracted. For example, to extract the first 3 characters from the word “Excel”, the formula would be “=LEFT(A1,3)”, where A1 is the cell containing the word “Excel”. Other examples include extracting the last 5 characters of a phone number or extracting a specific word from a sentence. These functions can be combined with other Excel features, such as “IF” statements or “COUNT” functions, to create more complex extractions.
Extract Substring in Excel (With Examples)
You can use the following formulas to extract certain substrings from text in Excel:
Method 1: Return Substring from Beginning of String
#return first 3 characters of string in cell A2=LEFT(A2, 3)
Method 2: Return Substring from Middle of String
#return 8 characters of string in cell A2 starting at position 2 =MID(A2, 2, 8)
Method 3: Return Substring from End of String
#return last 3 characters of string in cell A2 =RIGHT(A2, 3)
Method 4: Return Substring Before Certain Text
#return all text before the string "there" in cell A2 =TEXTBEFORE(A2, "there")
Method 5: Return Substring After Certain Text
#return all text after the string "there" in cell A2 =TEXTAFTER(A2, "there")
The following examples show how to use each of these methods in practice.
Method 1: Return Substring from Beginning of String
The following screenshot shows how to use the LEFT() function to return the first three characters from cell A2:
Method 2: Return Substring from Middle of String
The following screenshot shows how to use the MID() function to return the eight characters in the middle of cell A2, starting at position 2:
Method 3: Return Substring from End of String
The following screenshot shows how to use the RIGHT() function to return the last three characters from cell A2:
Method 4: Return Substring Before Certain Text
The following screenshot shows how to use the TEXTBEFORE() function to return all of the text that comes before the string “there” in cell A2:
Method 5: Return Substring After Certain Text
The following screenshot shows how to use the TEXTAFTER() function to return all of the text that comes after the string “there” in cell A2:
Additional Resources
The following tutorials explain how to perform other common operations in Excel: