Splitting Row Into Multiple

[bsheremeta]

I have bunch of rows that look like this:

"1050                                                                           United States"

or

"59495                                              California"

How can I obtain output that will look like this:

1050

United States

59495

California

I believe I need to use Text to Columns and split by whitespace but not sure if that works well. 

Thank you!

[atcodedog05]

Hi @bsheremeta 

You can use data cleanse tool to remove duplicate spaces and then use text to column.

Workflow:

Hope this helps : )

[MichaelSu]

Hi @bsheremeta ,

In addition to @atcodedog05 's solution, here is another fun way to accomplish the above. 

Thanks,

Mike

[MichaelSu]

Also if you add '\s' into the RegEx expression you no longer need the data cleansing tool.

Thanks,

Mike