Introduction to Excel text functions - LEFT, RIGHT, MID, FIND

These four functions are your essentials for manipulating text in Excel. Let’s take a closer look…

LEFT

=left(text,num_chars)

As the name suggests, LEFT snips you a section of text starting from the left hand side. Our example in the picture above shows there are two parts; first you state which text you’re using, in this case the contents of cell B5; second you specify how many letters you want, in this case we chose 3 returning “XLC”.

RIGHT

=right(text,num_chars)

RIGHT does exactly the same as LEFT but – yep you guessed it! – starting from the right hand side. So our example shows us taking the last 5 letters from cell B5 giving “Libre”.

MID

=mid(text,start_num,num_chars)

MID takes things up a notch. It’s going to give us a number of characters from the middle of a text string, but we also need to tell it where to start. So the example above looks 3 characters along in B5, then gives you the next 3 characters “Cal”. If you changed it to =MID(B5,3,4) it would return “Cali”, whereas making it =MID(B5,4,3) would give “ali”.

FIND

=FIND(find_text,within_text,start_num)

FIND checks for one text string within another one (e.g. here we look for “Libre” within “XLCalibre”) and tells us how many characters along it appears (in this case 5). You can tell it how many characters in to start (start_num), but don’t worry about that for now, you can leave it out if you want. There are two good uses for FIND that spring to mind:

1. Is the text there? Say you want to check whether someone has used a company e-mail address or not. When the e-mail address is in cell A1, you could try this:

=FIND(“@xlcalibre.com”,A1)

If our friend Al is using his XLCalibre e-mail address, al@xlcalibre.com, this formula will return 3, as the text string “@xlcalibre.com” starts from the third character. If it can’t find the string, it will return a #VALUE! error.

2. Combine FIND with other functions to manipulate text.

So we have our list of e-mail addresses starting in A1, and we know they will all be in the format firstname@xlcalibre.com. We want to get all of the first names. We could nest the FIND in a LEFT function like this:

=LEFT(A1,FIND(“@”,A1)-1)

For our example of “al@xlcalibre.com” this will return “al” Can you see how it works? If you omit the -1 at the end it gives “al@”.

You could go further and nest this in a PROPER function to get the capitalisation right:

=PROPER(LEFT(A1,FIND(“@”,A1)-1))

This returns “Al”.