☰

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

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 vba format import
Как можно «выровнять по правому краю» результаты функции Excel «Текст по столбцам»?

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

foo;bar;qux;baz;тост;
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   |

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

решение1

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

Excel фрагмент

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

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

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

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

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

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

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

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

решение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

решение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;). В противном случае различия между этим ответом и другим являются просто произвольными личными предпочтениями, и другой может быть лучше в тех аспектах, которые я не понимаю.

Связанный контент