Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов
Там ноль, хотя внешне вроде бы числа извлеклись правильные.
Как превратить текст в число? Функция ЛЕВСИМВ / 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*Мур
будет соответствовать и вариант «ЛеМур» (но не «Лемур» — из-за регистра).
_____________________
{количество повторений} — в фигурных скобках можно задать точное число повторений
