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,";",""))))&D5Copying 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 SubHere’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 SubThis 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.
