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,

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" 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

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





On May 1, 4:33*am, "Steve Yandl" wrote:
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" 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

Data Text to columns...



--
Regards
Dave Hawley
www.ozgrid.com
"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

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

Thanks a ton, Rick, Works like magic.

Regards,
Raj


On May 1, 8:01*am, "Rick Rothstein"
wrote:
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