To covert text to columns and then arrange each values in each rows

[ANANDPRABHU]

Hi All,

Please see below the input and the expected output. Can you help with a workflow to accomplish this use case?My data is currently separated using pipe and available in one cell of an excel.The values should be separated and each values should then be arranged one after another in each individual cell (not in a particular order though).

Input:

Col A

123|326|789|569|

000|999|663

336|445

972|971273|27275

416|98537|985374

9715|98696|98697

246|989042|9890

8|1100|11136|11183|1084|1094|11|115|11136|1110|11|10130|1113|1

Output:

123
000
336
972
416
9715
246
8
326
999
445
971273
98537
98696
989042
1100
789
663
27275
985374
98697
9890
11136
569
11183
1084
1094
11
115
11136
1110
11
10130
1113
1

[RishiK · Accepted solution]

@ANANDPRABHU I have used the Text to Columns tool to parse out the data into rows by a specific delimiter (|) in your case.

Does the attached workflow produce the output you require?

[atcodedog05]

Hi @RishiK 

Interesting, I am wondering why isnt the last pipe generating a null row. It usually does with column though

Hope this helps : )

[RishiK]

@atcodedog05 that's probably because there is no space or value after the last |

[ANANDPRABHU]

Thank you. That worked but how about this use case? 

Each value in 'col A' separated by pipe corresponds to its value in 'col B' in same order. For example, AAA in the first row corresponds to 123; BB to 326; CCC to 789 and FFF to 569. I need the output as give below. Can you help?

Input:

Col A	Col B
AAA|BB|CCC|FFF	123|326|789|569|
RRR|EEE|TTT	000|999|663
ZZZ|EEE	336|445
MM|RR|QQQ	972|971273|27275
TTT|GGG|EEE	416|98537|985374

Output:

Col A	Col B
AAA	123
BB	326
CCC	789
FFF	569
RRR	0
EEE	999
TTT	663
ZZZ	336
EEE	445
MM	972
RRR	971273
QQQ	27275
TTT	416
GGG	98537
EEE	985374

[atcodedog05]

Hi @ANANDPRABHU 

You can split the 2 columns separately and join on position using join tool like below.

Workflow:

Hope this helps : )

[ANANDPRABHU]

Yes it worked. Thanks All!