My whitespace delimiter

[lynettejackson]

My whitespace is the delimiter, as you can see from your input. I'd like to divide the columns into two.

[ChrisTX]

Can you attach the input file

Research and Development US Indianapolis Cost centers scc edits1.xlsx

[lynettejackson]

Hi Chris,

I have attached the input file.

[ChrisTX]

Your original post only includes the YXMD file, which does not include the XLSX file.  And your second post does not contain an attachment.

[apathetichell]

Hi - your datasource wasn't included with your file - perhaps if you could post some sample entries to the thread?

as a quick note - if you are trying to split a column into two with a varying amount of whitespace as a delimiter you can use regex tool in parse mode with:

(.*)\s{2,}(.*)

This assumes that the whitespace occurs more than twice at the break (to signify that it isn't standard spacing between word) and that you are looking for two columns at the end.

[lynettejackson]

Sorry Chris,  I thought I attached the .xls file.

[apathetichell]

(\d+\w+\d+)\s(.*)

In regex tool parse mode.

update - this one works better.

(\d{3}[0987654321a-zA-Z]+)\s(.*)

[Maskell_Rascal · Accepted solution]

Hi @lynettejackson 

I'm assuming you want to split the Cost Centers field into two columns. You can accomplish this several ways, but the simplest way is to use a Cleanse and Text to Columns tool. 

Workflow:

Cleanse Tool config:

Text to Columns config:

Output:

If this solves your issue please mark answer as correct, if not let me know!

Thanks!

Phil

[apathetichell]

One more - if you want the non-conforming entries in their own column you can grab them with:

(\d{3}[0987654321a-zA-Z]+)\s(.*)|[\*](.*)

I had thought they might be part of a multi-row header or other signifier but you can take them into a regexout3. you can change column names as you in the bottom part of the parse tool.

[ChrisTX]

The easiest way, without RegEx, is:

1) Use a formula tool to remove leading spaces:   Update the value in your [Cost centers] field:   formula = trim([Cost centers])

2) Use a Text to Columns tool, 2 columns, delimiter = space, extra characters = Leave extra in last column

 

Chris