Extract Text from Cell – Excel & Google Sheets

This tutorial will demonstrate how to extract text from a cell in Excel and Google Sheets.

LEFT and LEN Functions

You can extract text from the left side of a cell in Excel by using the LEFT and LEN function.

We will walkthrough this below.

LEN Function – Count Characters in a Cell

First, we will use the LEN Function to count the number of characters in the cell:

In the next section, we will use the number of characters to trim off a certain number of characters from the right.

LEFT Function – Show Characters from the Left

The Left Function returns a certain number of characters from the left side of a cell. For the number of characters, we will use the result of the LEN Function minus the number of characters to remove (ex. 4):

Combining these functions yields the original formula.

 

RIGHT and LEN Functions

We can also extract characters from the left of a cell by using the RIGHT Function to return a certain number of characters from the right.  We use the LEN Function to count how many characters are in the cell, allowing us to remove n (ex. 5) characters from the left:

We will walkthrough this below.

LEN Function

First, we will use the LEN Function to count the number of characters in the cell:

In the next section, we will use the RIGHT function to trim off a certain number of characters from the left.

RIGHT Function

The Right Function returns a certain number of characters from the right side of a cell. For the number of characters, we will use the result of the LEN Function minus the number of characters to remove (ex. 5):

Combining these functions yields the original formula.

MID and SEARCH Functions

In the next section, we will use the SEARCH and MID functions to extract characters from the middle of a text string.

SEARCH Function

First, we used the SEARCH Function to find the position of the space between the first and last names.

We then add one onto the value returned by this formula to get the starting position of the last name.

MID Function

Next, we use the MID Function to return all the characters after the space plus 1 (the last name)

Combining these 2 functions gives us the original formula for the last name.

Extract Text After or Before a Specific Character

You can also use the LEFT, RIGHT, LEN and SEARCH functions to extract the text after or before a specific character.

Extract Text Before Character

First, we can use the SEARCH Function to find the position of the comma in the text string.

Next, we can use the LEFT function to extract the text before the position of the comma.  Note we need to minus 1 from the position of the comma so not to include the comma in our result.

Combining these 2 functions gives us the original formula for the last name.

Extract Text After Character

In addition to using the SEARCH function once again, we also use the LEN function in conjunction with the RIGHT function to get extract text after a specific character.

The LEN Function is to get the length of the text in B3, while the SEARCH function is once again used to find the position of the comma.  We then use the RIGHT function to extract the characters after the comma in the text string.

Extract Text From Middle of Text String

Next, we will discuss how to extract text from the middle of a text string

To extract text from the middle of a text string, we would need to use the RIGHT, SEARCH and LEN functions to get the text from the right side of the string, and then use the MID and LEN functions to get the text in the middle.  We are also going to incorporate the TRIM function to trim any spaces on either side of the text string.

This formula will only work if there is more than one space in the text string.  If there is only one space, an error with #VALUE would be returned.

To solve this problem, for names without middle names or initials, we can use the original formula using the MID and SEARCH Functions.

We could then combine the 2 formulas using the IFERROR Function.

We can then use the MID and LEN functions to obtain the middle name or initial.

 

Extract Text From Cell in Google Sheets

All the examples above works the same way in google sheets.