Text to Columns Leaving multiple blanks cells

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

Screenshot 2023-10-12 113630.png

Expression

Accepted answers

lwolfie

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.

Address Sample.yxmd

All comments

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.

Screenshot 2023-10-12 120157.png

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

Address Split Sample.xlsx

lwolfie

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.

Address Sample.yxmd

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335