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.

Solution 1

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:

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.

Solution 2

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.

Solution 3

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

Comments

[Some_Guy]

missing cell was a typo

[Some_Guy]

And as for your second question, text to columns is a feature that already exists, and outputs the split delimited text line by line, giving each record its own cell in a left to right manner (try it) I wish to output the data in cells in a right to left fashion. If you are asking me how to command excel to do this then you're asking what I'm asking in the question...

[Some_Guy]

For a delimited dataset with variable character lengths where the length of each row is given by k and the length of the longest row is given by n. The value in the kth column should go into the nth column, the value in the k-1 th column should go in the n-1 th column, etc. If you think this is not clear by extrapolation from the question, please tell me. In my experience people prefer examples to a rigorous definition. I've tried to make the question clear as possible, I'm just unsure what the correct terminology for what I need is. Alignment seems closest.

[Scott - Слава Україні]

Thanks, that's pretty clear.  The whole "toast" thing got me off to a bad start.

[Raystafarian]

No, you can't "text-to-columns" fill right-to-left as a standard text to columns function in excel. You'll need vba after the text to columns, or you'll need to use a series of formulas.

[Some_Guy]

I have to mark this as the answer just due to its sheer simplicity. One of those "why didn't I think of that" moments! Very elegant, thanks :D VBA were both very appreciated. but I always try to avoid VBA wherever possible (emailing spreadsheets etc.) so this answer meets my needs the most

[Some_Guy]

In the end I used the VBAless solution, but the well annotated code was very appreciated (for someone like me who isn't "literate" in VBA) Thanks!

[Some_Guy]

I copied this code into a new module, higlighted my data and ran, but I get application-defined or object-defined error. This might be nothing to do with your code but can you help me out?

[Some_Guy]

Tested it and it works a charm btw

[Raystafarian]

It's assuming your data is in column A through n, no need to make a selection. If your data is elsewhere, it needs to to be told so. Not sure what the error is, what line does it highlight?

[hBy2Py]

@Some_Guy Ah, good point -- just has to be consistent across all the data.

[Scott - Слава Україні]

Congratulations on figuring out the LEN()-LEN(SUBSTITUTE()) trick; but, if you browse Super User, you’ll see that it’s somewhat well known. You don't need to hard-code the 5; you can use the array formula =MAX(LEN(D5:D7) - LEN(SUBSTITUTE(D5:D7,";",""))).  You should probably put it into a separate cell for clarity and simplicity; type the formula (or copy it from this comment and paste) into the formula bar, and then press (Ctrl)+(Shift)+(Enter) to make it an array formula — curly braces ({...}) will appear around it.  Also, @Brian, congratulations on reaching 1000 reputation points!

[hBy2Py]

@Scott Nice extension! Makes perfect sense. Is not surprising to me that the SUBSTITUTE trick isn't new -- as you say, I've found out through browsing SuperUser that most of my tricks that I thought were really clever are actually well known, or there are better ways of doing things. And, thanks!

[Some_Guy]

@Brian thought I had, sorry

[hBy2Py]

@Some_Guy No sweat, I was glad that you were so engaged in responding to all of the answerers. So often askers don't even leave comments saying whether something worked or not. Definitely appreciate the marked answer, though. :-)