Extract parts of the text from cells

Free Preview

Sometimes you need to split a cell into various components e.g. the first 3 characters and the last 3 characters need to be extracted to their own cells.

To follow along go to 26.2

The LEFT, RIGHT, MID and LEN functions are all useful in separating a cell into component parts. It is in effect the opposite of the CONCATENATE function.

The LEFT function extracts a certain number of characters from the left hand side of the cell. So in the example below we are asking Excel to use cell A5 (JHB-55568-001) and extract the first 3 characters (JHB in this case)

The RIGHT function extracts a certain number of characters from the right hand side of the cell. So in the example below we are asking Excel to use cell A5 (JHB-55568-001) and extract the last 3 characters (001 in this case).

The MID (stands for middle) function extracts a certain number of characters from somewhere within the cell. So in the example below we are asking Excel to use cell A5 (JHB-55568-001), start at character 5,  and extract the next  5 characters from there (55568 in this case).

LEFT_RIGHT_MID_LEN

The LEN function stands for length and tells you how many characters make up a cell. Once you know this about a cell you can generally use it in the LEFT, RIGHT and MID functions.

Back to: Intermediate Excel Course > Text Functions