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:
Excel: How to Remove First and Last Character from String
Excel: How to Find First Letter in a String
Excel: How to Find First Number in Text String
