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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Регулярные выражения

^{Google Таблица с примерами: Регулярные выражения}

^{https://mif.to/ywyPVn}

Регулярные выражения (Regular Expressions или RegExp) позволяют решать довольно сложные задачи по поиску, извлечению и замене текстовых строк. Они нужны для решения задач с динамическими текстовыми строками.

Для тривиальных задач с фиксированным текстом (например, когда нужно просто найти/заменить определенное слово) подходят и обычные текстовые функции Excel или Google Таблиц (как НАЙТИ / FIND или ПОДСТАВИТЬ / SUBSTITUTE) и окно «Найти и заменить».

Если нужно вырезать первые шесть символов (допустим, если в полном почтовом адресе всегда указан индекс, всегда в начале и всегда из шести символов), подойдет ЛЕВСИМВ / LEFT, которая извлекает заданное количество символов из начала текстовой строки в ячейке.

^{Кот Лемур, автор многих примеров в этой книге, изучает книгу «Регулярные выражения» Джеффри Фридла. Помимо нее он рекомендует книгу Бена Форта «Изучаем регулярные выражения»}

А вот для более сложных задач обычных функций уже может не хватить:

— извлечь дату из текстового описания транзакции;

— извлечь индекс из полного почтового адреса;

— проверить, есть ли в ячейке артикул товара;

— или извлечь этот самый артикул;

— найти товары с двумя или тремя портами HDMI в описании (2xHDMI или 3xHDMI), но не одним или более чем тремя (не 1xHDMI или что-то еще).

И тут на помощь могут прийти «регулярки». С помощью языка регулярных выражений можно задать паттерн, маску, шаблон — символьную строку, которой будут соответствовать определенные текстовые значения.