Seperate cell info into rows

[ekhawaja]

Hello,

So i have multiple data in one cell which i would like to get split into individual rows. See sample snaps.

Basically, "names" that need to be extracted either begin with 27, 21, 22, 30 or 31 followed by alphabets/dash/number. Some cells have a space separating the listed items and some use "," comma, and some have blank space, but it is not consistent.

Any way i can split the data into individual rows?

 

[ekhawaja]

Hello,

So i have multiple data in one cell which i would like to get split into individual rows. See sample snaps.

Basically, "names" that need to be extracted either begin with 27, 21, 22, 30 or 31 followed by alphabets/dash/number. Some cells have a space separating the listed items and some use "," comma, and some have blank space, but it is not consistent.

Any way i can split the data into individual rows?

thanks in advance.

 

[kweaver]

Maybe something along these lines?

=TRANSPOSE(TEXTSPLIT(LEFT(A1&A2,LEN(A1&A2)-1),{","," "}))

 

[ekhawaja]

Maybe something along these lines?

=TRANSPOSE(TEXTSPLIT(LEFT(A1&A2,LEN(A1&A2)-1),{","," "}))

thanks for the reply. I have data in from A1 to A500......., and need that all split into separate rows.

 

[kweaver]

How about:

=TRANSPOSE(TEXTSPLIT(LEFT(CONCAT(A1:A500),LEN(CONCAT(A1:A500))-1),{","," "}))

Or, in light of the repeat:

=LET(cc,CONCAT(A1:A500),TRANSPOSE(TEXTSPLIT(LEFT(cc,LEN(cc)-1),{","," "})))

 

[kweaver]

I suspect the left calculation can be removed.

 

[Fluff]

Another option

=DROP(REDUCE("",A2:A500,LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,,{" ",","})))),1)