CODEWITHSUNDEEP
excelsearch
sunny
sunny

Reputation: 11

excel search function in the cell

this is my excel column 

Iolite Purple Color Faceted Almonds Shape "AA" Grade Bead
Spinal Black Color Smooth Balls Shape "AAA" Grade Bead
Opal Orange Color Faceted Round Shape "AAA" Grade Bead
Spesitite Orange Color Faceted Trillion Shape "AA" Grade Bead
Garnet Pink Color Faceted Trillion Shape "AA+" Grade Bead
GreenGold Yellow Color Faceted Fancy Shape "AA+" Grade Bead
GreenGold Yellow Color Faceted Fancy Shape "AAA" Grade Bead
Sapphire Shaded Blue Color Faceted Bead Shape "AAA" Grade Bead

i want to copy the word before the word "color" in a different cell. The world "color" is in different position.
any formula?

thanks

Upvotes: 0

Views: 193

Answers (1)

James L.
James L.

Reputation: 9453

It looks like your column data was reformatted in your question. So, I extrapolated the following. Provided this list in column A:

Purple Color Faceted Almonds Shape "AA" Grade Bead 
Spinal Black Color Smooth Balls Shape "AAA" Grade Bead 
Opal Orange Color Faceted Round Shape "AAA" Grade Bead 
Spesitite Orange Color Faceted Trillion Shape "AA" Grade Bead 
Garnet Pink Color Faceted Trillion Shape "AA+" Grade Bead 
GreenGold Yellow Color Faceted Fancy Shape "AA+" Grade Bead 
GreenGold Yellow Color Faceted Fancy Shape "AAA" Grade Bead 
Sapphire Shaded Blue Color Faceted Bead Shape "AAA" Grade Bead

These formulas in column B:

=LEFT(A1,SEARCH("color",A1)-1)
=LEFT(A2,SEARCH("color",A2)-1)
=LEFT(A3,SEARCH("color",A3)-1)
=LEFT(A4,SEARCH("color",A4)-1)
=LEFT(A5,SEARCH("color",A5)-1)
=LEFT(A6,SEARCH("color",A6)-1)
=LEFT(A7,SEARCH("color",A7)-1)
=LEFT(A8,SEARCH("color",A8)-1)

Yield these values:

Purple 
Spinal Black 
Opal Orange 
Spesitite Orange 
Garnet Pink 
GreenGold Yellow 
GreenGold Yellow 
Sapphire Shaded Blue

Best of luck!

Upvotes: 4

Related Questions