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!

Route.xlsx

Preparation

Accepted answers

Qiu

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

https://regex101.com/

0321-rively90.yxmd

Qiu

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

@AGilbert
Nice detailed information, thank you.

0321-rively90 - r1.yxmd

All comments

Qiu

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

https://regex101.com/

0321-rively90.yxmd

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.

regex_example.yxzp

Qiu

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

@AGilbert
Nice detailed information, thank you.

0321-rively90 - r1.yxmd

rively90

thank you

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703