В столбце A Feroz есть ряд текстовых значений. Эти значения отформатированы двумя разными цветами шрифта в каждой ячейке. (Первая часть текста одного цвета, а вторая часть другого цвета.) Он хотел бы разделить эти текстовые значения на столбцы B и C, чтобы все, что было первым цветом, находилось в столбце B, а все, что было второго цвета, было в столбце C. Он отмечает, что инструмент «Текст в столбцы» не справится с этим, поэтому он задается вопросом, можно ли это сделать.

Да, это может быть сделано. Один из способов — использовать встроенную функцию Flash Fill в Excel. (Этот инструмент доступен только в Excel 2013 или более поздних версиях.) Допустим, вы начинаете с данных, которые выглядят следующим образом:

(См. Рис. 1.)

image

Рисунок 1. Ваши разноцветные данные.

Обратите внимание, что мои данные тестирования включают в столбце E некоторые характеристики данных в столбце A. На этом этапе все, что вам нужно сделать, это дать Flash Fill то, с чем он может работать. Я делаю это, вручную разбивая текст в строках 2 и 3, как показано здесь: (См. Рис. 2.)

image

Рисунок 2. Настройка примеров.

Важно, чтобы примеры, которые вы создаете в B2: C3, были точными — они должны включать все, что имеет любой цвет (включая начальные и конечные пробелы), а орфография и использование заглавных букв должны быть правильными.

Теперь выберите ячейку B4 и нажмите Ctrl + E. Это приводит в действие Flash Fill, и вы увидите, что текст отображается в оставшейся части столбца B. Сделайте то же самое в столбце C — выберите ячейку C4 и нажмите Ctrl + E. Ваши результаты должны выглядеть примерно так: (См. Рис. 3.)

image

Рисунок 3. После использования Flash Заполните оба столбца.

Я должен отметить, что ваш успех с Flash Fill будет в значительной степени зависеть от характеристик данных в столбце A. В большинстве случаев он будет выполнять большую часть работы, и он может значительно выполнить задачу. Могут быть некоторые странные случаи, когда Flash Fill не может понять, как он должен разделять ваши данные. Вы можете видеть это на предыдущем рисунке, где ячейки A7, A9 и A11 не были правильно разделены. Вы захотите внимательно проверить свои результаты, чтобы убедиться, что они имеют смысл.

Если Flash Fill у вас не работает, вам нужно создать макрос для выполнения этой работы. Можно придумать макрос, который будет работать со всеми ячейками в столбце A и вытягивать текст в столбцы B и C (как и нужно Ферозу), но более гибко можно создать пользовательскую функцию, которая будет возвращать все, что угодно. разыскивается из камеры. Вот пример:

Function SplitColors(r As Range, Optional iWanted As Integer = 1) _   As String     Dim sTemp As String     Dim J As Integer     Dim K As Integer     Dim iColors(9) As Integer

sTemp = ""

If r.Cells.Count = 1 Then         For J = 1 To 9             iColors(J) = 0         Next J

' Determine where colors change         ' Remember there will always be at least one color         K = 1         iColors(K) = 1         For J = 2 To Len(r.Text)

If r.Characters(J,1).Font.Color <> _               r.Characters(J-1,1).Font.Color Then                 K = K + 1                 iColors(K) = J             End If         Next J

' Check if wanted color is less than total colors         If iWanted <= K Then             J = iColors(iWanted + 1)

If J = 0 Then J = Len(r.Text) + 1             J = J - iColors(iWanted)

sTemp = Mid(r.Text, iColors(iWanted), J)

End If     End If     SplitColors = sTemp End Function

Для функции SplitColors требуется один параметр (диапазон действия)

и второй необязательный параметр (какой цвет из желаемого диапазона).

Функция сначала проверяет, была ли передана одна ячейка. Если да, то он определяет, сколько цветов находится в этой ячейке, и номера символов, в которых происходит изменение цвета. Затем, если желаемый цвет (переданный в необязательном втором параметре) меньше количества цветов в ячейке, возвращаются символы, использующие этот цвет.

Так, например, если вы хотите вернуть ячейки, используя первый цвет в ячейке A2, вы можете использовать на своем листе одно из следующих действий:

=SplitColors(A2, 1)

=SplitColors(A2)

Второй вызов работает, потому что второй параметр является необязательным. Если вы не включите его, функция предполагает, что вы хотите работать с первым цветом. Если вы хотите вернуть текст, используя второй цвет в ячейке, то будет работать следующее:

=SplitColors(A2, 2)

Функция SplitColors будет работать с 9 цветами в любой ячейке, которую вы проверяете. Если вы укажете второй параметр, равный 9 или больше, вы получите ошибку.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13605) применим к Microsoft Excel 2013, 2016, 2019 и Excel в Office 365.