Hi,

LEN(OP)-LEN(SUBSTITUTE(OP, ',' , '')) to get number of commas


(1-3 in your case)


i will mark the statement as X.


OP is OPORTUNITY (i suggest to rename field to short simple name, put formula, ensure it works, and then rename to original, formula will update itself)

STREET: IF(X>1,LEFT(OP,FIND(’,’,OP)-1),’’)

for others, use SWITCH for different number of commas


use MID for words that are inside, RIGHT for last word, LEN(OP) for end position


use FIND 3rd parameter to find 2nd, 3rd comma

location of second comma FIND(’,’, OP, 1+FIND(’,’,OP))


third: FIND(’,’,OP,1+FIND(’,’,OP,1+FIND(’,’,OP)))

for example, for TOWN:


SWITCH(


LEN(OP)-LEN(SUBSTITUTE(OP, ‘,’ , ‘’))


,1,LEFT(OP,FIND(’,’,OP)-1)


,2,MID(OP, 2+FIND(’,’,OP), -1+FIND(’,’,OP,1+FIND(’,’,OP))-FIND(’,’,OP)-1)


,3,MID(OP, 2+FIND(’,’,OP,1+FIND(’,’,OP)),


-1+FIND(’,’,OP,1+FIND(’,’,OP,1+FIND(’,’,OP)))-FIND(’,’,OP,1+FIND(’,’,OP))-1)


,‘wrong_commas’


)

and so on.


Hope it will help you

This worked!! Thanks!!

Here are some alternate formulas that use regular expressions:

Street:

REGEX_EXTRACT(OP, SWITCH(LEN(OP) - LEN(SUBSTITUTE(OP, ",", "")), 1, "", "([^,]+)"))

Number:

REGEX_EXTRACT(OP, SWITCH(LEN(OP) - LEN(SUBSTITUTE(OP, ",", "")), 3, "^(?:[^,]*), ?([^,]+)", ""))

Town:

REGEX_EXTRACT(OP, "([^,]*), ?([^,]+)$")

City:

REGEX_EXTRACT(OP, "([^,]+)$")