Solved: Text-to-column but right to left

[RKramkowski]

I have a spreadsheet (80,000 lines) that has a field made up of comma delimited values, but not each field has the same number of values. Normally the text-to-column function creates the columns going from left to right. I need the single column expanded out so the last values from all fields line up in the rightmost column, working back to the left.

See the attached test file for an example of what I'm trying to achieve. It's probably simple but I'm relatively new to Excel.

Thanks,
Bob

[Tinbendr]

There's gotta be a better way to do this, but this is what I came up with.

Select the cells to convert.

Sub JustifyRightTextToColumns()
Dim aRow As Range
Dim Counter As Integer
Dim HighCount As Integer

With Selection
'Count each cell and look for comma
For Each aRow In .Rows
For A = 1 To Len(aRow)
If Mid(aRow, A, 1) = "," Then
Counter = Counter + 1
'Secondary counter that keeps the
'highest comma count of all cells.
If HighCount < Counter Then
HighCount = HighCount + 1
End If
End If
Next
Counter = 0
Next

'Count through them again,
'but add the necessary commas to the left.
For Each aRow In .Rows
For A = 1 To Len(aRow)
If Mid(aRow, A, 1) = "," Then
Counter = Counter + 1
End If
Next
If Counter < 6 Then
ActiveSheet.Cells(aRow.Row, 1).Value = _
Left(",,,,,,", 6 - Counter) & aRow
End If

Counter = 0
Next

.TextToColumns DataType:=xlDelimited, comma:=True

End With
End Sub

[mdmackillop]

Similar methodology

Option Explicit
Sub RightAlign()
Dim Commas As Long, x As Long, cel As Range
For Each cel In Selection
If UBound(Split(cel, ",")) > Commas Then Commas = UBound(Split(cel, ","))
Next
For Each cel In Selection
x = Commas - UBound(Split(cel, ","))
If x > 0 Then cel = Application.Rept(",", x) & cel
Next
Selection.TextToColumns DataType:=xlDelimited, comma:=True
End Sub

[mikerickson]

A formula based solution.

If your data is in A1:A10, add a helper column with the CSE formula
=REPT(",",MAX(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,",","")))-LEN(A1)+LEN(SUBSTITUTE(A1,",","")))& A1

Then copy/paste special values and use TextToColumns (comma delimited) on the helper column.

[RKramkowski]

Thanks for all your help. The various solutions worked as advertised.

Bob