How do I separate data in one column into two columns and then make the columns turn

[aflutist]

I have sample output data that comes out in column form like this:

1:1
2:3
4:5

I need to separate the values into individual columns and then make it go across rows to end up looking like this:

1 1 2 3 4 5

I want to do this in Excel or Access. I can make excel separate it into 1 1 OR move it to rows but I can't figure out how to do both.

[anvidc]

you can use Split & array()

Dim i as Integer
Dim xStr as String
Dim xArr() As String
 
xStr = "1:1"
xArr = Split(xStr, ":")
 
For i= LBound(xArr) To UBound(xArr)
  Debug.Print xArr(i)
Next

[aflutist]

@anvidc
So where do I type this?

[anvidc]

You Data is in excel or access or maybe you can upload some sample

[Rabbit]

Why do you need to do this? It sounds like you're normalizing and then denormalizing your data. You should avoid denormalization unless you have a very good reason to do so because it just causes more headaches later on.

[aflutist]

@Rabbit
I have an output file from a machine that needs to be reformatted to run in analysis software that runs in Command line and it's input requires it in row format, separated and without the colons.

[aflutist]

@anvidc
This is a small sample of my data output

[anvidc]

This may not the best solution, but should help

1.Create a Module then paste this code in VB (Alt+F11)
2.Create a Button then assign to SlpOut Macro

Sub SlpOut()
 
Dim xC As Range
Dim xArr() As String
Dim i As Long
Dim LRow As Long
 
For Each xC In Range("B1:B" & Range("B1").End(xlDown).Row)
    xArr = Split(xC.Cells.Value, ":")
    For i = LBound(xArr) To UBound(xArr)
        LRow = WorksheetFunction.CountA(Range("D:D")) + 1
 
        Range("D" & LRow).Value = xArr(i)
    Next i
Next xC
 
End Sub