Formula to extract numbers from text

[Obewan]

I have numbers that appear in the following format in column A. i want a formula in columns B:E that would separate them so probably 4 different fomrulas. The answer to the first row would be
B1 = 3
C1 = 2
D1= 0
E1 = 0

3: 2- 0- 0

21: 3- 3- 2

2: 1- 0- 0

2: 0- 2- 0

3: 1- 1- 0

Thanks in advance

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E
1
2	3: 2- 0- 0	3	2	0	0
3	21: 3- 3- 2	21	3	3	2
4	2: 1- 0- 0	2	1	0	0
5	2: 0- 2- 0	2	0	2	0
6	3: 1- 1- 0	3	1	1	0
Main

Cell Formulas
Range	Formula
B2:E6	B2	=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(A2,":","</m><m>"),"-","</m><m>")&"</m></k>","//m"))
Dynamic array formulas.

 

[veyselemre]

VBA >

Sub test()
    Dim i&, ii&
    [B:E].ClearContents
    With CreateObject("Vbscript.Regexp")
        .Pattern = "(\d+)\:\s(\d+)\-\s(\d+)\-\s(\d+)"
        For i = 1 To Cells(Rows.Count, 1).End(3).Row
            If .test(Cells(i, 1).Value) Then
                With .Execute(Cells(i, 1).Value)(0).submatches
                    For ii = 0 To .Count - 1
                        Cells(i, ii + 2).Value = .Item(ii)
                    Next ii
                End With
            End If
        Next i
    End With
End Sub

Sub test2()
    Dim i&
    [B:E].ClearContents
    For i = 1 To Cells(Rows.Count, 1).End(3).Row
        Cells(i, 2).Resize(, 4).Value = Split(Replace(Cells(i, 1).Value, ":", "-"), "- ")
    Next i
End Sub

 

[Obewan]

=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(A2,":","</m><m>"),"-","</m><m>")&"</m></k>","//m"))

Gold!! Thank you!!

 

[Rick Rothstein]

Here is another formula solution you can consider (put it in cell B2 and copy across to E2, then copy B2:E2 down)

=TRIM(MID(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE($A2,"-",""),":","")," ",REPT(" ",99)),COLUMNS($B:B)*99,99))

And here is another macro that you can also consider...

Sub SplitOutNumbers()
  Dim Cell As Range
  Application.ScreenUpdating = False
  For Each Cell In Range("A2", Cells(Rows.Count, "A").End(xlUp))
     With Cell.Offset(, 1).Resize(, 4)
       .Cells = Split(Replace(Replace(Cell.Value, ":", ""), "-", ""))
       .Value = .Value
     End With
  Next
  Application.ScreenUpdating = True
End Sub

 

[Fluff]

Glad we could help & thanks for the feedback.