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 Column based on >1 space

crunyeon
7 - Meteor

Hi Everyone,

 

I have a set of data that is separated based on a random number of spaces.  I would like to parse these out into columns, but I need to split them based on more than one space, since the data in the first column could have spaces in it.  It looks something like this:

 

Name:                             Start Date:                    End Date:
John Smith                 3/4/2017                       6/8/2017
Susan R.  Jones         4/8/2016                       5/1/2017

 

 I need these split into individual columns, but when I try to use the text to columns tool with the delimiter being two or three spaces, it still splits the names into different columns as well.

 

Any help would be greatly appreciated.  Thank you!

4 REPLIES 4
Kenda
16 - Nebula
16 - Nebula

Hey @crunyeon! I used a Data Cleansing tool first to get rid of "Tabs, Line Breaks, and Duplicate Whitespace." I then added a Formula tool with the following expression:

REGEX_Replace([Name:                             Start Date:                    End Date:], "(.*?)\s(\d+\/?)", "$1,$2")

I could then use a Text to Columns tool with a comma as the delimiter to parse out the data into columns. 

crunyeon
7 - Meteor

Hi @Kenda, I used that REGEX expression, however it doesn't seem to be putting commas in like it should.  Any idea why mine might not be working?

DanS
9 - Comet

 

 

MarqueeCrew
20 - Arcturus
20 - Arcturus
Regex_Replace([Field],"\s{2,}",'|')

If you want to replace all 2+ occurrences of a space with a '|' character (easy for parsing), you can use this formula.  Later, you can text to columns your data.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels