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

 

[Steve Yandl]

Raj,

What are the number pairs separated by? In your post it appears to be
multiple spaces.

Where is the output to appear relative to the cell containing the original
string? For example, should the current cell with the string be the upper
left cell of the range created?

Is it always going to be four number pairs and an odd lone number or might
there be more pairs?


Steve Yandl

 

[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