Эта функция позволяет сделать строчные (маленькие) буквы прописными (заглавными), иными словами, перевести их в верхний регистр. Символы в верхнем регистре и символы, для которых это неприменимо, не изменяются.
Первые слова ячеек целиком заглавными буквами (“капсом”)
Наиболее часто такое может быть полезно при работе с ФИО в Excel. Фамилию обычно пишут целиком заглавными, а имя и отчество – просто с заглавной.
Допустим, в исходном списке все слова уже с заглавной буквы. Тогда формула потребует взять первое слово ячейки (в данном случае фамилию), преобразовать его в верхний регистр и добавить к нему остальные (имя и отчество).
Казалось бы, что может быть проще, но вот поиск той самой первой буквы в ячейке так еще проблема, потянувшая на целую статью. А вот финальная формула массива оттуда:
Сделать прописными первые буквы предложений в Excel
Формула выше сделает прописной только одну, первую букву ячейки.
А если мы хотим сделать заглавными первые буквы предложений, которых в ячейке может быть несколько? Первую букву второго предложения и далее эта формула не определит и, следовательно, не решит задачу.
Исправляем регистр предложений, начиная их с заглавной буквы.
Понравилась статья? Поддержите ее автора! Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
Все буквы кроме первой нам также поможет извлечь ПСТР (здесь 1000 – число, заведомо превышающее возможное количество символов в ячейке):
=ПСТР(A1;2;1000)
Ну а итоговая формула будет составлена из вышеуказанных с обработкой функциями СТРОЧН и ПРОПИСН:
=ПРОПИСН(ПСТР(A1;1;1))&СТРОЧН(ПСТР(A1;2;1000))
=ПРОПИСН(ЛЕВСИМВ(A1;1))&СТРОЧН(ПСТР(A1;2;1000))
Как видно из примера, эта комбинация не всегда дает тот результат, который хотелось бы получить, работая с форматированным текстом (подсвечено желтым)
Ведь фактически она не делает заглавными первые буквы ячеек, а делает заглавным первый символ, который не всегда бывает буквой. А если в строке несколько предложений, второе и далее функция не определит и не сделает первые символы в них заглавными.
Исправляем регистр предложений, начиная их с заглавной буквы.
Функция СТРОЧН, другие примеры
Функция СТРОЧН кажется такой незамысловатой и поэтому не такой уж и нужной. Но тут и парадокс – простота и и прямолинейность функции как раз и делают её востребованной в множестве задач.
Вот лишь те задачи, которые есть на этом сайте, и которые в Excel помогает решить функция СТРОЧН:
Понравилась статья? Поддержите ее автора! Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
Эта функция преобразует целое число в символ, который ему соответствует в текущей кодировке ANSI, используемой операционной системой.
ВАЖНО: ANSI-кодировка – это собирательное понятие, их (кодировок) очень много, и таблица сопоставления чисел и символов будет разной в зависимости от того, какая указана в реестре вашей операционной системы.
Наиболее часто на русскоязычных системах Windows установлена кодировка Windows-1251 (она же cp1251).
Синтаксис функции СИМВОЛ
=СИМВОЛ(число)
Число должно быть от 1 до 255, в иных случаях функция выдаст ошибку #ЗНАЧ!
Коды символов и соответствующие им символы
Таблица ниже поможет быстро сориентироваться, когда нужны редко используемые символы. Наиболее востребованные из них:
Полная таблица сопоставления символов и их кодов в кодировке Windows-1251
Функция СИМВОЛ – примеры
Помимо использования строго по назначению, функция СИМВОЛ может использоваться для иных задач в Excel, включая такие, где выступает вспомогательной.
Создать алфавит функцией СИМВОЛ в Excel
Удобный способ не вводить буквы с клавиатуры самостоятельно.
В зависимости от того, какой алфавит вы хотите создать в Excel, понадобятся разные диапазоны чисел.
Для английского языка это диапазоны 65-90 (заглавные) и 97-122 (строчные). Для русского алфавита – 192-223 и 224-255 соответственно.
По какой-то причине буква Ё не входит ни в один из этих диапазонов (см. таблицу выше), иными словами, русский алфавит в кодировке 1251 будет неполон, если взять только подряд идущие цифры. Код заглавной Ё – 168, строчной – 184.
Понравилась статья? Поддержите ее автора! Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
Эта функция сравнивает два элемента текста и определяет, являются ли они абсолютно одинаковыми.
Учитывается регистр символов, только слова, написанные одинаково и имеющие символы верхнего и нижнего регистра в одной и той же позиции, будут считаться равными.
Синтаксис
=СОВПАД(Текст1, Текст2)
Можно сравнить только два аргумента.
Форматирование
Как и все текстовые функции, функция преобразует числовой формат в натуральное число перед сравнением. Поэтому функция считает 200% и 2 (изначально в числовом формате) совпадающими текстовыми значениями.
Это касается форматирования как даты, даты-времени, времени, процентного и финансового формата.
СОВПАД – примеры формул
Умение различать регистр символов делает функцию крайне важной при работе с текстом в Excel. Разберем пару примеров.
Если описать итоги кратко, нам поможет логика и функция СТРОЧН:
переведем значение ячейки в нижний регистр
сравним результат с изначальным значением ячейки
если вернется ЛОЖЬ, – хотя бы одна заглавная буква была.
Вуаля!
А вот и формула:
=НЕ(СОВПАД(СТРОЧН(A1), A1))
Найти любые строчные буквы поможет аналогичная формула, но с функцией ПРОПИСН:
=НЕ(СОВПАД(ПРОПИСН(A1), A1))
Найти, есть ли буквы в ячейке
Если проверить, совпадают ли значения ячейки в верхнем и нижнем регистре, можно выявить, есть ли в ней буквы. Если буква была в нижнем, станет верхним, а если в верхнем – в нижнем, и только если букв в ячейке нет, она останется такой же, как была. Иероглифы не в счет.
Логично ведь, правда? :)
Соответствующая формула:
=НЕ(СОВПАД(ПРОПИСН(A1), СТРОЧН(A1)))
Другие примеры
Функция СОВПАД встречается и в других интересных формулах, смотрите статьи по ссылкам:
Понравилась статья? Поддержите ее автора! Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
Функцию часто называют “Длина строки”, что не совсем корректно, потому что речь не о строке, а о строковом выражении значения аргумента.
Эта функция позволяет посчитать количество символов в ячейке, включая пробелы и цифры, в этом самом строковом выражении. Обратите внимание на примеры ниже.
Хотя визуально 2500% это 5 символов, при преобразовании в текстовую строку это всего лишь 25, поэтому ДЛСТР возвращает 2.
Даты преобразуются в целые числа, где единицей является 1.01.1900 – вот почему для неё функция возвращает длину 1.
Есть символ, который функция ДЛСТР вообще не учитывает. Это апостроф (‘), когда он в начале ячейки. Если в начале ячейки идут два символа апострофа подряд, второй уже учитывается.
Аргумент у функции один, он обязательный. В нем можно указывать как одну ячейку, так и другие вычисления с помощью формул. При использовании в функциях массива аргументом может быть диапазон ячеек.
ДЛСТР – форматирование
Если входящее значение не в текстовом формате, оно конвертируется в него.
Ячейки в формате даты воспринимаются как числа, поэтому содержат обычно 5 знаков (если это не даты, близкие к 1900 году).
Процентный формат также конвертируется в натуральное число, например, 2500% = 25 (2 символа, см. таблицу примеров выше).
Примеры формул, применение ДЛСТР
Функция часто используется как вспомогательная в составных формулах, в комбинации с другими текстовыми функциями.
При этом эти комбинации позволяют решать самые необычные задачи.
Пример 1. Посчитать слова
Как узнать, сколько слов в ячейке Excel? Обычно их на 1 больше, чем пробелов между ними. Если пробел один, значит слов – два, если два – то слов три, и так далее.
Если есть вероятность, что пробелов больше 1 между словами, или они есть в начале и конце ячейки, их можно устранить функцией СЖПРОБЕЛЫ.
Пробелы в строке можно посчитать, измерив длину строки в символах:
Так будет выглядеть формула для ячейки A1, учитывающая все эти нюансы. СЖПРОБЕЛЫ удаляет лишние пробелы, ПОДСТАВИТЬ удаляет их, а ДЛСТР измеряет длину строк:
Пример 2 – посчитать встречаемость фрагмента в тексте
Есть ли в тексте определенный символ? И если есть, то сколько их?
Рассмотрим пару примеров формул.
Есть ли в ячейке лишние пробелы
Формула ниже проверит, есть ли в ячейке лишние пробелы. Это удобно, когда нужно не просто удалить пробелы, а узнать, были ли они вообще:
=ДЛСТР(СЖПРОБЕЛЫ(A1))<>ДЛСТР(A1)
Формула проверки наличия лишних пробелов в строке
Формула очевидна и довольно проста. Если длина ячейки после удаления лишних пробелов функцией СЖПРОБЕЛЫ равна исходной – значит, их в ней нет, а если наоборот, не равна – есть.
Обратите внимание, что, несмотря на то, что в самой постановке вопроса звучит условие ЕСЛИ, функция ЕСЛИ тут не нужна. Если равенство верное, Excel сам вставит в ячейку результат ИСТИНА, и ЛОЖЬ, если наоборот.
Сколько раз встречается символ или слово в тексте
По некой аналогии с предыдущим примером, мы можем производить не булевое (ДА-НЕТ, ИСТИНА-ЛОЖЬ) сравнение длин строки перед и после удаления символа или фрагмента, а вычислить разницу.
Если мы ищем один символ, это позволит сразу понять его встречаемость.
Для удаления произвольного символа из строки нам понадобится функция ПОДСТАВИТЬ.
Подсчет встречаемости буквы в ячейке
Если же текстовый фрагмент длиной 2 и более символов – нам потребуется еще и поделить полученный результат на его длину:
=(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;B1;""))/ДЛСТР(B1)
В данной формуле исходный текст в ячейке A1, а подсчитываемый фрагмент – в ячейке B1.
Подсчет встречаемости текстового фрагмента
Обратите внимание на 5 и 6 строки – поскольку функция “ПОДСТАВИТЬ” чувствительна к регистру, из текста ею не было удалено первое вхождение. Если нужно учитывать вхождения в любом регистре, могут понадобиться функции СТРОЧН или ПРОПИСН.
Подсчитываемый текстовый фрагмент может быть любой длины.
Еще момент – функция может быть непригодна для подсчета коротких слов, т.к. одно слово может быть частью другого.
Пример 3 – извлечь или удалить последнее слово
Зная количество пробелов в тексте, можно заменить последний из них на редкий символ, чтобы потом вычислить его позицию.
С этим нам, как и в предыдущем примере, поможет функция ПОДСТАВИТЬ – она позволяет заменить не только все определенные текстовые фрагменты в строке, но и определенное по порядку вхождение (первое, второе и т.д.).
Что это нам дает? Последний пробел – не что иное, как символ, отделяющий последнее слово от остальной части строки. И по его позиции далее уже нехитрыми манипуляциями удалить или извлечь его.
Здесь поможет формула массива на основе комбинации функции ДЛСТР и функции СУММ. Первая создаст массив значений длины каждой из ячеек диапазона, а вторая просуммирует эти значения:
={СУММ(ДЛСТР(ДИАПАЗОН))}
Функция ДЛСТР в формуле массива
Аналогичным способом функциями МАКС и МИН можно посчитать длины самой длинной и самой короткой строк в диапазоне.
Фигурные скобки вводить не нужно, они появятся сами при вводе формулы массива сочетанием клавиш Ctrl + Shift + Enter (вместо обычного Enter).
Понравилась статья? Поддержите ее автора! Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
ЗАМЕНИТЬ — это текстовая функция, которая позволяет заменить часть строки на другой текст по заданной позиции. Это особенно полезно, если нужно изменить определённое количество символов, начиная с конкретной позиции, например, маскировать номер телефона, править шаблоны или убирать лишние символы.
Нужно указать, где должна начинаться замена, сколько символов удалить и какой новый текст замены должен быть.
Если параметр “количество символов” равен нулю, производится вставка текста.
При этом текст вставляется после предыдущей позиции
Если позиция превышает количество символов в исходном тексте, заменяемый текст добавляется к текущему.
ПРАВСИМВ(A1;3) — берёт последние 3 символа из ячейки A1.
ЗНАЧЕН(…) — пытается преобразовать эти символы в число.
*1.1 — увеличивает число на 10%.
ТЕКСТ(…;”0″) — преобразует результат обратно в текст (без дробной части).
ЗАМЕНИТЬ(…) — подставляет полученное значение вместо последних 3 символов.
ЕСЛИОШИБКА(…;A1) — если последние 3 символа не число (например, “XYZ”), возвращается исходное значение A1.
Понравилась статья? Поддержите ее автора! Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
Эта функция ищет текст внутри другого фрагмента текста регистрозависимым поиском. Это отличает ее от функции ПОИСК, при абсолютно идентичном синтаксисе. Второе отличие – она не поддерживает подстановочные символы, воспринимая их обычным образом.
Когда искомое найдено, отображается его позиция в тексте в виде числа. Если текст содержит более одного вхождения, выводится позиция первого.
Третий опциональный параметр – чтобы найти фрагмент c определенной позиции в тексте.
Если искомое не найдено в тексте, функция возвращает ошибку #ЗНАЧ.
Если регистр не важен, лучше использовать функцию ПОИСК и примеры брать оттуда.
Найти заглавные буквы (кириллицы и латиницы)
Функция чувствительна к регистру символов, что позволяет с ее помощью, например, найти заглавные буквы в Excel. Решение ниже, правда, не самое изящное, есть варианты и попроще (в статье по ссылке).
Понравилась статья? Поддержите ее автора! Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
Эта функция аналогична функции НАЙТИ и так же ищет подстроку в строке. Когда искомое найдено, отображается его позиция в тексте в виде числа.
Отличие от функции НАЙТИ в том, что ПОИСК не принимает в расчет регистр текста. Как искомого, так и того, в котором мы ищем. Также она поддерживает подстановочные операторы.
У обеих функций есть процедура-аналог Найти и Заменить – как у процедуры, у нее есть свои преимущества и недостатки.
СтрокаВКоторойИщем — ячейка, текстовое значение или любое возвращаемое другой функцией выражение.
Стартовая позиция — опциональный параметр, при отсутствии поиск происходит с первого символа
Если текст содержит более одного вхождения, возвращается позиция первого. Третий (опциональный) параметр используется для поиска с определенной позиции в тексте и по умолчанию равен 1.
Если искомое не найдено в тексте, функция возвращает ошибку #ЗНАЧ.
Форматирование
При поиске дат функция ПОИСК, как и все текстовые функции, воспринимает их как числа, поэтому для корректного поиска может понадобиться функция ТЕКСТ.
При этом логические значения ИСТИНА и ЛОЖЬ конвертируются в текст, соответствующий их написанию.
Поиск символа в ячейке
Наиболее простой пример использования функции – осуществление поиска определенного символа в ячейке.
Логика проста – если поиск позиции символа не возвращает ошибку, значит, символ в ячейке присутствует:
=НЕ(ЕОШ(ПОИСК(паттерн;текст)))
Поиск символа в ячейке формулой ПОИСК
Извлечь первое слово
В этом простейшем примере извлекаем первое слово из ячейки с помощью комбинации — функция ЛЕВСИМВ + функция ПОИСК. Поскольку пробел — регистронезависимый символ, для этого случая можно использовать и функцию НАЙТИ.
Определяем номер позиции первого пробела и возвращаем символы до этой позиции
Таблица выше была использована для извлечения имени из строки с именем и фамилией.
ПОИСК возвращает позицию пробела между именем и фамилией.
Длина имени вычисляется как позиция пробела минус 1.
Примеры выше, где буквы перечислены явно в строковом массиве, занимает довольно много места. Буквы при этом идут подряд, что наводит на мысль, что их можно как-то иначе выразить как диапазон.
И действительно, это возможно с помощью комбинации с функциями СТРОКА и ПОИСК:
{=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1))>0}
Отличие этой формулы массива от предыдущих — ее нужно вводить без фигурных скобок, они появятся при вводе формулы сочетанием Ctrl + Shift + Enter (вместо обычного Enter). В формуле выше, где явно прописаны все буквы, фигурные скобки вводятся вручную — это явное указание строкового массива.
Что происходит в этой формуле?
Функция СТРОКА с численным аргументом “65:90” возвращает массив чисел с 65 по 90 включительно. Как раз в этом диапазоне в таблице ASCII находятся все символы латиницы;
Функция СИМВОЛ возвращает для каждого числового значения в этом массиве его символ, таким образом создавая массив латинских символов;
Функция ПОИСК производит поиск каждого из этих символов в строке и возвращает либо число, либо ошибку, таким образом создавая массив чисел и ошибок
Функция СЧЁТ считает числовые значения в полученном массиве. Если результат больше нуля, значит, хотя бы один символ латиницы был найден. Если нет (все поиски вернули ошибку), значит, не был
Аналогичная формула для кириллицы:
{=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(192:223));A1))>0}
Подробнее о поиске и извлечении кириллицы и латиницы в Excel можно почитать тут:
Понравилась статья? Поддержите ее автора! Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
Эта функция преобразует первую букву каждого слова в верхний регистр, а все последующие буквы – в нижний.
Исходный регистр букв при этом не имеет значения и не учитывается.
Разделителем слов считается любой символ, не являющийся буквой, т.е. это может быть не только пробел, но любой знак препинания, цифра или специальный символ.
Синтаксис
=ПРОПНАЧ(Текст)
Форматирование
Как и все текстовые функции, функция превращает числовые значения в строковый формат числа. Дата и дата-время при этом станут натуральными числами в текстовом формате, проценты будут переведены в десятичную дробь, а финансовые значения потеряют как единицу измерения, так и характерное форматирование.
Это нужно учитывать, если при обработке больших массивов данных в ячейках помимо текстовых значений могут быть числовые.
Строковый формат на выходе легко распознается – результат применения функции в ячейке имеет выравнивание по левому краю, в отличие от чисел, которые имеют выравнивание по правому.
Пример применения. Делаем первое слово в ячейке с заглавной буквы
Как сделать первое слово с заглавной буквы в Excel? Не все слова, а только первое? В этом нам и поможет функция ПРОПНАЧ, но в комбинации с другими.
Алгоритмически задача решается в несколько простых шагов:
Извлекаем из исходной строки первое слово
Применяем к нему функцию ПРОПНАЧ
Извлекаем из исходной строки оставшуюся от первого слова часть (иными словами, нужно удалить первое слово из ячейки и посмотреть на результат).
Но, к сожалению, эта формула еще не идеальна и не будет работать, если в ячейке всего одно слово (выдаст ошибку поиск пробела). Поэтому нам придется и ее обернуть функцией ЕСЛИОШИБКА и применить обычную функцию ПРОПНАЧ в случае такой ошибки:
Идеальна ли эта формула для всех случаев? К сожалению, нет, т.к. ей присуща нехорошая особенность функции ПРОПНАЧ. Она изменяет регистр невзирая на исходный. Например, если первое слово является аббревиатурой и все буквы в нем заглавные, функция изменит в аббревиатуре регистр всех букв, кроме первой, на нижний.
Есть ли решение этой проблемы? Да, его предлагает моя надстройка.
Подробнее о том, как изменить регистр символов и получить желаемый результат: изменить регистр в Excel.
Исправляем регистр предложений, начиная их с заглавной буквы.
Понравилась статья? Поддержите ее автора! Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
Функция ПОДСТАВИТЬ позволяет заменить все вхождения или N-ное вхождение подстроки в тексте на другой текст. Подстрокой может быть как один символ, так и несколько.
Функция является в некоторой степени аналогом процедуры Найти и Заменить в Excel, с некоторыми отличиями:
Что очевидно и является базовым отличием, она является функцией, т.е. пересчитывается автоматически, в отличие от одноразового характера процедур, и выводит результат в другую ячейку, в то время, как процедура меняет результат на месте;
Функция ПОДСТАВИТЬ всегда чувствительна к регистру. В процедуре “найти и заменить” чувствительность к регистру опциональна;
Зато позволяет осуществлять массовую замену нескольких (до 64 в последней версии Excel) значений одновременно;
Еще одно преимущество функции в том, что она позволяет заменить не все вхождения, а определенное по порядку в строке, чего не позволяет сделать процедура.
Последний аргумент является необязательным (поэтому указан в квадратных скобках в примере), если его не указывать, будут заменены все вхождения.
Форматирование
Характерные особенности функции ПОДСТАВИТЬ:
Пустая ячейка воспринимается как строка нулевой длины (“”).
Логические значения конвертируются в текст с сохранением регистра.
Все вариации числовых форматов (дата, время, дата-время, проценты, финансовый формат) конвертируются в соответствующее им натуральное число в текстовом формате, поэтому для сохранения исходного форматирования может понадобиться функция ТЕКСТ.
Примеры формул с функцией ПОДСТАВИТЬ
Функция ПОДСТАВИТЬ необычайно популярна и может использоваться:
по прямому назначению, когда необходимым результатом является сама строка с заменнными фрагментами
как вспомогательная функция в сложносоставных формулах, когда результатом вычислений является даже не строка, а число или логическое значение
Рассмотрю несколько примеров ситуаций, в которых лично использовал функцию.
ПОДСТАВИТЬ с несколькими условиями (транслитерация)
Заменить кириллицу на транслит автоматически с помощью формулы? Можно! Правда, длина формулы настолько велика, что ее лучше сохранить где-нибудь, а не составлять самостоятельно. Потому что это формула с 64 условиями (уровнями вложенности). Подробнее – по ссылке в начале этого абзаца.
ПОДСТАВИТЬ, чтобы посчитать слова
Тот случай, когда функция используется не по своему назначению, т.к. целевым значением является число, а не текст. Лайфхак стар как мир, но не теряет своей актуальности.
Сколько слов в ячейке? На 1 больше, чем пробелов между ними. Это будет справедливо всегда, если разделителем слов считается только пробел, и если пробелов между каждыми двумя словами не более 1. Чтобы обезопасить себя от лишних, можно воспользоваться функцией СЖПРОБЕЛЫ.
Как посчитать пробелы в строке? Как и любой символ, можно посчитать их, подсчитав длину строки с ними и без них, и вычислив разницу. Как получить строку без них? Конечно, с помощью функции ПОДСТАВИТЬ.
Так будет выглядеть формула для ячейки A1, учитывающая все эти нюансы. ДЛСТР измеряет длину строк:
У формулы есть побочный эффект – для пустой ячейки она возвращает 1, поэтому, если пустые ячейки могут присутствовать, формулу выше придется “обернуть” выражением с Excel функцией ЕСЛИ:
Часто возникает задача заменить пробелы в строке на переносы строк, и наоборот. Здесь будет полезна комбинация функций ПОДСТАВИТЬ и СИМВОЛ. Так будет выглядеть формула, заменяющая все пробелы в строке на переносы строк.
=ПОДСТАВИТЬ(A1;" ";СИМВОЛ(10))
Как видно из формулы, код символа переноса строки – 10.
Обратите внимание, что результат может отображаться сплошной строкой, если не включить опцию “Перенести текст”.
Отображение переносов строк в ячейках после замены функцией ПОДСТАВИТЬ
ПОДСТАВИТЬ, чтобы посчитать встречаемость в тексте
Узнать, сколько раз встречается фрагмент в тексте, можно довольно просто с помощью комбинации функций ПОДСТАВИТЬ и ДЛСТР.
Первая (ПОДСТАВИТЬ) позволяет создать строку без указанного фрагмента, заменяя его на пустоту.
Вторая (ДЛСТР) – измерить длину результата.
Если вычесть из исходной длины полученную, и разделить на длину этого фрагмента, можно получить его встречаемость.
Если заведомо известно, что это один символ, процесс деления можно опустить, т.к. результат от деления на 1 не изменится.
Так выглядит формула, считающая в ячейке A1 количество вхождений 1-символьного фрагмента из ячейки B1:
=ДЛСТР(A1) -ДЛСТР(ПОДСТАВИТЬ(A1;B1;""))
Так – для подсчета встречаемости конкретного символа в ячейке, в данном случае – косой черты, что позволяет, например, посчитать уровень вложенности директории URL по ее адресу:
=ДЛСТР(A1) -ДЛСТР(ПОДСТАВИТЬ(A1;"/";""))
А так – для фрагмента длиной более 1 символа, например, если нужно посчитать, сколько раз встречается конкретное слово в тексте:
Представим, что наша задача – провалидировать список номеров телефонов на количество цифр в нем. Корректным будем считать номер, в котором 11 цифр.
При этом номер может быть указан в любом формате – со скобками, дефисами, плюсом в начале, с пробелами и без. Помимо самого номера в строке могут присутствовать другие пометки, например, контактное лицо, является телефон мобильным или нет.
Очевидно, что функция ДЛСТР не подойдет в таком случае, как и не удастся сходу удалить все лишние символы, чтобы оставить в ячейках только цифры.
Комбинация функций ПОДСТАВИТЬ и ДЛСТР в формуле массива с функцией СУММ позволит решить данную задачу:
Понравилась статья? Поддержите ее автора! Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
