Sometimes when pasting data from the web, our values come accompanied by special characters. Other times, they crowd up a single cell.
In both cases, you can choose from a range of Excel functions to extract text from a cell.
Excel has three dedicated functions to pull such substrings—LEFT, RIGHT, and MID. While these functions are usually enough, you will need to nest a few additional functions for more complex extractions.
Extract Text from the Left
| Function Used | Type | Syntax |
| LEFT | TEXT | =LEFT(text, [num_chars]) |
As the name suggests, the LEFT function in Excel is used to extract text from the left. The function will extract the exact number of characters you specify in the ‘num_chars’ section of the formula.
We have the student ID of 10 students in column A.
The first three texts from the cell specify the batch of these students. Let’s use the LEFT function to extract the batch of all 10 students.
In cell D2, enter the following formula:
=LEFT(A2,3) // Extracts three characters from the left of A2.
Then, use flash fill to apply the formula to the entire range.
Extract Text from the Right
| Function Used | Type | Syntax |
| RIGHT | TEXT | =RIGHT(text,[num_chars]) |
The RIGHT function is used to extract text from the right. You must enter the number of characters you wish to extract from the right in the ‘num_chars’ section of the argument.
Column A contains the serial number of 10 delegates.
The last two characters of the ID contain the delegate’s name initials. Here’s how we will be using the RIGHT function to extract the initials:
=RIGHT(A2,2) // Extracts two characters from the right of A2.
Apply the formula to the entire range.
Extract Text from a Specific Location
| Function Used | Type | Syntax |
| MID | TEXT | =MID(text,start_num,num_chars) |
Say, you don’t want to extract substrings from either left or right but the middle. In such a case, use the MID function.
In MID, you must specify the position of the substring you wish to extract in the ‘start_num’ section. Then, the number of characters you want MID to extract in the ‘num_chars’ section.
The sheet below holds a list of code names in column A.
We’ve been assigned to clean the data by excluding the first and last three characters from the string. In cell G2, here’s how we constructed a formula using the MID function:
=MID(A2,4,5) // Extracts five characters from the fourth position of A2.
Use Flash Fill to paste the formula to the entire range.
Extract Text from Numbers
| Function Used | Type | Syntax |
| TEXTJOIN | TEXT | =TEXTJOIN(delimiter,ignore_empty,text1…) |
| IF | LOGICAL | =IF(logical_test,[value_if_true],[value_if_false]) |
| ISERROR | BOOLEAN | =ISERROR(value) |
| MID | TEXT | =MID(text,start_num,num_chars) |
| SEQUENCE | DYNAMIC ARRAY | =SEQUENCE(rows,[columns],[start],[step]) |
| LEN | INTEGER | =LEN(text) |
You may find the need to only extract texts in alphanumeric characters. For such scenarios, you will have to use the TEXTJOIN, IF, ISERROR, MID, and SEQUENCE functions to construct a formula.
Column D holds 10 addresses. Some of these addresses include postal codes which, we’re looking to remove.
Here’s how we constructed a formula to remove such numbers from our final address:
=TEXTJOIN("", TRUE, IF(ISERROR(MID(D2, SEQUENCE(LEN(D2)), 1)*1), MID(D2, SEQUENCE(LEN(D2)),1),"")) // Joins characters that ISERROR returned TRUE for.
We’ve covered an entire article on How to Remove Numbers from Text. If you’re looking for more ways to extract text from numbers, check this article out.
Extract Text Before a Specific Character
| Function Used | Type | Syntax |
| LEFT | TEXT | =LEFT(text,[num_chars]) |
| FIND | INTEGER | =FIND(find_text,within_text,[start_num]) |
When we copy-paste from the web, our data may come accompanied by a few special characters. If they follow a pattern, create a formula using the FIND function to extract text before the character.
The FIND function will return the position of the character inside the next. We can then use the LEFT function to only extract the text that comes before the specified character.
Column A is supposed to be the first names of 20 employees. However, the names are followed by a series of special characters on the right.
We could’ve used the LEFT function to extract characters from the left. However, as the names of each individual are of varying lengths, this isn’t possible.
Here’s the formula we used by nesting FIND inside the LEFT function:
=LEFT(A2,(FIND("*",A2)-1)) // Extracts values before the “*” character.
Extract Text After a Specific Character
| Function Used | Type | Syntax |
| RIGHT | TEXT | =RIGHT(text,[num_chars]) |
| FIND | INTEGER | =FIND(find_text,within_text,[start_num]) |
| LEN | INTEGER | =LEN(text) |
Now, what if the text we’re looking to extract comes after a specific character?
To create this formula, we will need RIGHT, FIND, and LEN. Using FIND, we will first identify the position of the character we want to extract data after. Then, we will subtract it by the total length of the text. Finally, we will enter the end value in the [num_chars] section of the RIGHT formula.
Here’s how we used the formula to extract characters after the letter “Q” from the values in column A:
=RIGHT(A3,(LEN(A3)-FIND("Q",A3))) // Extracts text after “Q” in the text.
Extract Text Between Characters
| Function Used | Type | Syntax |
| MID | TEXT | =MID(text,start_num,num_chars) |
| FIND | INTEGER | =FIND(find_text,within_text,[start_num]) |
The data in column A holds the student ID of 10 individuals. Between the “/” symbols, is the batch number of each student.
To extract the text between the slash symbol, we will be entering the following formula:
=MID(A3,(FIND("/",A3)+1),FIND("/",A3,FIND("/",A3)+1)-FIND("/",A3)-1)