Multiple Data in Cell to columns

[petergassert]

Hi,

I got a table with multiple values in one and more cells.
All values are Separated by ' ' and i do not know how many values are  given in one cell.

Desc	C1	C2	C3
A	1,2 3,4	5,6 7,8	9,10
B	1,2 3,4 5,6	5,6 7,8	9,10 11,12
C	1,2 3,4 5,6	5,6 7,8 9,10	11,12 13,14
D	A,B	B,C C,D
E	A,B B,C	C,D D,E	E,F

Main Question is, after concatenation of C1, C2 and C3 to transfer a respective cell?

Example for value A:

Desc	Value
A	1,2
A	3,4
A	5,6
A	7,8
A	9,10

Is a function given simular to Excel to transfer data to columns and then may pivot the data in script?

Thanks,

Peter

[chrismarlow · Accepted solution]

Hi,

You might need to do something about the empty values, but how about the below for a start;

data:
load 
	Desc,
	Trim(SubField(C1&' '&C2&' '&C3, ' ')) as Value;
load * inline [
Desc|C1|C2|C3
A|1,2 3,4|5,6 7,8|9,10
B|1,2 3,4 5,6|5,6 7,8|9,10 11,12
C|1,2 3,4 5,6|5,6 7,8 9,10|11,12 13,14
D|A,B|B,C C,D|	 
E|A,B B,C|C,D D,E|E,F
] (delimiter is '|');

Cheers,

Chris.

[petergassert]

Thanks Chris!

Forgot this possibility of the subfield function.

Thanks and a all the best for 2022,

Peter