Extract the letters after a word

**cmb80** · 08-12-2015, 05:12 AM

In column A I have fruits with a letter after the fruit (not always a letter and it may vary in length).

So example

Apple bt
Water Melon c
Pear w
Orange
Grape q
Plum esq

In column B I want to extract the abbreviation after the fruit. How is best to do so with a formula?

**XOR LX** · 08-12-2015, 05:19 AM

Hi.

How will we instruct Excel to know if the desired extraction is present or not when the fruit is made up of more than one word?

For example, if you had just "Water Melon", i.e. without the "c" at the end (by the way, I always thought this was one word, i.e. "Watermelon", not two), how do we know that "Melon" is not to be extracted, since it is actually just part of the fruit name?

Regards

**cmb80** · 08-12-2015, 05:23 AM

The fruit name may not always be one word after. So in my example I will have Apple bt in row A1, in C1 I could have the fruit name (apple). What is the best way for B1 to show just the "bt"

**XOR LX** · 08-12-2015, 05:28 AM

Originally Posted by cmb80

The fruit name may not always be one word after. So in my example I will have Apple bt in row A1, in C1 I could have the fruit name (apple). What is the best way for B1 to show just the "bt"

How does that answer my question?

Regards

**Sam Capricci** · 08-12-2015, 05:29 AM

This could work (except for Orange) =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",125)),125))

**XOR LX** · 08-12-2015, 05:57 AM

Originally Posted by Sambo kid

This could work (except for Orange)

Which is why I asked my question!

**cbatrody** · 08-12-2015, 06:07 AM

It can be done if you have the complete list of fruits available in a column somewhere in the file.

Can you post a sample excel with some more examples?

**cmb80** · 08-12-2015, 07:00 AM

Hi all, thanks for the replies - I have found a workaround using Vlookup and Mid

**TMS** · 08-12-2015, 07:50 AM

This would work, though a bit long winded:

Formula:

Please Login or Register  to view this content.

This variation on Sambo kid's solution also works, including for Orange:

Formula:

Please Login or Register  to view this content.

Still shorter than mine

We've shown you ours. Now care to share your solution?

Regards, TMS

**Sam Capricci** · 08-12-2015, 09:42 AM

TMS, nice, I didn't have the time to work it out before leaving for work but I was trying some combo like how you tweaked what I gave him. For some reason I cannot give you a rep for it but, nice job.

**TMS** · 08-12-2015, 10:05 AM

No worries. Probably because you gave me rep a few days ago

Extract the letters after a word

LinkBack

Thread Tools

Rate This Thread

Display

Extract the letters after a word

Re: Extract the letters after a word

Re: Extract the letters after a word

Re: Extract the letters after a word

Re: Extract the letters after a word

Re: Extract the letters after a word

Re: Extract the letters after a word

Re: Extract the letters after a word

Re: Extract the letters after a word

Re: Extract the letters after a word

Re: Extract the letters after a word

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] I need to extract first 4 letters from a cell

[SOLVED] Extract first or first two letters from postcode

Need to extract only numbers after certain letters

[SOLVED] Extract letters before first number

Extract a text from word and place it en excel or word?

[SOLVED] Extract bold letters in a cell

RE: Extract bold letters in a cell

Bookmarks

Bookmarks

Posting Permissions