CODEWITHSUNDEEP
excelexcel-formula
Cherubim
Cherubim

Reputation: 35

Excel: find a word in a cell, then return the preceding word and the found word

This is a slightly different take on a previous question I asked. I tried to modify the great advice I received but couldn't quite get there.

I am looking to find text in a cell (not case specific), then return the preceding word plus the found text. I prefer a formulaic solution (over a macro).

Sample cell contents include the following where I'm looking for "TRUCK":

"This is a test dump TRUCK like it 54" - I want "dump TRUCK"

"This is pick-up Truck forgot 346 I like" - I want "pick-up Truck"

I tried some combo of the Mid() function but wasn't quite getting it:

=MID(B2,(FIND("Truck",B2,1)-1),FIND(" ",B2,FIND("Truck",B2,1)-1)-FIND("Truck",B2,1)+1)

Thanks for the help!

Upvotes: 0

Views: 806

Answers (1)

Slai
Slai

Reputation: 22876

=TRIM(RIGHT(SUBSTITUTE(LEFT(B2,SEARCH(" Truck ",B2)+5)," ",REPT(" ",99)),199))

LEFT(B2,SEARCH(" Truck ",B2)+5) gets the "This is a test dump TRUCK" part.

Then SUBSTITUTE( ^ ," ",REPT(" ",99)) gets: 

This                                                                                                   is                                                                                                   a                                                                                                   test                                                                                                   dump                                                                                                   TRUCK

and =TRIM(RIGHT( ^ ,199)) gets the last 2 words from the above and removes the extra spaces.

Upvotes: 3

Related Questions