Reverse Text to Columns

M

MarvInBoise

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

Thanks!
 
K

Ken Johnson

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

Thanks!
Click to expand...

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
 
D

Dave Peterson

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
 
Top