Splitting a string into columns

[Raj]

Hi,

A cell has the following string:
1-5 3-4 12-2 1-3 5
ie. pairs of numbers separated by a hyphen and an odd lone number at
the end of the string

This needs to be converted into columns and rows as follows:
1 5
3 4
12 2
1 3 5

Each hyphenated pair into two columns in a row and the odd lone number
in a third column in the last row.

Thanks in advance for the help.

Regards,
Raj

 

[Raj]

The number pairs are separated by one or more spaces.

The current cell with the string should be the upper left cell of the
range.

The number of pairs in a cell are not fixed: they can vary from one to
several.

Thanks,

Regards,
Raj

 

[ozgrid.com]

Data > Text to columns...

 

[Rick Rothstein]

Give this macro a try (simply select the cell with your text string
first)...

Sub DistributeNumbers()
Dim X As Long, CellText As String, Parts() As String
CellText = WorksheetFunction.Trim(Selection.Value)
Parts = Split(CellText)
For X = 0 To UBound(Parts) - 1
Selection.Offset(X, 0).Value = Split(Parts(X), "-")(0)
Selection.Offset(X, 1).Value = Split(Parts(X), "-")(1)
Next
Selection.Offset(UBound(Parts) - 1, 2).Value = Parts(UBound(Parts))
End Sub

 

[Raj]

Thanks a ton, Rick, Works like magic.

Regards,
Raj