Text to Columns & Rows from Single Cell

[kafka_u21]

I am new to Alteryx, hence the query:

I have data formatted in an excel in a single column, i need to break it into multiple rows & columns with headers. how to go about it?

Single data cell contains this continuous data like : "ID:123,name:smith,city: chicago,state;IL,Country:US;ID:234,anem:john,city:LA,State:california"....

Basically i want it to be displayed in table structure with table headers of ID, Name, City, State & Country and data in rows.

[ShankerV]

Hi @kafka_u21 

As per the data shared, the data's are in single line.

There is one uniqueness shared in the data; after every record there is one ";" as separator.

Many thanks

Shanker V 

[ShankerV · Accepted solution]

Hi @kafka_u21 

Further if you need to have the results like below, you can use one more Text to columns tool.

Step 1: Input

 

Step 2:

Step 3:

 

Step 4:

  

 

Many thanks

Shanker V 

[kafka_u21]

This works great. Thanks a lot for this.

[kafka_u21]

Shanker V - the solution works great in most cases but when one of the columns is blank then it doesnt split into columns correctly, the alignment goes away. for example:ID:123,name:smith,city: chicago,state;IL,Country:US;ID:234,anem:john,city:LA,State:null,Country:US" then 2 separate columns are created. any workaround for that?

[ShankerV]

Hi @kafka_u21 

For my better understanding, if you say the alignment goes away means you are referring to semi-colon after State in the below?

ID:123,name:smith,city: chicago,state;IL,Country:US;ID:234,anem:john,city:LA,State:null,Country:US

Many thanks

Shanker V

[kafka_u21]

yes correct. the semi colon comes as separate column in those rows which have State as blank

[ShankerV · Accepted solution]

Hi @kafka_u21 

I have added this Formula to overcome the issue.

Many thanks

Shanker V 

[ShankerV · Accepted solution]

Hi @kafka_u21 

Please find the below modifications done.

Step 2: Added filter tool.

Step 3: Changed to $ symbol

 

Step 4: Added extra ; symbol

Step 5: Select tool remains the same.

Hope this helps. 

Many thanks

Shanker V

[flying008 · Accepted solution]

Hi, @kafka_u21 

FYI.  your input data is already changed, so detail as you need to be please follow @ShankerV .