When working with Strings in Excel, it doesn’t get any easier than this. You can practically do anything you want with the strings and also visualize as you do them.
Some Functions that are frequently used – LEFT(), RGHT(), MID(), FIND(), SUBSTITUTE(), TRIM(). These are even more powerful when used in combination. LEFT() & RIGHT() are used to extract a part of the text from LEFT/RIGHT of the string input. MID() is used to extract text from anywhere in a string given the start and end positions. FIND() is used to locate a character or string in a string. SUBSTITUTE() is used to substitute a character or string within a string. TRIM() is used to trim any trailing spaces.
So, what’s the BIG deal with these functions anyways?
Consider extracting the First Name from every record in the file – assuming every name is stores <First Name> <Last Name>. There are a number of ways to do this – I’d use a combination of LEFT and FIND. Here’s a quick syntax – LEFT(A2,FIND(A2,” “,1)-1)
Perfect. That was easy. Now what?
What if I wanted to extract the Last Name of every Record?
While the RIGHT() function certainly extracts text from the right, when used with FIND() – things get a little complicated. So, RIGHT(A2,FIND(A2,” “,1)-1) does not work!
But this does – RIGHT(A2,LEN(A2) – FIND(A2,” “,1) + 1)
Great! We’re set here. Is there any more to this?
What if we had First, Middle & Last Name for every record? What then? How will you extract the last name in this case? Or, let’s take the case of Addresses – can you get just the PINCODE for every address in the database?
What this entails is – nested FIND() to locate the correct delimiter. For example, in the case of First, Middle & Last name, we’d have to locate the second ” ” (space) for the start number for Last name. Here’s the syntax – RIGHT(RIGHT( A2, LEN(A2) – FIND(A2,” “,1) + 1),LEN(RIGHT( A2, LEN(A2) – FIND(A2,” “,1) + 1))-FIND(RIGHT( A2, LEN(A2) – FIND(A2,” “,1) + 1),” “,1) +1)
Too complicated? What if we have more than 2 delimiters? What if we don’t know how many exist?
It surely doesn’t have to be so complicated. Why can’t FIND() just look from the right of a string?!
There indeed is a neat solution to this.
Here’s the idea – How about if we counted the number of occurrences of the delimiter per string, and then, temporarily change that delimiter to another delimiter and use that in the FIND function I’ve mentioned above. Voila!!
Still confused?? Take a look below for a step by step explanation –
Find number of occurrences (this is the most important step in all this)
= LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””)) (What a great way to get the number of occurrences – very thoughtful!)
Substitute the last occurrence only!
= SUBSTITUTE(A1,”,”,”|”, … ) (… follows from the function above which gives the instance to be substituted)
Extract using the first formula above
RIGHT(A1, LEN(A1) – FIND(“|”,…,1))
Putting them all together –
And there we have it… This can be further improved using the ‘INSTANCE’ option in SUBSTITUTE() function.
This should handle most of the situations that calls for text extraction directly from the right. Just copy that last function and put it in the cell next to where you want to extract (ofcourse, redirect the A1 to your cell) and you’re good to go.
Don’t have to thank for this one! 🙂