Для разделения текста по столбцам используется инструмент «Текст по столбцам» (Text to Columns) из вкладки «Данные» (Data).
Выделите ячейки с текстом, который нужно разделить, и вызовите инструмент.
После выбора формата — с разделителем или фиксированной ширины — нужно будет указать разделитель или ширину (на скриншоте пример с разделителем).
В Google Таблицах этот инструмент называется «Разделить текст на столбцы» (Split text to columns) и находится в меню «Данные» (Data).
Кроме того, в Google Таблицах есть функция SPLIT, разделяющая текст (первый аргумент) по разделителю (второй аргумент).
А в Excel аналог SPLIT — TEXTSPLIT / ТЕКСТРАЗД — появился только в 2022 году у подписчиков Microsoft 365 (то есть этой функции нет даже в Excel 2021).
Вместе с ней в Excel появились функции TEXTBEFORE / ТЕКСТДО и TEXTAFTER / ТЕКСТПОСЛЕ, извлекающие текст до/после разделителя.
Если «Разделить текст на столбцы» разделяет данные как значения (один раз — при изменении исходных данных ничего не произойдет), то SPLIT или ТЕКСТРАЗД, будучи функцией, не влияют на исходный столбец, и результат ее вычисления будет пересчитываться при изменении исходных данных.
Если вам нужно разделить по переносу строк в Excel, введите в качестве разделителя Ctrl + J.
В Google Таблицах: введите в качестве разделителя в функции SPLIT другую функцию — СИМВОЛ(10) / CHAR(10). Эта функция возвращает разные символы по их кодам — коду 10 соответствует перенос строки.
«Найти и заменить»
Инструмент «Найти и заменить» (Find and Replace) во многих случаях помогает решить задачи по обработке текстовых значений (и не только) без применения сложных функций и формул. Это окно позволяет исправить большое количество формул, поменять форматирование всех однотипных ячеек, удалить определенные слова или символы из диапазона или из всей книги Excel.
Его можно вызвать сочетаниями клавиш Ctrl + F ( + F) или Ctrl + H (^ + H) — в обоих случаях откроется одно и то же диалоговое окно, но в первом случае на вкладке «Найти» (Find), а во втором — «Заменить» (Replace).
Если вы предварительно выделили диапазон ячеек, то поиск/замена будут производиться в пределах этого диапазона. Если же нет, то на листе или в книге (изменить этот параметр можно в поле «Искать» (Within) в окне «Найти и заменить»; по умолчанию будет лист).
Удаление и замена символов/слов
Чтобы удалить любой символ или слово из диапазона или всей книги, просто укажите его в поле «Найти» (Find what) и заменяйте его на ничто, то есть оставьте поле «Заменить» (Replace with) пустым.
Символы подстановки
В «Найти и заменить» (Find and Replace) в Excel можно использовать символы подстановки:
• * — любой текст, в том числе нулевой длины (то есть на месте звездочки может не быть ничего);
• ? — один любой символ (на месте знака вопроса обязательно должен быть символ).
Например, если вам нужно удалить любой текст в скобках (вместе с самими скобками), то в поле «Найти» нужно ввести:
(*)
А если нужно удалить все скобки, в которых внутри слова строго из 4 букв (или 4 цифры, или же 4 любых символа), нужно указать четыре знака вопроса в скобках:
(????)
Если вам нужно найти именно звездочки или знаки вопроса (например, чтобы удалить все звездочки в какой-то таблице), поставьте перед символом тильду (~):
~* — поиск звездочки,
— ? — поиск знака вопроса,
~~ — поиск самой тильды.
Удаление переносов строк
В ячейках Excel и Google Таблиц можно переходить на новую строку — сочетание клавиш Alt + Enter. Это отдельный символ, а не визуальный перенос, который задается с помощью параметра «Переносить текст» (Wrap Text).
Если вам нужно удалить переносы строк, введите в поле «Найти» Ctrl + J.
Изменение формул с помощью окна «Найти и заменить»
Окно «Найти и заменить» позволяет произвести изменения сразу с большим количеством формул. Например, вам нужно поменять диапазон или функцию во многих формулах. Выделите диапазон с формулами, вызовите окно «Найти и заменить» и введите в поле «Найти» (Find what) тот фрагмент формул, который вы хотите изменить, а в «Заменить на» (Replace with) — то, на что хотите его изменить. Убедитесь, что в списке «Область поиска» (Look in) заданы «Формулы» (Formulas).
Допустим, у вас есть несколько формул с расчетом сумм с условиями — через функцию СУММЕСЛИМН / SUMIFS. Вы решили изменить все расчеты на вычисление среднего — это делает функция с таким же синтаксисом, но другим названием — СРЗНАЧЕСЛИМН / AVERAGEIFS. Чтобы в целом диапазоне с формулами изменить все функции СУММЕСЛИМН на СРЗНАЧЕСЛИМН, достаточно будет следующих манипуляций в окне «Найти и заменить»:
Обратите внимание, что достаточно было бы заменять «СУММ» на «СРЗНАЧ». Но только в том случае, если вы уверены, что в выделенном диапазоне нет отдельных функций СУММ, которые в таком случае бы заменились на функции СРЗНАЧ. В нашем случае мы точно уверены, что будут изменяться только функции СУММЕСЛИМН.
Удаление пробелов
Для удаления лишних пробелов (в начале, в конце и всех, кроме одного между слов) используйте функцию СЖПРОБЕЛЫ / TRIM. Ее единственный аргумент — текст (ссылка на ячейку с текстом, как правило).
Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите.
Удаление непечатаемых символов
Для удаления непечатаемых символов (табуляция, перенос строки, другие символы, которые не отображаются на экране) используйте функцию ПЕЧСИМВ / CLEAN.
СТРОЧН / ПРОПИСН / ПРОПНАЧ (LOWER / UPPER / PROPER)
Меняют регистр текста на нижний (СТРОЧН), верхний (ПРОПИСН) или на каждое слово с заглавной (ПРОПНАЧ).
Единственный аргумент в каждом случае — текст (обычно заданный в виде ссылки на ячейку, чтобы протянуть формулу по всей таблице, но технически это может быть и текст в кавычках, и результат объединения текста из нескольких ячеек/функций).
Объединяет все значения из диапазона в один текст через указанный в первом аргументе разделитель. Второй аргумент — «пропускать пустые». Если он равен ИСТИНА, то не будут вставляться два подряд разделителя при наличии в диапазоне пустых ячеек.
Функция появилась только в Excel 2019.
В Google Таблицах она есть и называется TEXTJOIN (нет названия на русском).
ЛЕВСИМВ / ПРАВСИМВ / ПСТР (LEFT / RIGHT / MID)
Извлекают из текста заданное число символов (из начала, конца или середины соответственно).
У функций ЛЕВСИМВ / LEFT и ПРАВСИМВ / RIGHT:
• первый аргумент — текст;
• второй аргумент — число символов (если его пропустить, то будет извлекаться один).
Если вам нужно извлечь фиксированное количество символов из ячейки в начале или конце строки — эти функции подойдут.
У функции ПСТР / MID:
• первый аргумент — текст;
• второй аргумент — с какой позиции в тексте извлекаются символы;
• третий аргумент — число символов.
ПОИСК / НАЙТИ
(SEARCH / FIND)
Находят положение символа или текста в текстовой строке (ПОИСК — без учета регистра, НАЙТИ — с учетом):
• первый аргумент — что ищем;
• второй аргумент — где ищем;
• третий (необязательный) — с какого символа в тексте начинаем поиск.
ПСТР / MID можно использовать в сочетании с функциями поиска. Допустим, нам нужно извлечь цифровую часть артикула.
ПРАВСИМВ уже не подойдет, потому что у некоторых артикулов справа есть «хвост» из дефиса и букв.
ПСТР с фиксированным положением не подойдет: цифры идут после косой черты, и это всегда разная позиция в тексте — иногда 5, иногда 6, иногда 7, в зависимости от числа букв до черты.
Значит, нужно сначала находить положение косой черты с помощью функций НАЙТИ или ПОИСК. К этому результату можно добавить единицу, чтобы получить позицию первой цифры.
=НАЙТИ("/";ячейка с текстом) + 1
А далее подставим эту позицию в функцию ПСТР / MID в качестве начальной позиции:
=ПСТР(ячейка с текстом; НАЙТИ("/";ячейка) + 1; число извлекаемых символов)
Заменяет один текст или символ на другой:
• первый аргумент — текст;
• второй аргумент — что в нем заменяем;
• третий аргумент — на что мы это заменяем.
Ее можно использовать для очистки текста от каких-то символов/значений. Если третий аргумент пустой (пустые кавычки), то мы просто удаляем подстроки, соответствующие второму аргументу.
При объединении нескольких значений в одну текстовую строку (например, когда вы хотите к какой-то надписи добавить результат вычисления функции) исходные числовые форматы не сохраняются. Это значит, что у чисел не будет разделителей групп разрядов, будут все знаки после запятой (если они есть), даты превратятся в числа и так далее.
В таких случаях можно использовать функцию ТЕКСТ / TEXT, которая превращает значение (первый аргумент) в текстовую строку в заданном вами формате (во втором аргументе):
=ТЕКСТ(значение, которое нужно отформатировать; код формата)