Extracting Text from Right of String in Excel

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 ideaHow 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 –

Step1 :

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!)

Step2:
Substitute the last occurrence only!
= SUBSTITUTE(A1,”,”,”|”, … ) (… follows from the function above which gives the instance to be substituted)

Step3:
Extract using the first formula above
RIGHT(A1, LEN(A1) – FIND(“|”,…,1))

Putting them all together
=RIGHT(A1,LEN(A1)-FIND(“|”,SUBSTITUTE(A1,”,”,”|”,LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””)))))

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! 🙂

Advertisements

5 thoughts on “Extracting Text from Right of String in Excel

  1. Eldon

    hi!,I really like your writing so a lot! share we be in contact more approximately your
    article on AOL? I need an expert in this house to
    resolve my problem. May be that’s you! Taking a look ahead to look you.

    Reply
  2. Lily Le

    With a couple tweaks for me, it works great!

    tweak 1 – replace all the ” manually with your excel version of “, which is what Tommy was referring to…I think.

    tweak 2 – add | between the last “”

    tweak 3 – (maybe specific for my needs because I had 2 underscores) had to add -1 after FIND() for FIND()-1

    My modified version as an additional example:

    =RIGHT(A51,LEN(A51)-FIND(“|”,SUBSTITUTE(A51,”__”,”|”,LEN(A51)-LEN(SUBSTITUTE(A51,”__”,”|”))))-1)

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s