community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Ideas

Share your Designer product ideas - we're listening!

Text To Column Improvement - Using Fixed Width when a Delimiter Does Not Exist

I've discovered something I feel could be a drastic improvement in time with the Text To Columns tool. Frequently we have to split out data such as SSNs and ZIP Codes. From 9 digits to say XXX XX XXXX for SSN in order to mask data or from 9 digits to XXXXX-XXXX for ZIP Code or split it into into two separate cells. If the data is received with no delimiter, there is no way in text to columns to easily separate it without complex code. I asked for a way to do this and didn't get a clear response from support@alteryx.com for about a week after being unsatisfied with the basic and incomplete explanations I was receiving. Finally a Sr Customer Support Engineer gave me this complete answer using string function formulas:

 

FormattedSSN
left([SSN],3)+"-"+Substring([SSN],3,2)+"-"+Right([SSN],4)

MaskedSSN
"XXX-XX-"+right([SSN],4)

This seems like a very complicated way to do something Excel can do in three clicks.

 

Any chance we can get a fixed width delimiter button added to Text To Columns in future versions?