Thread: Help with splitting contents of a cell

Morning,


I can't remember how to split the contents of a cell into different cells.

Currently I have:
A1 = 41-33.91
would like to have:
B1 = 41
C1 = 33
D1 = 91

A2 = 41-38.00

B2 = 41
C2 = 38
D2 = 00
etc.

Ideally would like to have it in VBA because I have 5345 rows that needs to be split, but a formula would be fine. I would just copy the formula into the other cells.

Thanks in advance,

George

Hi George,

Which format do you want for extracted data- Text or Number?

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi George,

Basis your data, use following formulas for column B, C and D respectively.

Note: Extracted data numbers would be in text format.

=LEFT(A2,2)
=MID(A2,4,2)
=RIGHT(A2,2)

Regards,
DILIPandey

<click on below 'star' if this helps>

VBA

Sub Test()
For N = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    Range(Cells(N, 2), Cells(N, 4)).NumberFormat = "@"
    Cells(N, 2) = Left(Cells(N, 1), InStr(Cells(N, 1), "-") - 1)
    Cells(N, 3) = Mid(Cells(N, 1), InStr(Cells(N, 1), "-") + 1, InStr(Cells(N, 1), ".") - InStr(Cells(N, 1), "-") - 1)
    Cells(N, 4) = Right(Cells(N, 1), Len(Cells(N, 1)) - InStr(Cells(N, 1), "."))
Next N
End Sub

Formulae
B1
=LEFT(A1,FIND("-",A1)-1)

C1
=MID(A1,FIND("-",A1)+1,FIND(".",A1)-FIND("-",A1)-1)

D1
=RIGHT(A1,LEN(A1)-FIND(".",A1))