Excel: Multi-way string search and extract -


i have column 11k+ rows of text. of them this:

1. vc (abrev.), viscous coupling 

and may this:

2. manual transmission fluid, mtf (abrev.) 

or even:

3. m/t (abrev.), manual transaxle, manual transmission, mt (abrev.) 

my goal extract "* (abrev.)" without quotes within text, , place in new column. in third case, nice if both "m/t (abrev.)" , "mt (abrev.)" extracted , separated comma.

so far i've came function

=iferror(if(find("abrev.",c68),mid(c68,1,find(")",c68)),""),"") 

which works in cases, not on of them. (function translated http://en.excel-translator.de/translator/ because original function in portuguese). tried using wildcards in function don't work.

any ideas?

thanks.

i have solution takes multiple columns it. if data starts in cell a2, here formulas:

b2 = find("(abrev.)", a2) c2 = iferror(trim(right(trim(left(a2,b2-1)),find(" ",trim(left(a2,b2-1))))), "") d2 = iferror(find("(abrev.)", a2, b2+1), "") e2 = iferror(trim(right(trim(left(a2,d2-1)),find(" ",trim(left(a2,d2-1))))), "") f2 (your goal) = concatenate(if(len(c2) > 0, c2 & " (abrev.)", ""), if(len(e2) > 0, ", " & e2 & " (abrev.)", "")) 

basically doing finding phrase "(abrev.)" , finding space before word before , selecting phrase , putting them again @ end.


Comments