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.

hBy2Py
  • 2,243
  • 14
  • 28
Some_Guy
  • 754
  • missing cell was a typo – Some_Guy Jun 10 '15 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 '15 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 '15 at 10:54
  • Thanks, that's pretty clear.  The whole "toast" thing got me off to a bad start. – Scott - Слава Україні Jun 10 '15 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 '15 at 11:32
  • 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. – Raystafarian Jun 10 '15 at 11:45
  • How about "left-stacked" and "right-stacked" as alternatives to "-aligned"? – hBy2Py Jun 10 '15 at 18:05

3 Answers3

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.

hBy2Py
  • 2,243
  • 14
  • 28
  • 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 '15 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 '15 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 '15 at 16:26
  • @Some_Guy Ah, good point -- just has to be consistent across all the data. – hBy2Py Jun 11 '15 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! – Scott - Слава Україні Jun 12 '15 at 01:11
  • @Some_Guy: If you want to mark this as "*the*" answer, click the check mark to the left (below the voting buttons).  See What should I do when someone answers my question? – Scott - Слава Україні Jun 12 '15 at 01:19
  • @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! – hBy2Py Jun 12 '15 at 01:39
  • @Brian thought I had, sorry – Some_Guy Jun 12 '15 at 14:23
  • @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. :-) – hBy2Py Jun 12 '15 at 14:31
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
Raystafarian
  • 21,743
  • 12
  • 62
  • 90
  • 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 '15 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? – Raystafarian Jun 11 '15 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.

Community
  • 1
Scott - Слава Україні
  • 21,717