Extract text in Excel with the LEFT, RIGHT and MID functions

If you need to extract a fixed part of a text, or have a range of consistent text you need to extract a part of then you can use the LEFT, RIGHT and MID functions in Excel to do this.

Download File

If you would like to follow along download the attachment below.

Extract text in Excel with the LEFT, RIGHT and MID functions.xlsx

Download XLSX • 15KB

What are the LEFT, RIGHT and MID functions

The LEFT, RIGHT and MID functions are text functions in Excel that extract a specific and fixed part of a text string in a cell, which can be useful for data cleaning.

The LEFT and RIGHT functions have similar syntax:

=LEFT(text, num_chars)

=RIGHT(text, num_chars)

• text: The original text string you want to extract characters from.

• num_chars: The number of characters to extract from the left for the LEFT function and

  right for the RIGHT function

The MID function works in a similar way but has a slightly different syntax:

=MID(text, start_num, num_chars)

• text: The original text string.

• start_num: The position where extraction begins (1 = first character)

• num_chars: The number of characters to extract after the start_num

Using the LEFT function

The LEFT function works by taking a fixed number of characters from the left of a text string in a cell.

To extract the relevant characters select the cell with text in it for the text parameter and enter the number of characters you want to extract in the num_chars parameter.

Using the RIGHT function

The RIGHT function works by taking a fixed number of characters from the right of a text string in a cell.

To extract the relevant characters select the cell with text in it for the text parameter and enter the number of characters you want to extract in the num_chars parameter.

Using the MID function

The MID function works by taking a fixed number of characters from within the text string.

To extract the relevant characters select the cell with text in it for the text parameter, enter the number of the character you want to start extracting from in the start_num, and the number of characters you want to extract in the num_chars parameter.

Using the LEFT, RIGHT and MID functions across a range

These functions can be used to extract text from a range, not just a single cell.

This can be done by selecting a range of continuous cells in the text parameter.

The output will then spill across the range selected.

When doing this it is important to make sure that your data is consistent, if not the output may only be correct for some.

In the example below the output for row 2 is correct, but all other rows have not correctly had the city, region or country extracted in full.

If you are trying to extract text from text where there is consistency in format e.g. words are separated by “-“, but the words are different lengths, then using TEXTBEFORE and TEXTAFTER will be more helpful.

Click here to learn more about TEXTBEFORE and TEXTAFTER.

Conclusion

If you have consistent data or want to extract a fixed part of a text value in a cell then using the LEFT, RIGHT and MID functions will do this for you.

The functions can be used across a range of cells but will still only extract the fixed amount, even if the data in the cells is inconsistent.

For text with a common delimiter it can be better to use the TEXTBEFORE and TEXTAFTER functions.