Код формата указывается в кавычках. Это те же самые пользовательские форматы — во всем, кроме кодов цветов в квадратных скобках (цвета в функции ТЕКСТ не будут работать).
Если функция возвращает текст, то результат вычисления будет текстовым, даже если состоит только из цифр. Например, если мы извлекаем суммы из текстовой строки, они не будут готовы к употреблению сразу — это будут текстовые значения, пусть и похожие на числа. См. сумму в столбце B.
Там ноль, хотя внешне вроде бы числа извлеклись правильные.
Как превратить текст в число? Функция ЛЕВСИМВ / LEFT здесь для примера, на ее месте может быть любая функция, которая возвращает текстовую строку (из которой вам нужно сделать «настоящее» число, пригодное для вычислений).
1. С помощью двух минусов. Два раза умножив текст на минус единицу, мы меняем тип данных, не меняя значения:
=-ЛЕВСИМВ(…)
2. С помощью функции ЗНАЧЕН / VALUE:
=ЗНАЧЕН(ЛЕВСИМВ(…))
3. С помощью еще какой-нибудь математической операции, не меняющей значение, например умножения на единицу:
=ЛЕВСИМВ(…)*1
Регулярные выражения
Google Таблица с примерами: Регулярные выражения
https://mif.to/ywyPVn
Регулярные выражения (Regular Expressions или RegExp) позволяют решать довольно сложные задачи по поиску, извлечению и замене текстовых строк. Они нужны для решения задач с динамическими текстовыми строками.
Для тривиальных задач с фиксированным текстом (например, когда нужно просто найти/заменить определенное слово) подходят и обычные текстовые функции Excel или Google Таблиц (как НАЙТИ / FIND или ПОДСТАВИТЬ / SUBSTITUTE) и окно «Найти и заменить».
Если нужно вырезать первые шесть символов (допустим, если в полном почтовом адресе всегда указан индекс, всегда в начале и всегда из шести символов), подойдет ЛЕВСИМВ / LEFT, которая извлекает заданное количество символов из начала текстовой строки в ячейке.
Кот Лемур, автор многих примеров в этой книге, изучает книгу «Регулярные выражения» Джеффри Фридла. Помимо нее он рекомендует книгу Бена Форта «Изучаем регулярные выражения»
А вот для более сложных задач обычных функций уже может не хватить:
— извлечь дату из текстового описания транзакции;
— извлечь индекс из полного почтового адреса;
— проверить, есть ли в ячейке артикул товара;
— или извлечь этот самый артикул;
— найти товары с двумя или тремя портами HDMI в описании (2xHDMI или 3xHDMI), но не одним или более чем тремя (не 1xHDMI или что-то еще).
И тут на помощь могут прийти «регулярки». С помощью языка регулярных выражений можно задать паттерн, маску, шаблон — символьную строку, которой будут соответствовать определенные текстовые значения.
Регулярные выражения поддерживаются разными языками программирования (например, JavaScript, Java, Perl, PHP) и разными инструментальными средствами (например, Word или Google Таблицы). К сожалению, в Excel нет встроенных функций для работы с «регулярками», но можно создать пользовательскую функцию на VBA. А вот в Google Таблицах такие функции есть «из коробки». Их три (названия у этих функций только на английском):
• REGEXMATCH для проверки текста на соответствие регулярному выражению (возвращает ИСТИНА / TRUE или ЛОЖЬ / FALSE);
• REGEXREPLACE для замены части текста, соответствующей регулярному выражению, на другой текст;
• REGEXEXTRACT для извлечения фрагмента текста, который соответствует регулярному выражению.
Кроме того, в Google Таблицах можно фильтровать данные с условием, заданным в виде регулярного выражения, с помощью функции QUERY.
Давайте рассмотрим основные элементы, из которых собираются шаблоны — регулярные выражения.
Здесь в примерах мы будем обозначать полужирным регулярные выражения, а полужирным курсивом — совпадения с ними в тексте.
Просто текст без всяких знаков — это вполне себе регулярное выражение, хоть и очень простое.
Лемур
Это совпадение с конкретным словом «Лемур» в тексте — именно в таком регистре (регулярные выражения чувствительны к регистру).
Привет,Лемур!
_____________________
. (точка) — любой символ. Например, «Ро. линг» — две конкретные буквы, далее любой символ, далее еще четыре конкретных буквы. Такое регулярное выражение будет соответствовать и фамилии «Роулинг», и другим вариантам написания с ошибкой (например, «Ровлинг»), и вообще любому варианту написания, где на третьей позиции будет любой символ.
Ро. линг
В некоторых ячейках была опечатка в фамилии Роулинг — она была указана как Ровлинг, а то и как Роуллинг.
«Роуллинг» нашему регулярному выражению не соответствует — на месте точки может быть только один символ.
Если вам нужно явным образом указать непосредственно символ точки в регулярном выражении, используйте обратную косую черту: \.
_____________________
[символы] — символы в квадратных скобках — это набор из нескольких символов, которые могут быть на соответствующей позиции в текстовой строке.
Например:
Ле[мд]ур — это один из вариантов «Лемур» или «Ледур», но не что-то иное.
Если кот вытворял что-то эдакое, мы могли в шутку пожурить его: "Лемур-Ледур", но быстро прощали любимого Ле Мура.
Можно использовать дефис, чтобы обозначать диапазоны символов. Например, [0–6] и [0123456] идентичны.
[^символы] — если перед набором символов добавить «крышечку» (^), то это будет отрицанием, то есть любым символом, кроме перечисленных в квадратных скобках.
_____________________
\d — любая цифра. Идентично выражению [0–9].
Регистр здесь имеет значение. \D — это уже любой символ, кроме цифры.
_____________________
\w — любая латинская буква в любом регистре, любая цифра или нижнее подчеркивание.
\W — любой символ, кроме перечисленных (то есть «не \w», не латинская буква, не цифра и не подчеркивание).
_____________________
\s — любой пробельный символ (непечатаемые символы: пробел, перевод строки, табуляция и другие).
\S — соответственно, любой не пробельный символ.
_____________________
| (вертикальная черта) — один из вариантов, «или — или». Например:
USD|EUR
Функция REGEXMATCH в Google Таблицах возвращает ИСТИНА / TRUE, если в ячейке (ссылка на нее — первый аргумент функции) есть текст, соответствующий регулярному выражению (указанному во втором аргументе этой функции)
_____________________
? (знак вопроса) — ничего или один указанный символ.
Например:
Ле\s?Мур
Такому выражению будут соответствовать и варианты с одним переносом строки, и с одним пробелом «Ле Мур», и слитное написание «ЛеМур». Варианты с несколькими пробелами не подойдут.
Слитное написание «Лемур» не будет соответствовать такому выражению — из-за регистра. Чтобы учесть варианты и «ЛеМур», и «Лемур», можно указать обе буквы в квадратных скобках, напомним: это означает один символ из набора.
_____________________
+ (плюс) — одно или несколько повторений символа.
Например, \s + — один или более пробелов.
Ле\s + Мур — будет соответствовать вариантам «Ле Мур», «Ле Мур» (много пробелов между словами), «Ле Мур» с переносом строки между словами, но не вариантам «ЛеМур» или «Лемур».
_____________________
* (звездочка) — ноль, одно или несколько повторений символа.
Если заменить в предыдущем примере плюс на звездочку, то такому регулярному выражению
Ле\s*Мур
будет соответствовать и вариант «ЛеМур» (но не «Лемур» — из-за регистра).
_____________________
{количество повторений} — в фигурных скобках можно задать точное число повторений символа. Например, \d{4} — четыре любые цифры.
Кроме того, можно задавать диапазон:
{1, 5} — от одного до пяти повторений;
{4,} — четыре и более повторений.
Например, Ро.{1,2}линг — это и Роулинг, и Ровлинг, и Ро линг, и Роуллинг. Но не Ролинг.
_____________________
^ («крышечка», или, как этот символ называет Лемур, карет) — начало текстовой строки.
Например, ^HP — “HP” в начале строки.
_____________________
Например, \d + $ — число в конце строки.
Извлекаем число в конце строки (функция REGEXEXTRACT в Google Таблицах не проверяет соответствие регулярному выражению, а извлекает соответствующее значение, если оно есть).
Там, где в конце строки не число, возвращается ошибка #N/A.
Без знака доллара (\d +) будет извлекаться первое число в тексте.
_____________________
(?i) — делает выражение нечувствительным к регистру.
Например, (?i)Лемур.
Это выражение будет соответствовать слову «Лемур» в любом регистре.
Выше мы обсуждали окно «Найти и заменить» — самое время напомнить, что в Google Таблицах в нем тоже можно использовать регулярные выражения! Для этого достаточно включить галочку «Поиск с использованием регулярных выражений» (Search using regular expressions).