My whitespace delimiter
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
My whitespace is the delimiter, as you can see from your input. I'd like to divide the columns into two.
Solved! Go to Solution.
- Labels:
- Expression
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Can you attach the input file
Research and Development US Indianapolis Cost centers scc edits1.xlsx
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Chris,
I have attached the input file.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Your original post only includes the YXMD file, which does not include the XLSX file. And your second post does not contain an attachment.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sorry Chris, I thought I attached the .xls file.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
(\d+\w+\d+)\s(.*)
In regex tool parse mode.
update - this one works better.
(\d{3}[0987654321a-zA-Z]+)\s(.*)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
