Calling all Racers for the Alteryx Grand Prix! It's time to rev your engines and race to the stage at Inspire! Sign up here.

Alteryx Designer Discussions

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

Text to Column based on >1 space

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!

15 - Aurora
15 - Aurora

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. 

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?

9 - Comet



20 - Arcturus
20 - Arcturus

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.




Alteryx ACE & Top Community Contributor

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