Need help splitting text variants into columns

[Guerson_Meyer]

Hey there,
For a specific field “OPPORTUNITY” that I have integrated into Airtable, I get three type of inputs. I need a formula to be able to separate this field in 4 columns.

Input posibilities in “OPPORTUNITY” Field:

• Option 1: Opportunity in TOWN, CITY
• Option 2: Opportunity in STREET, TOWN, CITY
• Option 3: Opportunity in STREET, NUMBER, TOWN, CITY

I need a formula to be able to create the following columns automatically:

• STREET
• NUMBER
• TOWN
• CITY

Any idea?

Thanks in advance!!
:slightly_smiling_face:

GM

[Alexey_Gusev]

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 :slightly_smiling_face:

[Guerson_Meyer]

This worked!! Thanks!!

[Justin_Barrett]

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, "([^,]+)$")