Text to Columns Leaving multiple blanks cells

[andersom]

Hello

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?

Thanks 

[ChrisTX]

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?

Chris

[Raj]

Please attach some sample data

[andersom]

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.

[OllieClarke]

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.

Ollie

[andersom]

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

Thanks

[lwolfie · Accepted solution]

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.