How can I 'align right' the results of Excel's "Text to Columns" feature?

Question

If I am using the Text to Columns feature on the following data, using ";" as a delimiter:

foo;bar;qux;baz;toast;
quux;jam;beans;

I'll end up with the results "left aligned" in the resulting grid of cells:

 |foo   |bar   |qux   |baz   |toast |
 |quux  |jam   |beans |      |      |

However, I want them to be "right aligned":

 |foo   |bar   |qux   |baz   |toast |
 |      |      |quux  |jam   |beans |

How can I do this?

NOTE: I know that "right-aligned" might not be the correct term, instead implying

|   foo|   bar|   qux|   baz| toast| 
|  quux|   jam| beans|      |      |

but this isn't what I'm seeking. So, if anyone can suggest a better term for what I'm describing, please do so.

Addendum: As an alternative approach, if anyone knows a way to use Excel to rearrange cells such that

 |a   |b   |c   |d   |    |    |    |    |    |
 |n   |m   |o   |p   |q   |    |    |    |    |
 |e   |f   |g   |h   |i   |j   |k   |l   |    |
 |n   |m   |o   |p   |q   |    |    |    |    |
 |x   |    |    |    |    |    |    |    |    |

becomes

 |    |    |    |    |    |a   |b   |c   |d   |
 |    |    |    |    |n   |m   |o   |p   |q   |
 |    |e   |f   |g   |h   |i   |j   |k   |l   |
 |    |    |    |    |n   |m   |o   |p   |q   |
 |    |    |    |    |    |    |    |    |x   |

then that would also work.

hBy2Py · Accepted Answer · 2015-06-11T16:30:47.453

The following formulas will allow quick conversion of your data to a form that Text-to-Columns will readily parse right-justified as you describe:

Excel snip

D5 formula (appends a semicolon if absent):

=IF(RIGHT(B5,1)<>";",B5&";",B5)

G5 formula (prepends necessary number of semicolons):

=REPT(";",5-(LEN(D5)-LEN(SUBSTITUTE(D5,";",""))))&D5

Copying the results followed by a Paste-Special-as-Values should afford raw material suitable for a Text-to-Columns conversion.

The solution depends on there being a fixed maximum number of columns; here, five. The formula of G5 could be generalized by adding a 'number of columns to generate' cell elsewhere on the sheet and referencing this new cell instead of the hard-coded 5 value.

Additionally, if you are guaranteed that the data will always have the trailing semicolon, the intermediate step of D5:D7 is superfluous.

EDIT: Per Some_Guy's observation in the comments, the method will also work if all rows are constructed to lack a trailing semicolon.

score 2 · Answer 2 · answered Jun 10 '15 at 12:00

As stated, no this isn't a standard function of text to columns or is there an inherent way to do this in excel, that I know of. However, this VBA will do it for you (assuming you have no blanks between populated cells)-

Sub test()
Dim lrow As Integer
lrow = Cells(Rows.Count, "A").End(xlUp).Row

Dim lcol As Integer
lcol = Cells("1", Columns.Count).End(xlToLeft).Column

Dim lfcol As Integer
Dim dif As Integer
For i = 1 To lrow
    lfcol = Cells(i, Columns.Count).End(xlToLeft).Column
    dif = lcol - lfcol
    For j = lfcol To 1 Step -1
        If dif = 0 Then Exit For
        If Not Cells(i, j) Is Nothing Then
            Cells(i, j + dif) = Cells(i, j)
            Cells(i, j) = vbNullString
        End If
    Next
Next
End Sub

score 2 · Answer 3 · edited Mar 20 '17 at 10:17

Here’s another VBA routine to do it. Do your Text to Columns, then select the rectangular range that you put data into (i.e., columns A-(max fields) × rows) and run this macro. See How do I add VBA in MS Office? for instructional material.

Sub Copy_Right()
    For Each rr In Selection.Rows
        For cn = Selection.Columns.Count To 1 Step -1
            If Len(rr.Cells(1, cn)) > 0 Then Exit For
        Next cn
        ' cn is now the (relative) column number of the last cell in this row
        ' that contains (non-blank) data.
        my_offset = Selection.Columns.Count - cn
        ' my_offset is how many columns to the right we need to move.
        ' If my_offset = 0, the row is full of data (or, at least,
        ' the last column contains data; there may be blank cells
        ' to its left), so there’s nowhere to move it.
        ' If cn = 0, the row is empty, so there’s nothing to move.
        If cn = 0 Or my_offset = 0 Then
            ' Nothing to do.
        Else
            For cn = Selection.Columns.Count To 1 Step -1
                If cn > my_offset Then
                    ' Copy data to the right.
                    rr.Cells(1, cn) = rr.Cells(1, cn - my_offset)
                Else
                    ' Set the cells on the left to blank.
                    rr.Cells(1, cn) = ""
                End If
            Next cn
        End If
    Next rr
End Sub

This will handle embedded blank cells (e.g., the;quick;;fox;) correctly. Otherwise, differences between this answer and the other one are just arbitrary personal preference, and the other one may be superior in ways that I don’t understand.

How can I 'align right' the results of Excel's "Text to Columns" feature?

3 Answers3