Tag Archives: mid

XL FORMULA FOCUS: LEFT, RIGHT, MID, FIND (an introduction to Excel text functions)

XLCalibre Basic Excel Text Functions - LEFT, RIGHT, MID, FIND
XLCalibre Basic 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)

[showmyads]

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)

[showmyads]

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)

[showmyads]

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.

[showmyads]

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:

[showmyads]

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

This returns “Al”.

For more info on PROPER and more text manipulation, check out our post on PROPER, UPPER, LOWER and LEN.

Splitting names

Today on LinkedIn somebody posed the following question (for anonymity I have changed any names given):

Sorting by Last Name

I have a list of 2,000 names by First, Middle, Last, Suffix (Jr or I,II, or III) I need to be able to Sort by last name. I know how to use “Text to Column” but unfortunately there is not a consistancy in the names, I have some that have just First and Last name, I have some that may have a space in their last name such as “De Souza” or “John Dave Jones Jr II” any suggestions.

The issue wasn’t really about sorting, which would be easy with autofilters (CTRL+Shift+L to apply autofilters). The problem was separating out the parts of the names, and lining them up in the right columns. Usually text to columns would fix this, but doing it with the different formats mentioned in the post would have left last names in various different columns.

I find this is a common predicament in HR, where lists of names pulled from various sources are full of inconsistencies, and therefore hard to manipulate or ‘plug in’ to other systems or spreadsheets.

My solution was not particularly elegant. You are rarely afforded the time to create the perfect single formula answer that covers all angles. But it does show you some text-manipulation functions at work.

I e-mailed the file to the poster. They mentioned 5 exceptions that it had not worked for, and they all included initials (I hadn’t thought of this possibility). This leads to my next point: testing. If at all possible you should use your spreadsheets from beginning to end with real data to check if they’re working before they go to your customer. Even better, get someone else to do this for you, as its easy to miss something when you’re buried in details.

Here you can access the spreadsheet that I created. See if you can understand how it works. How would you do it differently? Can you adjust it to work for with initials? Feel free to send your spreadsheets in to admin@xlcalibre.com.

If you’d be interested in similar discussions, come join the brand new XLCalibre Group on LinkedIn.