The Handy Functions in Excel – LEFT and RIGHT

Simple Defaults

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


To extract the last four characters you would use


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.


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


And the last character


This saves a couple of keystrokes.

Please note: I reserve the right to delete comments that are offensive or off-topic.