Разделение и очистка неструктурированных данных

Импортированные данные редко бывают аккуратными. В одной ячейке может находиться John,Smith,Sales, хотя на самом деле нужны три отдельных столбца.

Функция SPLIT в Google Таблицах разделяет строку по разделителю и разливает фрагменты по соседним ячейкам.

В этом заключительном уроке Вы объедините SPLIT и CLEAN с только что освоенными навыками работы с регулярными выражениями, чтобы полностью привести неаккуратный текст в порядок.

SPLIT(text, delimiter) разделяет текст везде, где встречает разделитель, и выводит каждый фрагмент в отдельную ячейку справа.

Формула ниже разделяет A2 по запятым. John,Smith,Sales разливается в три ячейки: John, Smith и Sales.

По умолчанию каждый символ в строке разделителя считается отдельным разделителем. Поэтому SPLIT(A2, ",;") разделяет по запятым и точкам с запятой.

Формула ниже разделяет по запятой или пробелу, что удобно, когда разделители в разных строках непоследовательны.

У SPLIT есть необязательные аргументы. Третий определяет, разделяет ли каждый символ разделителя отдельно, а четвёртый — удаляются ли пустые результаты.

По умолчанию пустые части отбрасываются, что может сдвинуть столбцы. Формула ниже сохраняет пустые части, передавая FALSE в качестве последнего аргумента и сохраняя выравнивание столбцов для A,,C.

Текст, вставленный из PDF-файлов или веб-страниц, часто содержит невидимые управляющие символы, которые нарушают работу формул и поиска.

Функция CLEAN удаляет из строки большинство непечатаемых символов. Формула ниже возвращает A2 без этих помех, поэтому значение, которое выглядело одинаково, но никогда не совпадало, наконец начинает работать как нужно.

CLEAN обрабатывает управляющие символы, но не лишние пробелы. Объедините её с TRIM, которая удаляет начальные, конечные и повторяющиеся пробелы, а затем выполните разделение.

Формула ниже очищает управляющие символы, убирает лишние пробелы и разделяет результат по запятым в одном выражении. Читайте её изнутри наружу.

В столбце A находятся неаккуратные записи вроде Smith , John с лишними пробелами. Вам нужны аккуратные имя и фамилия в отдельных ячейках.

Формула ниже убирает лишние пробелы и разделяет текст по запятой. Чтобы также очистить каждый фрагмент, можно после разделения обернуть отдельные результаты в TRIM, но предварительной очистки обычно достаточно.

SPLIT требует буквального разделителя. Если разделители различаются, сначала приведите их к единому виду с помощью REGEXREPLACE, а затем разделите по стандартному.

Формула ниже заменяет любую последовательность пробелов, запятых или точек с запятой одной вертикальной чертой, а затем разделяет текст по этой черте. Это позволяет аккуратно обработать даже крайне непоследовательные исходные данные.

Иногда нужен только фрагмент ячейки, который затем надо разделить. Из Tags: red, blue, green нужно получить только цвета в отдельных ячейках.

Формула ниже сначала извлекает всё после двоеточия, а затем разделяет результат по запятой. Именно сочетание инструментов регулярных выражений и разделения раскрывает их настоящую мощь.

Для большинства неаккуратных импортированных данных хорошо работает такой порядок:

• CLEAN для удаления управляющих символов
• TRIM для исправления пробелов
• REGEXREPLACE для приведения разделителей к единому виду или удаления ненужных символов
• SPLIT для разделения на столбцы

Формула ниже последовательно применяет все четыре шага к одной ячейке.

По умолчанию SPLIT разливает фрагменты по столбцам. Иногда вместо этого нужен вертикальный список — по одному значению в строке, готовый для фильтрации или подсчёта.

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

Проверьте, насколько хорошо Вы поняли разделение и очистку.

Вы завершили курс, объединив инструменты разделения и очистки в Google Таблицах:

• SPLIT(text, delimiter) разделяет ячейку на столбцы, заполняемые результатами
• Дополнительные аргументы управляют разделителями из нескольких символов и пустыми частями
• CLEAN удаляет невидимые управляющие символы, а TRIM исправляет пробелы
• Используйте REGEXREPLACE, чтобы привести неаккуратные разделители к единому виду перед разделением
• Объединяйте CLEAN, TRIM, REGEXREPLACE и SPLIT в повторяемую последовательность очистки

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