Reverse Text to Columns
 

I have comma-delimited data that I would like to parse right to left rather
than left to right; can do?

Thanks!
--
Marv Lusk

Reverse Text to Columns
 

MarvInBoise wrote:
I have comma-delimited data that I would like to parse right to left rather
than left to right; can do?

Thanks!
--
Marv Lusk

Hi Marv,

Could you just parse left to right then attach an extra row of
increasing numbers (1 in 1st column, 2 in second column, 3 in 3rd
column etc) then reverse the order of the result of text to columns by
sorting left to right by the row of increasing numbers in descending
order.

Ken Johnson

Reverse Text to Columns
 

Maybe with a macro.

But if you have different number of elements in the range, what should happen?

For instance, if I have this in A1:A3
Column A
--------------
$A$1
$A$1,$A$2
$A$1,$A$2,$A$3

Should I end up with:

Column A B C D
-------------- ---- ---- ----
$A$1 $A$1
$A$1,$A$2 $A$2 $A$1
$A$1,$A$2,$A$3 $A$3 $A$2 $A$1

So cells with a fewer number of elements have those elements go to the far right
(kind of right justifying the values)???

If that's what you want:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myTRng As Range
Dim CurWks As Worksheet
Dim TmpWks As Worksheet
Dim NumberOfCols As Long
Dim iCol As Long

Set CurWks = Worksheets("sheet1")
Set TmpWks = Worksheets.Add

With CurWks
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
myRng.Copy _
Destination:=TmpWks.Range("a1")
End With

With TmpWks
Set myTRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
With myTRng
.TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False
End With

NumberOfCols = .UsedRange.Columns.Count

For iCol = NumberOfCols To 1 Step -1
With myRng.Offset(0, NumberOfCols - iCol + 1)
.Value = myTRng.Columns(iCol).Value
End With
Next iCol
End With

Application.DisplayAlerts = False
TmpWks.Delete
Application.DisplayAlerts = True

End Sub

It just copies the data to a temporary worksheet, then does data|text to
columns, then goes in reverse order to plop the columns back to the original
sheet. And then deletes the temp worksheet.


=============

If you want it to look like:

Column A B C D
-------------- ---- ---- ----
$A$1 $A$1
$A$1,$A$2 $A$2 $A$1
$A$1,$A$2,$A$3 $A$3 $A$2 $A$1

Kind of left justified???

Then try this:

Option Explicit
Sub testme2()
Dim CurWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim myVals As Variant
Dim iCtr As Long

Set CurWks = Worksheets("sheet1")

With CurWks
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
For Each myCell In myRng.Cells
myVals = Split(myCell.Value, ",")
For iCtr = UBound(myVals) To LBound(myVals) Step -1
myCell.Offset(0, UBound(myVals) - iCtr + 1).Value _
= myVals(iCtr)
Next iCtr
Next myCell
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

MarvInBoise wrote:

I have comma-delimited data that I would like to parse right to left rather
than left to right; can do?

Thanks!
--
Marv Lusk

--

Dave Peterson

Reverse Text to Columns
 

WOW, the VB code is "T E R R I F I C"! Thanks Dave, just what we wanted!!
--
Marv Lusk


"Dave Peterson" wrote:

Maybe with a macro.

But if you have different number of elements in the range, what should happen?

For instance, if I have this in A1:A3
Column A
--------------
$A$1
$A$1,$A$2
$A$1,$A$2,$A$3

Should I end up with:

Column A B C D
-------------- ---- ---- ----
$A$1 $A$1
$A$1,$A$2 $A$2 $A$1
$A$1,$A$2,$A$3 $A$3 $A$2 $A$1

So cells with a fewer number of elements have those elements go to the far right
(kind of right justifying the values)???

If that's what you want:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myTRng As Range
Dim CurWks As Worksheet
Dim TmpWks As Worksheet
Dim NumberOfCols As Long
Dim iCol As Long

Set CurWks = Worksheets("sheet1")
Set TmpWks = Worksheets.Add

With CurWks
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
myRng.Copy _
Destination:=TmpWks.Range("a1")
End With

With TmpWks
Set myTRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
With myTRng
.TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False
End With

NumberOfCols = .UsedRange.Columns.Count

For iCol = NumberOfCols To 1 Step -1
With myRng.Offset(0, NumberOfCols - iCol + 1)
.Value = myTRng.Columns(iCol).Value
End With
Next iCol
End With

Application.DisplayAlerts = False
TmpWks.Delete
Application.DisplayAlerts = True

End Sub

It just copies the data to a temporary worksheet, then does data|text to
columns, then goes in reverse order to plop the columns back to the original
sheet. And then deletes the temp worksheet.


=============

If you want it to look like:

Column A B C D
-------------- ---- ---- ----
$A$1 $A$1
$A$1,$A$2 $A$2 $A$1
$A$1,$A$2,$A$3 $A$3 $A$2 $A$1

Kind of left justified???

Then try this:

Option Explicit
Sub testme2()
Dim CurWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim myVals As Variant
Dim iCtr As Long

Set CurWks = Worksheets("sheet1")

With CurWks
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
For Each myCell In myRng.Cells
myVals = Split(myCell.Value, ",")
For iCtr = UBound(myVals) To LBound(myVals) Step -1
myCell.Offset(0, UBound(myVals) - iCtr + 1).Value _
= myVals(iCtr)
Next iCtr
Next myCell
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

MarvInBoise wrote:

I have comma-delimited data that I would like to parse right to left rather
than left to right; can do?

Thanks!
--
Marv Lusk

--

Dave Peterson