Как правильно "выровнять" результаты функции Excel "Текст по столбцам"?

Question

Если я использую функцию «Текст в столбцы» для следующих данных, используйте «;» в качестве разделителя:

Foo, бар, QUX, Баз, тосты;
quux, джем, фасоль;

Я получу результаты "выровненные по левому краю" в результирующей сетке ячеек:

 |foo   |bar   |qux   |baz   |toast |
 |quux  |jam   |beans |      |      |

Однако я хочу, чтобы они были "выровнены по правому краю":

 |foo   |bar   |qux   |baz   |toast |
 |      |      |quux  |jam   |beans |

Как я могу это сделать?

ПРИМЕЧАНИЕ: я знаю, что «выровненный по правому краю» может быть неправильным термином, вместо этого подразумевается

|   foo|   bar|   qux|   baz| toast| 
|  quux|   jam| beans|      |      |

но это не то, что я ищу. Итак, если кто-то может предложить лучший термин для того, что я описываю, пожалуйста, сделайте это.

Приложение: В качестве альтернативного подхода, если кто-нибудь знает способ использования Excel для перестановки ячеек таким образом, чтобы

 |a   |b   |c   |d   |    |    |    |    |    |
 |n   |m   |o   |p   |q   |    |    |    |    |
 |e   |f   |g   |h   |i   |j   |k   |l   |    |
 |n   |m   |o   |p   |q   |    |    |    |    |
 |x   |    |    |    |    |    |    |    |    |

становится

 |    |    |    |    |    |a   |b   |c   |d   |
 |    |    |    |    |n   |m   |o   |p   |q   |
 |    |e   |f   |g   |h   |i   |j   |k   |l   |
 |    |    |    |    |n   |m   |o   |p   |q   |
 |    |    |    |    |    |    |    |    |x   |

тогда это тоже сработает.

Answer 1

Следующие формулы позволят быстро преобразовать ваши данные в форму, которую Text-to-Columns будет легко анализировать по правому краю, как вы описываете:

Формула D5 (добавляет точку с запятой, если отсутствует):

=IF(RIGHT(B5,1)<>";",B5&";",B5)

Формула G5 (добавляет необходимое количество точек с запятой):

=REPT(";",5-(LEN(D5)-LEN(SUBSTITUTE(D5,";",""))))&D5

Копирование результатов с последующей вставкой специальных значений должно дать сырье, подходящее для преобразования текста в столбцы.

Решение зависит от фиксированного максимального количества столбцов; здесь пять. Формула G5 может быть обобщена путем добавления ячейки «количество столбцов для генерации» в другом месте на листе и ссылки на эту новую ячейку вместо жестко заданного значения 5 .

Кроме того, если вы гарантируете, что у данных всегда будет конечная точка с запятой, промежуточный шаг D5:D7 является излишним.

РЕДАКТИРОВАТЬ: Согласно наблюдению Some_Guy в комментариях, метод также будет работать, если все строки построены так, что им не хватает конечной точки с запятой.

Answer 2

Как уже говорилось, нет, это не стандартная функция преобразования текста в столбцы или есть присущий мне способ сделать это в Excel, о котором я знаю. Однако этот VBA сделает это за вас (при условии, что между населенными клетками нет пробелов)-

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

Answer 3

Вот еще одна рутина VBA, чтобы сделать это.  Сделайте текст в столбцы, затем выберите прямоугольный диапазон, в который вы помещаете данные (то есть столбцы A - (максимум полей) × строки), и запустите этот макрос.  См. Как добавить VBA в MS Office? для учебного материала.

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

Это будет правильно обрабатывать встроенные пустые ячейки (например the;quick;;fox;).  В противном случае различия между этим ответом и другим просто произвольные личные предпочтения, а другой может быть лучше, чем я не понимаю.