Text to Column based on >1 space

[crunyeon]

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!

[Kenda]

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]

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?

[MarqueeCrew · Accepted solution]

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