Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Text To Columns with multiple spaces

murthybhs
8 - Asteroid

Hello everyone,

 

I have a data set that looks as below. 

murthybhs_0-1624303289323.png

I am interested only in the "PO Number" values. If it has a value next to it, I would like to output it as a separate column that says PO Number or else leave a blank value. I know if the column has single space separator, I can use Text to Columns tool and separate using \s. Please help me in this scenario. Thank you amazing people!!!!

4 REPLIES 4
T_Willins
14 - Magnetar
14 - Magnetar

@murthybhs,

 

I would use a RegEx Parse as it allows for easier identification of the data you are looking for.

 

The formula .*?PO Number\s+(.*?)\s+Zip.* does the following:

 

.*?PO Numbers\s+     From the start of the string look at everything until if finds the text PO Numbers followed by one or more spaces

(.*?)\s+Zip.*                Once that is found return everything until the next one or more spaces that is followed by the text Zip (and everything else)

                           

 

Text to Columns.png

Maskell_Rascal
13 - Pulsar

Hi @murthybhs 

 

Are you looking for something like the below?

Maskell_Rascal_0-1624304713237.png

If so, you can use the RegEx tool to parse the data you want. 

 

Regular Expression:

(PO Number)\s{1,}(\w{2}\d{1,})

 

RegEx tool configuration:

Maskell_Rascal_1-1624304829567.png

 

Attached is a sample workflow. 

 

Cheers!

Phil

 

murthybhs
8 - Asteroid

Thank you for the solution @T_Willins . It looks like RegEx expressions solve most of my other applications as well. Is there is a book or source where I can learn more about RegEx expressions?

T_Willins
14 - Magnetar
14 - Magnetar

HI @murthybhs,

 

Glad I could help.  Yes, there are several resources on Alteryx Community regarding RegEx.  I would start with the two below and then practice.  The more you work with RegEx, the easier it is to work with.  

 

Tool Mastery RegEx - https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-RegEx/ta-p/37689

 

Perl Syntax Guide - This is the language that RegEx formulas are written in.  It looks a little overwhelming at first, but it it will be a page you will go back to reference often when learning RegEx.

 https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/RegEx-Perl-Syntax-Guide/ta-p/1288

 

In addition to these, there are multiple posts on Community on how to use RegEx.  There are also several Weekly Challenges that feature RegEx (including my submission #188).  I highly recommend the Weekly Challenges as a way to learn Alteryx in general.

 

Enjoy your Alteryx learning journey!

Labels