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
Post a Comment