Splitting the Text

[rively90]

Hi,

I want to split one column into 3 new columns:
Route Departure: Left before 'to '

Route Arrival: Right after 'to '  (please do not include Via *****)

 Via: text after the word 'Via' (if any). If there's nothing, just add "N/A - Direct Flight"

Example 1:

Aalborg to Copenhangen

Expected result:

Route Departure: Aalborg

Route Arrival: Copenhangen

Via: N/A - Direct Flight

Example 2:

LAX to CDG via IAH

Expected result:

Route Departure: LAX

Route Arrival: CDG

Via: IAH

I tried my best to create 3 calcs, but it did not do a great job splitting it. Any other thoughts/alternatives?

Route Departure:

• LEFT([Route], FINDSTRING([Route], " to ") - 1)

Route Arrival:

• RIGHT([Route], LEN([Route]) - FINDSTRING([Route], " to ") - 3)

Via:

IF CONTAINS([Route_Arrival], " via ") THEN
MID([Route_Arrival], FINDSTRING([Route_Arrival], " via ") + 5, LEN([Route_Arrival]) - FINDSTRING([Route_Arrival], " via ") - 3)
ELSE
"N/A - Direct Flight"
ENDIF

Could you please send the alteryx workflow please? I'd like to learn how you approach this one. I attached the excel file. Thank you!

[Qiu · Accepted solution]

@rively90 
We can try to the RegEx parsing as blow.
I always use this website to test my RegEx

https://regex101.com/

[rively90]

Hi @Qiu , thank you

How do I replace nulls on 'Via' to "N/A - Direct Flight"?

[AGilbert]

Hi @rively90,

I like the expressions that you built! Nice job!

A 'go-to' tool for parsing strings is RegEx, but it does come with some complexity. I've included a simple (and incomplete) example in the attached workflow. Some of the helpful features to know are capturing, non-capturing groups and character classes. With regex, and some creativity, you can break apart strings in very useful ways. 

There are interactive lessons here under the Parsing Data category: Interactive Lessons - Alteryx Community

I found this link to be helpful when first understanding the syntax although it can be slightly different than the Alteryx implementation. 

Regular Expression HOWTO — Python 3.12.2 documentation

Alternatively, you can replace spaces with another character which does not exist in the data. For example, a pipe ("|") which you can then use to split particularly tricky parts of a string. I do this in the second part of the workflow. 

[Qiu · Accepted solution]

@rively90 
We can use a simple Formula tool for what you need.

@AGilbert 
Nice detailed information, thank you.

[rively90]

thank you