5 примеров с функцией DLSTR в Excel.
Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы точно попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию DLSTR (LEN на английском языке) для расчета длины текстовых строк в Excel с пробелами или без них.
Из всех функций Excel это, пожалуй, самая простая и понятная. Название функции легко запомнить, это просто сокращение от «длины строки». Возвращает длину текстовой строки или длину ячейки.
Другими словами, вы используете его для подсчета всех символов, включая буквы, числа, специальные символы и все пробелы.
В этом коротком руководстве мы сначала кратко рассмотрим синтаксис, а затем более подробно рассмотрим некоторые примеры, которые полезны для определения длины текста в электронных таблицах Excel.
Синтаксис.
Подсчитывает и возвращает длину текста. Есть только один аргумент, который, разумеется, обязателен:
= DLSTR (текст)
Где текст — это текстовая строка, для которой вы хотите подсчитать количество символов. Нет ничего проще, правда?
Ниже вы найдете несколько простых формул, чтобы получить общее представление о том, как это работает.
= DLSTR (123) — возвращает 3, поскольку в текстовом аргументе переданы 3 цифры .
= DLSTR («яблоко») — возвращает 6, потому что в яблоке 6 букв. Как и любая другая формула Excel, она требует, чтобы текстовые выражения были заключены в кавычки, которые не учитываются при вычислении.
Однако на практике вы, скорее всего, будете указывать ссылки на ячейки, а не писать текст в самой формуле.
Например, чтобы получить длину текста в формате A1, вы должны написать:
= DLSTR (A1)
Ниже приведены более информативные примеры с подробными пояснениями и скриншотами.
На первый взгляд функция DLSTR кажется настолько простой, что не требует дополнительных пояснений. Однако есть несколько полезных приемов, которые помогут вам настроить его под свои нужды.
Определяем длину ячейки.
Как уже было сказано, здесь учитываются абсолютно все символы, включая все пробелы: начальные, конечные и между словами.
Например, чтобы получить длину A2, мы используем это выражение:
= ДЛСТР (A2)
Как показано на скриншоте ниже, результат равен 32, включая 23 буквы, 2 цифры, 3 пробела и 1 знак препинания.
Чтобы подсчитать длину нескольких ячеек, выберите ячейку с формулой и скопируйте ее в другие, например, перетащив маркер заполнения.
После копирования мы получаем длину каждой ячейки отдельно.
Примечание. При копировании столбца обязательно используйте относительную ссылку, например DLSTR (A1), или смешанную ссылку, например DLSTR ($ A1), которая захватывает только столбец, чтобы выражение правильно соответствовало новой позиции.
Считаем длину нескольких ячеек.
Самый очевидный способ сделать это — добавить больше функций, например:
= DLSTR (A2) + DLSTR (A3) + DLSTR (A4)
Или используйте функцию СУММ, чтобы вычислить длину текста, возвращаемого из нескольких ячеек:
= СУММ (DLSTR (A2), DLSTR (A3), DLSTR (A4), DLSTR (A5))
В каждом случае программа считает в каждой из указанных ячеек и возвращает общую длину строк в диапазоне:
Этот подход, несомненно, прост для понимания и использования, но это не лучший способ рассчитать длину текста в диапазоне, скажем, 100 или 1000 ячеек.
Таким образом, приведенные выше формулы могут хорошо работать для небольшого диапазона. Чтобы вычислить общую длину текста на большой площади, нам лучше найти что-нибудь более компактное, например функцию СУММПРОИЗВ, которая умножает массивы и возвращает сумму этих произведений.
Вот как сделать подсчет в диапазоне:
= СУММПРОИЗВ (DLSTR (интервал))
И ваша настоящая формула может выглядеть так:
= СУММПРОИЗВ (DLSTR (A2: A5))
Другой способ выполнить вычисление по диапазону — использовать DLSTR в сочетании с SUM:
{= СУММ (DLSTR (A2: A5))}
В отличие от СУММПРОИЗВ, функция СУММ не работает с массивами по умолчанию, и вы должны обязательно нажать Ctrl + Shift + Enter после ввода, чтобы преобразовать его в формулу массива.
Как показано на следующем снимке экрана, СУММ возвращает точно такой же результат:
Как это работает?
Это один из самых простых способов рассчитать длину текстовой строки. Мы вычисляем длину каждой ячейки в указанном диапазоне и возвращаем их в виде массива чисел. Затем СУММПРОИЗВ или СУММ складывает эти числа и возвращает общую сумму.
В приведенном выше примере суммируется массив из 4 чисел, представляющих длины строк в ячейках от B2 до B5:
= СУММПРОИЗВ ({32; 32; 32; 39})
Помните, что функция Excel DLSTR считает абсолютно все символы в каждой ячейке, включая буквы, числа, знаки препинания, специальные символы и все пробелы (ведущие, конечные и пробелы между словами). Но нам это не всегда нужно. О том, как обойти эту особенность, поговорим дальше.
Как подсчитать без начальных и конечных пробелов?
При работе с большими таблицами распространенной проблемой являются начальные или конечные пробелы, то есть лишние пробелы в начале или конце различных заголовков или заголовков. Вы вряд ли заметите их на бумаге, но, встретив их несколько раз, вы научитесь их опасаться.
Если вы подозреваете, что в ваших данных есть несколько невидимых пробелов, мы легко с этим справимся.
Чтобы получить длину текста без начальных и конечных пробелов, просто вставьте функцию TRIM в DLSTR:
= ДЛСТР (ОБРЕЗАТЬ (A2))
Как посчитать без любых пробелов?
Если ваша цель состоит в том, чтобы считать только буквы и цифры без пробелов в начале, конце или середине, вам нужно немного более сложное выражение:
= DLSTR (ПОДСТАВИТЬ (A2;» «;»»))
Как вы, наверное, знаете, функция REPLACE заменяет один текст другим. В приведенной выше формуле вы заменяете пробел («») ничем, то есть пустой текстовой строкой («»). И поскольку вы встраиваете ПОДСТАВКУ в функцию DLSTR, подстановка фактически не выполняется в ячейках, она просто говорит вам вычислить длину строки без пробелов.
Определяем длину текста до или после определённого знака.
Иногда вам может потребоваться узнать длину определенного фрагмента текста, а не подсчитывать его общую длину.
Допустим, у нас есть список артикулов для различных продуктов. И все действующие артикулы содержат ровно 5 символов в первой группе (до тире). Как мы обнаруживаем опечатки? Да, посчитав количество символов до первого тире.
Итак, наше выражение выглядит так:
= DLSTR (ЛЕВО ($ A2; ПОИСК («-«; $ A2) -1))
А теперь пошагово, чтобы вы поняли логику работы.
- Мы используем функцию ПОИСК, чтобы вернуть позицию первого тире («-») в A2:
ПОИСК («-«; $ A2) - Затем вы используете функцию LEFT для извлечения подстроки определенной длины, начиная с левой стороны. И не забудьте вычесть из результата 1, потому что вы не хотите включать тире:
ВЛЕВО ($ A2; ПОИСК («-«; $ A2) -1) - Наконец, у вас есть функция DLSTR, которая возвращает длину этого фрагмента текста.
Теперь вы можете пойти дальше и выделить неправильные SKU, установив простое правило условного форматирования с формулой вида = $ B2 5:
В качестве альтернативы вы можете указать неправильные артикулы в отдельном столбце, введя приведенную выше формулу в функцию ЕСЛИ :
= ЕСЛИ (DLSTR (ЛЕВО (A2; ПОИСК («-«; A2) -1)) 5; «Неверно!»; «»)
Как показано на снимке экрана ниже, формула идеально определяет недопустимые SKU на основе длины строки. И вам даже не нужен отдельный столбец:
Точно так же вы можете подсчитать, сколько символов стоит после определенной позиции в строке.
Например, в списке имен нужно узнать, сколько букв содержит фамилия. Вот как мы это делаем:
= DLSTR (ПРАВО (A2; DLSTR (A2) -ПОИСК («»; A2)))
Как это работает:
- Сначала определите положение пробела («») в текстовой строке с помощью функции ПОИСК:
ПОИСК («»; A2) - Итак, вы посчитаете, сколько символов следует за пробелом. Для этого вычтите положение пробела из общей длины текста:
DLSTR (A2) -ПОИСК («»; A2) - Затем мы используем ВПРАВО, чтобы вернуть все после пробела, то есть фамилии.
- Наконец, вы используете формулу DLSTR, чтобы получить длину фамилии, возвращаемую RIGHT.
Обратите внимание, что для правильной работы каждая ячейка должна содержать только один пробел, то есть только имя и фамилию, без отчества, заголовков или суффиксов.
Вот как можно использовать DLSTR в Excel.
