Extracting Middle and Last word after commas

[Ghosh]

My current file looks like this:

IATA	ICAO	Airport name	Location served	Time	DST
AAA	NTGA	Anaa Airport	Anaa, Tuamotus, French Polynesia	UTC−10:00

I am trying to get this:

IATA	ICAO	Airport name	Location served	Time	DST	Word 1	Word 2	Word 3
AAA	NTGA	Anaa Airport	Anaa, Tuamotus, French Polynesia	UTC−10:00		Anaa	Tuamotus	French Polynesia

There is a slight issue here, there are some rows where the Location served is in the following form, there is no city name,

IATA	ICAO	Airport name	Location served	Time	DST
AAO	SVAN	Anaco Airport	Anaco, Venezuela	UTC−04:00

The String function that I am using in a formula function Creating a new column called Country, is this: 

Substring([Location served],FindString([Location served], ',')+ 1, Length([Location served]))

The problem is that it isn't returning me the Country name, instead I am getting is everything after the 1st comma.

I am trying to get it all using one string function where I can get the Word 1 Field, Word 2 Field and the Word 3 Populated respectively, if the string is "Anaa, Tuamotus, French Polynesia", but if the string is "Anaco, Venezuela", then only the Word 1 and the Word 3 Fields are populated.

Is this possible? My build string is wrong I Know, but I am confused over the sentence above in my question here. Hope I am making it clear.

Any help is greatly appreciated.

Thank You.

[fmvizcaino · Accepted solution]

Hi @Ghosh ,

Attached is an example showing how to get it done!

I'm using parse method from regex tool with the expression ([^,]+?),(.*?),*([^,]+$)

Let me know if that works for you.

Best,

Fernando Vizcaino

[Jonathan-Sherman · Accepted solution]

Hi @Ghosh.,

I think this solves your issue, and this workflow is fully dynamic and would create the number of columns needed for the number of words to extract, whether that be 1, 3 or 50.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[Ghosh]

Thank you for the solution, just asking, can this also be done using just string functions?

[Ghosh]

Thank you for the solution, just asking, can this also be done using just string functions?

[OllieClarke · Accepted solution]

Hi @Ghosh You can often replicate a Regex Parse with a Regex_replace (which means you can use it in a formula tool). If we take @fmvizcaino regex, then we can set up a formula tool like this to get the same output:

The TRIM() removes the leading whitespace that the Regex output, and then each formula replaces the entire string with the specified capture group (\1, \2 or \3). As long as your regex covers the entire string, then you can use a regex_replace formula like this to parse strings. (the Regex_replace() function of course being a string function)

I've attached a workflow showing how the formula tool and the regex tool can give the same output with @fmvizcaino's regex

Hope that helps,

Ollie

[Ghosh]

Thank you so much. This works. I have made 3 workflows using all the 3 solutions provided. I understand using regex makes it faster. and know I know how to use regex expressions in the formula tool.

Lot of things learnt today. 

Thank you to all of you 🙂