4

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.

7
  • missing cell was a typo
    – Some_Guy
    Jun 10, 2015 at 10:31
  • 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
    Jun 10, 2015 at 10:32
  • 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.
    – Some_Guy
    Jun 10, 2015 at 10:54
  • Thanks, that's pretty clear.  The whole "toast" thing got me off to a bad start. Jun 10, 2015 at 11:12
  • Yeah, that was my bad. Hopefully someone will have some ideas :/ Maybe a custom sort row-wise that only sorts blank vs not blank, treats rows independently and doesn't alphabetize? Wait come to think of it that sounds ugly, impractical and unworkable :(
    – Some_Guy
    Jun 10, 2015 at 11:32

3 Answers 3

4

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.

9
  • 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
    Jun 11, 2015 at 15:46
  • @Some_Guy Glad you like! Absolutely, I always give a fair bit of thought to a formula-based solution before resorting to VBA. The key thing here was coming up with the LEN(D5)-LEN(SUBSTITUTE(D5,";","")) bit, to provide a count of how many leading semicolons were needed.
    – hBy2Py
    Jun 11, 2015 at 15:59
  • 1
    if you want to edit your answer, it also works when data never has a trailing delimiter.
    – Some_Guy
    Jun 11, 2015 at 16:26
  • @Some_Guy Ah, good point -- just has to be consistent across all the data.
    – hBy2Py
    Jun 11, 2015 at 16:29
  • 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! Jun 12, 2015 at 1:11
2

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
2
  • 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
    Jun 11, 2015 at 15:56
  • 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? Jun 11, 2015 at 15:58
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.

2
  • 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
    Jun 11, 2015 at 15:48
  • Tested it and it works a charm btw
    – Some_Guy
    Jun 11, 2015 at 15:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .