Return to Excel Formulas List

Extract Text from Cell – Excel & Google Sheets

Download Example Workbook

Download the example workbook

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

extract text from cell main

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:

extract text from cell 02

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):

extract text from cell 03

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:

extract text from cell 4

We will walkthrough this below.

LEN Function

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

extract text from cell 05

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):

extract text from cell 06

Combining these functions yields the original formula.

extract text from cell 4

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.

extract text from cell 07 00

SEARCH Function

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

extract text from cell 07 01

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)

extract text from cell 07 02

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

extract text from cell 07 00

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 from cell 08

Extract Text Before Character

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

extract text from cell 09

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.

extract text from cell 09 01

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

Extract Text After Character

extract text from cell 08

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.

extract text from cell 10

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.

extract text from cell 12

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

extract text from cell 10 MID

 

Extract Text From Cell in Google Sheets

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

extract text from cell Google