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

--
Rick (MVP - Excel)



"Raj" wrote in message
...
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