Excel Функции Excel

Как посчитать количество символов в ячейке Excel - функция ДЛСТР с примерами использования

Рассмотрим, как посчитать количество символов в ячейке с помощью функции ДЛСТР и объединять ее с другими функциями для подсчета в столбце, диапазоне, определенных символов, без определенных символов, строчных символов или символов после определенного знака.

Олег Толочко, 15 Мая 2021

411 просмотрели, 0 комментировали

Функция ДЛСТР

В Excel для определения количества символов существует встроенная функция ДЛСТР.

Синтаксис функции

=ДЛСТР(текст)

Аргументы функции

текст - текст, длину которого необходимо вычислить. Обязательный аргумент. В результате подсчета учитываются все символы, в том числе и пробелы.

Подсчет общего количества символов в ячейке

С помощью приведенной выше функции провести вычисление количества символов в одной ячейке не составит особого труда. Введите в ячейку следующую формулу:

=ДЛСТР(A1)

Пример работы функции ДЛСТР с одной ячейкой

У себя, по необходимости, замените ячейку A1 на любую другую. Excel подсчитает все символы в ячейке и вернет результат в виде целого числа. Важно учитывать, что в результате выполнения функции считаются именно ВСЕ символы, в том числе пробелы, знаки препинания и переносы строк.

Подсчет в столбце или диапазоне

Кроме подсчета символов в одной ячейке иногда нужно сделать это в диапазоне или целом столбце. Реализовать это можно несколькими способами.

Наиболее простой, но и наименее гибкий способ - это сложение нескольких функций ДЛСТР непосредственно в формуле. Для этого введите в строку формул «=» и сложите с помощью знака «+» необходимое количество функций ДЛСТР с выбранными для сложения ячейками.

=ДЛСТР(A1)+ДЛСТР(A2)+ДЛСТР(A3)+ДЛСТР(A4)

Однако при большом количестве ячеек формула получится слишком растянутой, поэтому практичнее использовать функцию СУММПРОИЗВ.

=СУММПРОИЗВ(ДЛСТР(A1:A7))

Кратко разберем, зачем тут функция СУММПРОИЗВ. Если вести просто ДЛСТР(A1:A7), Excel вернет значение 7, потому что сама функция получит не массив символов в ячейке, а массив самих ячеек. То есть, когда вводим одну ячейку - получаем ответ "в ячейке X символов", а вводим несколько ячеек - получаем ответ в виде массива "тут N ячеек, в 1 - X символов, во 2 - Y символов, в 3 - Z символов" и так далее.

Функция СУММПРОИЗВ в качестве аргументов как раз принимает массивы, потом перемножает значения внутри каждого из них и возвращает сумму результатов. Поскольку в наших массивах всего 1 значение - сколько символов в ячейке, функция просто суммирует все эти значения, поскольку перемножать нечего.

Однако этот способ также не лишён недостатков, поскольку позволяет суммировать показатели только в целом диапазоне, без разрывов. Для суммирования нескольких диапазонов можно использовать его совместно с первым способом, складывая несколько диапазонов, например:

=СУММПРОИЗВ(ДЛСТР(A1:A7))+СУММПРОИЗВ(ДЛСТР(A12:A15))+ДЛСТР(B2)

Или с функцией СУММ:

=СУММ(СУММПРОИЗВ(ДЛСТР(A1:A7));СУММПРОИЗВ(ДЛСТР(A12:A15));ДЛСТР(B2))

Подсчет без определенных символов

Мы хотим посчитать, сколько символов в ячейке, не учитывая пробелы и знаки препинания. Для этого можно использовать конструкцию с функциями ДЛСТР и ПОДСТАВИТЬ, заменив необходимые знаки на пустые значения. Например, если не нужно учитывать «!» - используйте такую формулу:

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

В этой формуле сначала функция ПОДСТАВИТЬ заменяет все «!» на «» (пустые значения), а затем функция ДЛСТР считает символы в получившейся строке.

Это простой вариант с заменой одного знака. Если же нужно заменить несколько знаков, используйте вложенную функцию ПОДСТАВИТЬ. Например, в файле Excel для управления кампаниями Яндекс Директ для расчёта длины заголовков и текста без учета знаков препинания используется такая формула:

=ДЛСТР(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;"!";"");",";"");".";"");";";"");":";"");"""";""))

Как мы видим, в ней заменяются символы «!», «,», «.», «;», «:», «».

Подсчет определенных символов

Чтобы посчитать количество определенных символов в ячейке достаточно посчитать общее количество символов и вычесть из него количество без определенных символов с помощью формулы из предыдущего пункта. Таким образом, в результате мы получим искомое значение. Например, посчитать, сколько цифр «5» содержится в ячейке можно такой формулой:

=ДЛСТР(D21)-ДЛСТР(ПОДСТАВИТЬ(D21;"5";""))

Подсчет без HTML тегов

Недавно наткнулся на интересный вопрос на Яндекс Кью, решил выложить решение и сюда. Суть вопроса: нужно посчитать количество символов без тегов HTML. Решил не мудрить со стандартными функциями и написать собственную.

Public Function DeleteHTMLTags(tocell As Range) As Integer
    On Error GoTo IfError

    celltext = tocell.Value

    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "(<([^>]+)>)"
    regex.Global = True
    regex.IgnoreCase = True
    regex.MultiLine = True

    newtext = regex.Replace(celltext, "")
    lentext = Len(newtext)
    DeleteHTMLTags = lentext
    Exit Function

    IfError:
        DeleteHTMLTags = "Ошибка"
End Function

Чтобы создать пользовательскую функцию, откройте окно редактора VBA. Для этого на вкладе "Разработчик" нажмите иконку "Visual Basic", либо сочетание клавиш Alt+F11.