Splitting a string into columns
in [Programming]
|
From: Raj on 30 Apr 2010 19:21 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
From: Steve Yandl on 30 Apr 2010 19:33 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" <rspai9(a)gmail.com> wrote in message news:1f96006f-ec23-41c6-b383-85bf345f27af(a)p5g2000pri.googlegroups.com... > 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
From: Raj on 30 Apr 2010 19:44 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" <sya...(a)comcast.net> 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" <rsp...(a)gmail.com> wrote in message > > news:1f96006f-ec23-41c6-b383-85bf345f27af(a)p5g2000pri.googlegroups.com... > > > 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
From: ozgrid.com on 30 Apr 2010 20:24 Data > Text to columns... -- Regards Dave Hawley www.ozgrid.com "Raj" <rspai9(a)gmail.com> wrote in message news:1f96006f-ec23-41c6-b383-85bf345f27af(a)p5g2000pri.googlegroups.com... > 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
From: Rick Rothstein on 30 Apr 2010 23:01
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" <rspai9(a)gmail.com> wrote in message news:1f96006f-ec23-41c6-b383-85bf345f27af(a)p5g2000pri.googlegroups.com... > 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 |