How To Extract text from cells

There may be times when you need to extract certain characters from text within a cell. For example, a general ledger code such as S-20-1010 may be comprised of a character representing the region, a 3-character subcode representing the department and a 4-character subcode representing the expense type. If you are required to extract each of these subcodes into separate columns, using Excel’s string functions can save you a whole lot of time.

The LEFT function returns a desired number of characters from the beginning of a text string. The RIGHT function returns a desired number of characters from the end of a text string. The MID function returns a desired number of characters starting at a specified position within the text string.

Assuming your general ledger codes are in column B and you want to extract the subcodes to columns C, D and E.
Excel Text functions sample

LEFT Function: In our example, to extract the region code, which is the left-most character, you can use the LEFT function.

=LEFT(text_string, #_of_characters)

In cell C2 you would enter the formula =LEFT(B2,1) and this would return the region code W. Here the region code is just one character. In other cases you can extract any number of characters by changing the #_of_characters attribute in the formula.

RIGHT Function: To extract the expense code, which is comprised of the 4 right-most characters, you can use Excel’s RIGHT function.

=RIGHT(text_string, #_of_characters)

In cell D2 you would enter the formula =MID(B2,4) and this would return the expense code 5095.

MID Function: To extract the department code, which is in the middle of this text string, you can use Excel’s MID function

=MID(text_string, start_pos, #_of_characters)

The difference with the MID function is that you specify at which character position in the text string you start. In cell E2 you would enter the formula =MID(B2,3,3) and this would return the expense code 320.