Extact next word after FIND of another word within string

[andrewjend]

Hello,

I have a sheet with columns of data, 2 adjacent columns have data, example below:

Column A Column B Column C
Base Model Model Description
Avalon Toyota Avalon XLS Sedan
Avalon Toyota Avalon XL Sedan
Camry Toyota Camry Hybrid Coupe
Camry Toyota Camry XLE Wagon

What I'd like to do is a find, searching the text string in Column B, for the occurence of the contents in column A, and then return the NEXT word after the found word in column C. I suspect I many need to use a vba function, but lack the skills. Currently I'm using a MID an LEN find formula (below) in Column C, but it's limited to a character count, and I only want to return the next whole word.

=MID(B2,FIND(A2,B2)+0,5)

This returns the following:
Column A Column B Column C
Avalon Toyota Corp. Avalon XLS Sedan XLS
Avalon Toyota Avalon XL Sedan XL S
Camry Toyota Co. Camry Hybrid Coupe HYBR
Camry Toyota Motors Camry XE Wagon XE W

What I want to return is just the trim level: Hybrid; XLS, XL or XE for example. As you can see, when using the character count, it may also extract into the body style; such as the W in wagon, etc. I've tried variations using RIGHT also, and still seems to be limited by the character count.

Eventually, if it works, in column D I'll concatenate the Column A and D together to return Avalon XLS, etc.

I know there are ways to filter, sort, vlookup, etc, based on all the model types, but those solutions won't work, because the data I'm using isn't toyota cars; not trim levels, and I have a sheet which is 77,000 rows long, by 90 columns wide, and there are over 18,000 unique/distinct values in column B.

I've also considered converting text to columns, which fails because sometimes the 'trim' level is the second word, sometime it's the 10th word; but it ALWAYS follows the model (which is listed solo in column A).

Thanks,

Andrew

[davesexcel]

Post a sample workbook

[andrewjend]

Hi -- I thought I'd uploaded a sample file, it seemed to have worked, but now I can't see it; How can I verify my sample uploaded?

[davesexcel]

Hi -- I thought I'd uploaded a sample file, it seemed to have worked, but now I can't see it; How can I verify my sample uploaded?

Your workbook sample does not have to be that large, just a few rows to show how your layout is and what your desired results should be.

[andrewjend]

Sample workbook attached (I hope!).

[daddylonglegs]

Try this formula in C2 copied down

=MID(LEFT(B2,FIND(" ",B2,FIND(A2,B2)+LEN(A2)+1)-1),FIND(A2,B2)+LEN(A2)+1,255)