We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

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

Text to Columns Leaving multiple blanks cells

6 - Meteoroid



I'm trying to split an rearrange an address field split by commas using text to columns. This is leaving multiple blanks cells across the columns it has been split into i.e., some address fields have been split into five cells but others have been split into 10+. I'm trying to use the Multi-Field formulas to say if address field x is null then use the value from the next column.


I've attached a screen-shot but all this does is move the data to newly populated columns in the same order?


Any ideas?








15 - Aurora

If you're writing a formula using brute force (coding many If/Then conditions), you don't really need a Multi-field formula tool.  You can use a regular Formula tool.


Can you post some sample input, and desired output?



13 - Pulsar

Please attach some sample data


6 - Meteoroid

Thanks Chris


I suppose what I'm trying to do is standardise the number of columns the text is split across. The data [Address] was taken from a survey that included a free text response so some of the address fields span fifteen columns.


Can I limit the number of fields the data is split across and maintain its integrity.



15 - Aurora
15 - Aurora

Hey @andersom,


If you split to rows rather than columns, then filter out the nulls, and then cross-tab back that might be a better solution.

It would help if you could provide some data and the ideal endpoint.



6 - Meteoroid

Thanks for your reply


I've attached a sample set of data I'm working with Row 147 is an example of where it's been split across multiple columns where row 136 is only split across 3



10 - Fireball

I'm not sure if this will work for you, but you could try something like this.  It's along the lines of Ollie's suggestion.