Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Messy Text to Columns | Excel Discussion (Misc queries) | |||
Justify text across multiple columns | Excel Discussion (Misc queries) | |||
Arithmetical Mode of Criteria in Multiple Non-Adjacent columns | Excel Worksheet Functions | |||
Text to Columns / Importing Data | Excel Discussion (Misc queries) | |||
Sort or Filter option? | Excel Worksheet Functions |