Работа с текстом

Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

Назад: Промежуточные итоги и функция АГРЕГАТ / AGGREGATE

Дальше: Регулярные выражения

Файл с примерами: Текст.xlsx

ТЕКСТ В ЯЧЕЙКАХ EXCEL

Длина текстовой строки — это число символов (пробелов, цифр, букв, знаков, переходов на следующую строку).

Определить его можно функцией ДЛСТР / LEN.

Максимальная длина текста в одной ячейке Excel — 32 767 символов (на практике столько обычно и не нужно, это полноценная глава из книги по объему). В Google Таблицах ограничение еще больше — 50 000 символов.

В ячейках и формулах можно вставлять переход на следующую строку:

(Alt + Enter / ^ +

+ Return). Такой переход — это отдельный символ (то есть ячейка с ним и без него не равны друг другу, даже если остальные символы совпадают).

ТЕКСТ В ФОРМУЛАХ

Текст в формулах можно:

• указывать прямо в формуле, и тогда он (текст) обязательно должен быть в кавычках;

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

То есть =A7 — это ссылка на ячейку A7, а вот ="A7" — это формула, которая будет возвращать текст из двух символов A7.

Апостроф (') в начале ячейки превращает ее содержимое в текст (формула перестает вычисляться, число становится текстом и т. д.).

Для объединения нескольких значений в одну текстовую строку используется амперсанд (&).

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

РАЗДЕЛЕНИЕ ТЕКСТА ПО СТОЛБЦАМ

Для разделения текста по столбцам используется инструмент «Текст по столбцам» (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)

Меняют регистр текста на нижний (СТРОЧН), верхний (ПРОПИСН) или на каждое слово с заглавной (ПРОПНАЧ).

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

TEXTJOIN / ОБЪЕДИНИТЬ

Объединяет все значения из диапазона в один текст через указанный в первом аргументе разделитель. Второй аргумент — «пропускать пустые». Если он равен ИСТИНА, то не будут вставляться два подряд разделителя при наличии в диапазоне пустых ячеек.

Функция появилась только в Excel 2019.

В Google Таблицах она есть и называется TEXTJOIN (нет названия на русском).

ФУНКЦИИ ДЛЯ ИЗВЛЕЧЕНИЯ ФРАГМЕНТОВ ТЕКСТОВОЙ СТРОКИ

ЛЕВСИМВ / ПРАВСИМВ / ПСТР (LEFT / RIGHT / MID)

Извлекают из текста заданное число символов (из начала, конца или середины соответственно).

У функций ЛЕВСИМВ / LEFT и ПРАВСИМВ / RIGHT:

• первый аргумент — текст;

• второй аргумент — число символов (если его пропустить, то будет извлекаться один).

Если вам нужно извлечь фиксированное количество символов из ячейки в начале или конце строки — эти функции подойдут.

У функции ПСТР / MID:

• первый аргумент — текст;

• второй аргумент — с какой позиции в тексте извлекаются символы;

• третий аргумент — число символов.

ФУНКЦИИ ДЛЯ ПОИСКА ПОЛОЖЕНИЯ СИМВОЛА/ПОДСТРОКИ В ТЕКСТЕ

ПОИСК / НАЙТИ

(SEARCH / FIND)

Находят положение символа или текста в текстовой строке (ПОИСК — без учета регистра, НАЙТИ — с учетом):

• первый аргумент — что ищем;

• второй аргумент — где ищем;

• третий (необязательный) — с какого символа в тексте начинаем поиск.

ПСТР / MID можно использовать в сочетании с функциями поиска. Допустим, нам нужно извлечь цифровую часть артикула.

ПРАВСИМВ уже не подойдет, потому что у некоторых артикулов справа есть «хвост» из дефиса и букв.

ПСТР с фиксированным положением не подойдет: цифры идут после косой черты, и это всегда разная позиция в тексте — иногда 5, иногда 6, иногда 7, в зависимости от числа букв до черты.

Значит, нужно сначала находить положение косой черты с помощью функций НАЙТИ или ПОИСК. К этому результату можно добавить единицу, чтобы получить позицию первой цифры.

=НАЙТИ("/";ячейка с текстом) + 1

А далее подставим эту позицию в функцию ПСТР / MID в качестве начальной позиции:

=ПСТР(ячейка с текстом; НАЙТИ("/";ячейка) + 1; число извлекаемых символов)

ФУНКЦИЯ ПОДСТАВИТЬ / SUBSTITUTE

Заменяет один текст или символ на другой:

• первый аргумент — текст;

• второй аргумент — что в нем заменяем;

• третий аргумент — на что мы это заменяем.

Ее можно использовать для очистки текста от каких-то символов/значений. Если третий аргумент пустой (пустые кавычки), то мы просто удаляем подстроки, соответствующие второму аргументу.

ФУНКЦИЯ TEXT / ТЕКСТ ДЛЯ ФОРМАТИРОВАНИЯ ЧИСЕЛ

При объединении нескольких значений в одну текстовую строку (например, когда вы хотите к какой-то надписи добавить результат вычисления функции) исходные числовые форматы не сохраняются. Это значит, что у чисел не будет разделителей групп разрядов, будут все знаки после запятой (если они есть), даты превратятся в числа и так далее.

В таких случаях можно использовать функцию ТЕКСТ / TEXT, которая превращает значение (первый аргумент) в текстовую строку в заданном вами формате (во втором аргументе):

=ТЕКСТ(значение, которое нужно отформатировать; код формата)

Код формата указывается в кавычках. Это те же самые пользовательские форматы — во всем, кроме кодов цветов в квадратных скобках (цвета в функции ТЕКСТ не будут работать).

КАК ПРЕВРАТИТЬ ЧИСЛО В ТЕКСТОВОМ ФОРМАТЕ В НАСТОЯЩЕЕ ЧИСЛО

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

Там ноль, хотя внешне вроде бы числа извлеклись правильные.

Как превратить текст в число? Функция ЛЕВСИМВ / LEFT здесь для примера, на ее месте может быть любая функция, которая возвращает текстовую строку (из которой вам нужно сделать «настоящее» число, пригодное для вычислений).

1. С помощью двух минусов. Два раза умножив текст на минус единицу, мы меняем тип данных, не меняя значения:

=-ЛЕВСИМВ(…)

2. С помощью функции ЗНАЧЕН / VALUE:

=ЗНАЧЕН(ЛЕВСИМВ(…))

3. С помощью еще какой-нибудь математической операции, не меняющей значение, например умножения на единицу:

=ЛЕВСИМВ(…)*1

Назад: Промежуточные итоги и функция АГРЕГАТ / AGGREGATE

Дальше: Регулярные выражения