Split by delimiter question

[LearnerD]

I have looked at other questions but haven't quite found the answer I need.

I have a column with data in it split by delimiter ; 1;4;10;23; etc.. each row could have anything from 1 to 26 items in that column all split by delimiter
I have 26 columns set up to take the data one column for each item.

How do I get the split data into each column !!

So for example: my delimited data 1;3;15;23 is in field C2

I have columns M2 N2 O2 etc..up to 26 fields waiting for the info.

in field M2 I would like to see 1 in field N2 I would like to see 3 In field O2 I would like to see 15 etc.. etc.. where there is no data to fill (so in this example Q2 would have nothing, I would like to see either 0 or blank don't mind which.

Hoping you can help

Thanks in advance

 

[RoryA]

If you have M365, in M2:

=TRANSPOSE(FILTERXML("<a><b>"&SUBSTITUTE(C2,";","</b><b>")&"</b></a>","//b"))

and clear anything currently in N2 and to the right.

 

[mohadin]

Ahh for data in column C Then

Sub test()
Dim a
Dim i&, k&
Dim x
    a = Range(Cells(2, 3), Cells(2, 3).End(xlDown))
    For i = 1 To UBound(a)
        x = Split(a(i, 1), ";")
        Cells(i + 1, 13).Resize(, UBound(x) + 1) = x
    Next
End Sub

 

[LearnerD]

So I have found this which helps me get the first one

=LEFT(C3,FIND(";",C3)-1)

but can't figure how to extrapolate out to get the next one? and so on

 

[LearnerD]

I think I'm nearly there?

If I use as above but change the number at the end to a plus for example 2 it will show me the first 2 items...

=LEFT($C$3,FIND(";",$C$3)+2)

However I need to then subtract the first item so that I'm only seeing the second? and so on in each cell .. how do I do that?

 

[RoryA]

Which version of Office do you have?

 

[LearnerD]

Which version of Office do you have?

O365 I missed your response above going to try it now !

 

[bebo021999]

looks good ! but just returns a blank?

It works for me! Starts from M2.

 

[LearnerD]

If you have M365, in M2:

=TRANSPOSE(FILTERXML("<a><b>"&SUBSTITUTE(C2,";","</b><b>")&"</b></a>","//b"))

and clear anything currently in N2 and to the right.

Great so that works for M3 but what do I put in N, O, P etc.

 

[LearnerD]

It works for me! Starts from M2.

View attachment 64380

Yay that works thanks sooo much. Reason for blank was my data is on row 3 not 2 !!

 

[LearnerD]

I'm sorted now thanks everyone so much !! :D