I am currently using a number of formulas to extract the first three words from a string.
A1 - contains a sentence of variable length eg "to supply and install suspended ceiling aircon system to the first floor"
B1 - counts the number of words by using =IF(LEN(TRIM(cleanwords(A1)))=0,0, LEN(TRIM(cleanwords(A1)))-LEN(SUBSTITUTE(TRIM(cleanwords(A1))," ",""))+1)
[cleanwords is a UDF to remove a list of 100+ words I am not interested in eg "up down in of to from supply and install" etc]
C1 - calculates the max number of words to extract by using =IF(A1>=3,3,IF(A1>1,2,IF(A1=1,1,0)))
So in my example the max number of words wanted is 3
D1 =IF(ISBLANK(A1),"",LEFT((((A1))),FIND("^",SUBSTITUTE((((A1)))&" "," ","^",C1))-1))
This would return "suspended ceiling aircon"
E1 uses to the result in D1 to vlookup data from a master product category
I am unable to combine the three elements of the formula into a single formula or UDF and would be grateful for any help!
Bookmarks