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.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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.