функция ДЛСТР - простейшие примеры
Примеры взаимодействия функции ДЛСТР с разными текстовыми и числовыми значениями в ячейках
Раздел функцийТекстовые
Название на английскомLEN
ВолатильностьНе волатильная
Похожие функцииСЧЁТЗ

Что делает функция ДЛСТР?

Эта функция подсчитывает количество символов, включая пробелы и цифры, в фрагменте текста.

Есть символ, который функция ДЛСТР не учитывает. Это символ апострофа (‘), когда он в начале ячейки. Если в начале ячейки идут два символа апострофа подряд, второй уже учитывается.

Почему так происходит? Дело в том, что апостроф в начале ячейки автоматически воспринимается Excel как служебный символ, переводящий формат ячейки в текстовый.

Синтаксис

=ДЛСТР(Аргумент)

Аргумент у функции один, он обязательный. В нем можно указывать как одну ячейку, так и другие вычисления с помощью формул. При использовании в функциях массива аргументом может быть диапазон ячеек.

Форматирование

Если входящее значение не в текстовом формате, оно конвертируется в текстовый формат.

Ячейки в формате даты воспринимаются как числа, поэтому содержат обычно 5 знаков (если это не даты, близкие к 1900 году).

Процентный формат также конвертируется в натуральное число, например, 2500% = 25 (2 символа, см. таблицу примеров выше).

Примеры применения ДЛСТР

Функция часто используется как вспомогательная в составных формулах, в комбинации с другими текстовыми функциями.

При этом эти комбинации позволяют решать самые необычные задачи.

Пример 1 — посчитать слова

Сколько слов в ячейке? Обычно на 1 больше, чем пробелов между ними. Если пробел один, значит слов — два, если два — то слов три, и так далее.

Если есть вероятность, что пробелов больше 1 между словами, или они есть в начале и конце ячейки, их можно устранить функцией СЖПРОБЕЛЫ.

Пробелы в строке можно посчитать, измерив длину строки в символах:

  • с ними,
  • без них,
  • и вычислив разницу.

Как получить строку без пробелов? Тут поможет функция ПОДСТАВИТЬ.

Так будет выглядеть формула для ячейки A1, учитывающая все эти нюансы. СЖПРОБЕЛЫ удаляет лишние пробелы, ПОДСТАВИТЬ удаляет их, а ДЛСТР измеряет длину строк:

=ДЛСТР(A1) - ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";"")) + 1

Пример 2 — посчитать встречаемость фрагмента в тексте

Есть ли в тексте определенный символ? И если есть, то сколько их?

Рассмотрим пару примеров формул.

Есть ли в ячейке лишние пробелы

Формула ниже проверит, есть ли в ячейке лишние пробелы. Это удобно, когда нужно не просто удалить их, а узнать, были ли они вообще:

=ДЛСТР(СЖПРОБЕЛЫ(A1))<>ДЛСТР(A1)
ДЛСТР для проверки лишних пробелов - пример
Формула проверки наличия лишних пробелов в строке

Формула очевидна и довольно проста. Если длина ячейки после удаления лишних пробелов функцией СЖПРОБЕЛЫ равна исходной — значит, их в ней нет, а если наоборот, не равна — есть.

Обратите внимание, что, несмотря на то, что в самой постановке вопроса звучит условие ЕСЛИ, функция ЕСЛИ тут не нужна. Если равенство верное, Excel сам вставит в ячейку результат ИСТИНА, и ЛОЖЬ, если наоборот.

Сколько раз встречается символ или слово в тексте

По некой аналогии с предыдущим примером, мы можем производить не булевое (ДА-НЕТ, ИСТИНА-ЛОЖЬ) сравнение длин строки перед и после удаления символа или фрагмента, а вычислить разницу.

Если мы ищем один символ, это позволит сразу понять его встречаемость.

Для удаления произвольного символа из строки нам понадобится функция ПОДСТАВИТЬ.

Пример - ДЛСТР для подсчета встречаемости буквы в ячейке
Подсчет встречаемости буквы в ячейке

Если же текстовый фрагмент длиной 2 и более символов — нам потребуется еще и поделить полученный результат на его длину:

=(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;B1;"")))/ДЛСТР(B1)

В данной формуле исходный текст в ячейке A1, а подсчитываемый фрагмент — в ячейке B1.

пример - функция ДЛСТР для подсчета встречаемости текстового фрагмента
Подсчет встречаемости текстового фрагмента

Обратите внимание на 5 и 6 строки — поскольку функция «ПОДСТАВИТЬ» чувствительна к регистру, из текста ею не было удалено первое вхождение. Если нужно учитывать вхождения в любом регистре, могут понадобиться функции СТРОЧН или ПРОПИСН.

Подсчитываемый текстовый фрагмент может быть любой длины.

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

Пример 3 — извлечь или удалить последнее слово

Зная количество пробелов в тексте, можно заменить последний из них на редкий символ, чтобы потом вычислить его позицию.

С этим нам, как и в предыдущем примере, поможет функция ПОДСТАВИТЬ — она позволяет заменить не только все определенные текстовые фрагменты в строке, но и определенное по порядку вхождение (первое, второе и т.д.).

Что это нам дает? Последний пробел — не что иное, как символ, отделяющий последнее слово от остальной части строки. И по его позиции далее уже нехитрыми манипуляциями удалить или извлечь его.

Подробно пример рассмотрен здесь: Удалить последнее слово в Excel

Пример 4 — удалить первые N символов / первое слово

Нехитрая комбинация функций ПРАВСИМВ, ПОИСК и ДЛСТР позволяет удалить из ячейки первое слово.

Механика проста:

  1. вычисляем позицию первого пробела функцией ПОИСК,
  2. вычитаем из длины строки (ДЛСТР) это число
  3. Извлекаем функцией ПРАВСИМВ полученную разницу из исходной строки
ПРАВСИМВ - пример с извлечением второго слова
Удаляем первое слово из ячейки, получая второе

Подробнее пример рассмотрен в статье про функцию ПРАВСИМВ.

Если же количество символов уже известно заранее, формула еще проще — нужны только ДЛСТР и ПРАВСИМВ:

=ПРАВСИМВ(A1;ДЛСТР(A1)-4)
ДЛСТР для удаления первых N символов - пример
Удаляем из строки первые 4 символа функциями ДЛСТР и ПРАВСИМВ

Подробнее можно почитать тут: удалить первые N символов в ячейке.

Пример 5 — замена последнего символа в ячейке

Как бы ни звучало очевидно, позиция последнего символа в строке равна ее длине в символах. А поэтому, зная длину строки, можно заменить последний символ в ней, взяв на 1 символ меньше с помощью ЛЕВСИМВ, и конкатенации с нужным текстом через амперсанд:

=ЛЕВСИМВ(A1;ДЛСТР(A1)-1)&"Текст вместо последнего символа"

Или с помощью функции ЗАМЕНИТЬ, которая на вход возьмет длину строки как позицию заменяемого символа:

=ЗАМЕНИТЬ(A1;ДЛСТР(A1);1;"Текст вместо последнего символа")

Пример 6 — в формуле массива

Как посчитать разом все символы во всех ячейках столбца или диапазона?

Здесь поможет формула массива на основе комбинации функции ДЛСТР и функции СУММ. Первая создаст массив значений длины каждой из ячеек диапазона, а вторая просуммирует эти значения:

={СУММ(ДЛСТР(ДИАПАЗОН))}
Пример - функция ДЛСТР в формуле массива
Функция ДЛСТР в формуле массива

Аналогичным способом функциями МАКС и МИН можно посчитать длины самой длинной и самой короткой строк в диапазоне.

Фигурные скобки вводить не нужно, они появятся сами при вводе формулы массива сочетанием клавиш Ctrl+Shift+Enter (вместо обычного Enter).

Добавить комментарий