Extracting Middle and Last word after commas

Question

My current file looks like this:

IATAICAOAirport nameLocation servedTimeDSTAAANTGAAnaa AirportAnaa, Tuamotus, French PolynesiaUTC−10:00

I am trying to get this:

IATAICAOAirport nameLocation servedTimeDSTWord 1Word 2Word 3AAANTGAAnaa AirportAnaa, Tuamotus, French PolynesiaUTC−10:00 AnaaTuamotusFrench 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,

IATAICAOAirport nameLocation servedTimeDSTAAOSVANAnaco AirportAnaco, VenezuelaUTC−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 Answer

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 Answer

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

OllieClarke · Accepted Answer

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