Alteryx Designer

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

Text to Column based on >1 space

Highlighted
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!

Highlighted
14 - Magnetar
14 - Magnetar

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. 

Highlighted
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?

Highlighted
9 - Comet

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
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 reboot. Order shall return.
Labels