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

=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.

l_r

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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

2 thoughts on “The Handy Functions in Excel – LEFT and RIGHT

  1. Hi Neale

    Thanks for all the great tips.

    A couple of other functions I have found useful are MID and TRIM (removes leading and trailing spaces as well as >1 space between words).

    • Hi David
      Thanks for the comment. Yes those functions are handy too – I have a free webinar recording that covers all the text functions – check out out my free webinars page for details.
      Regards
      Neale