The LEFT and RIGHT functions are great for extracting leading or trailing characters from a text string. Did you know their default setting is handy too?

Let’s say cell A1 contains the code ABC1234. If you want to extract the first three characters you can use

=LEFT(A1,3)

To extract the last four characters you would use

=RIGHT(A1,4)

It is important to note that both the LEFT and RIGHT functions return text.

So the 1234 that is returned by the above RIGHT function (cell D1 in the image below) is a text 1234, not a number. See that is left aligned.

To convert it into a number you could use

= RIGHT(A1,4)*1

This is in cell E1. This will return an error if a number can’t be created.

You can see all the examples in the image above – the formulas from the cell above are shown in row 2. The two examples on the end are explained below.

Defaults

The number used at the end of the LEFT and RIGHT functions is optional. If you leave it out, it defaults to 1.

This means that if you want to extract the first character you can use

=LEFT(A1)

And the last character

=RIGHT(A1)

This saves a couple of keystrokes.