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.
73 Answers
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.
9As 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 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.
