Splitting a string into columns

R

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
 
S

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
 
R

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top