Extract 1, 2 or 3 words from middle of text in a cell

[Vinnie Chan]

Hi,

Is it possible to extract words from text like this?


RETAILER ALL SALES Extra Foods British Columbia

I only want to extra Extra Food.


I've tried different formulas, but only worked if included 1st or last word.

Please help me.

Thanks.

[Richard Buttrey]

Hi,

What do YOU mean by extract. If all you want to do is remove the characters 'Extra Foods' just use the Find and Replace (with "") functionality.

HTH

[Vinnie Chan]

I have a list of banners (supermarkets) with categories in front and regions at the end. I only want the name of supermarkets.

for example:

RETAILER ALL SALES Tl Retailer National
RETAILER ALL SALES Dominion - Nfld Newfoundland
RETAILER ALL SALES Save Easy Newfoundland
RETAILER ALL SALES Maritimes Maritimes
RETAILER ALL SALES Atl Superstore Maritimes
RETAILER ALL SALES Atlantic SuperValu Maritimes
APPAREL Tl Retailer National
APPAREL Dominion - Nfld Newfoundland
APPAREL Save Easy Newfoundland
APPAREL Maritimes Maritimes
APPAREL Atl Superstore Maritimes
APPAREL Atlantic SuperValu Maritimes
BAKERY Tl Retailer National
BAKERY Dominion - Nfld Newfoundland
BAKERY Save Easy Newfoundland
BAKERY Maritimes Maritimes
BAKERY Atl Superstore Maritimes
BAKERY Atlantic SuperValu Maritimes
DELI Tl Retailer National
DELI Dominion - Nfld Newfoundland
DELI Save Easy Newfoundland
DELI Maritimes Maritimes
DELI Atl Superstore Maritimes
DELI Atlantic SuperValu Maritimes

I only want the names of supermarkets (highlighted in red). The list is a lot bigger than this and need to be updated weekly.

Is this doable?

Thanks for your help.

[StephenR]

This might help.

http://www.ozgrid.com/VBA/extract-words-function.htm

EDIT: now you've properly explained your problem, that link probably won't help.

[Vinnie Chan]

Thanks for your help. I followed the instruction, but didn't work. Don't know what I've done wrong.

Is there a way to set up a formula instead of VBA?

[NBVC]

I see you have multiples of the same stores...

Can you list all the possible stores somewhere off to the side,

then use a formula to check your strings for those stores and then extract those.

Eg.

If you list the stores in I1:I5 then apply formula:

Please Login or Register  to view this content.

copied down.

Does that help?

[Vinnie Chan]

Hi NBVC,

Thanks for your help. But I don't really understand what you mean. If my whole list is in Col L, should I list out all stores in one column (i.e., Col M) and apply the formula in Col N or any col?

[NBVC]

If your starting list (like sample you posted) is in column L.. then create a list of all the stores (i.e. the parts you want to extract from the list in column L) somewhere away from the main area (even on another sheet). Then in column M use the formula above with adjustments to the range I1:I5 to coincide with where your list of stores is....

See attached.

[Vinnie Chan]

Hi NBVC,

Something strange. It works if I don't change anything. When I expand my store list to I1:I19, nothing shows up.

Can you please tell me why?

Thanks.

[NBVC]

Are there blanks in that range?

Try:

Please Login or Register  to view this content.

[Vinnie Chan]

Hi NBVC,

There is no blanks in the range. But the last formula you provided works perfect.

Thanks a million!!