Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше
В этой статье описаны синтаксис и использование функций ДЛСТР и ДЛИНБ в Microsoft Excel.
Описание
Функция ДЛСТР возвращает число символов в текстовой строке.
Функция ДЛИНБ возвращает число байтов, представляющих символы в текстовой строке.
Важно:
-
Эти функции могут быть доступны не на всех языках.
-
Функция ДЛИНБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой двухбайтовой кодировки. В противном случае функция ДЛИНБ работает так же, как функция ДЛСТР, и отсчитывает по одному байту на каждый символ.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Синтаксис
ДЛСТР(текст)
ДЛИНБ(текст)
Аргументы этой функции описаны ниже.
-
Текст Обязательный. Текст, длину которого требуется узнать. Пробелы считаются символами.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
|
Данные |
Мурманск |
Формула |
=LEN(B1) |
=LEN(B2) |
=LEN(B3) |
|
Описание |
Длина первой строки |
Длина второй строки |
Длина третьей строки, которая содержит восемь пробелов |
||
|
Один |
Результат |
11 |
0 |
11 |
Нужна дополнительная помощь?
Skip to content
Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии) для вычисления длины текстовых строк в Excel с пробелами или без них.
Из всех функций Excel она, пожалуй, самая простая и понятная. Название функции легко запомнить, это не что иное, как сокращение «длина строки». Она возвращает длину текстовой строки или длину ячейки.
Другими словами, вы используете её для подсчета всех знаков, включая буквы, числа, специальные символы и все пробелы.
В этом коротком руководстве мы сначала кратко рассмотрим синтаксис, а затем более подробно изучим некоторые полезные примеры для определения длины текста в ваших таблицах Excel.
- Базовые сведения
- Считаем длину одной ячейки
- Определяем общую длину диапазона ячеек
- Как игнорировать начальные и конечные пробелы?
- Как считать длину без учета любых пробелов?
- Количество символов до или после определённого знака.
Синтаксис.
Она подсчитывает и возвращает длину текста. Здесь всего один аргумент, который, естественно, обязателен:
= ДЛСТР(текст)
Где текст — это текстовая строка, для которой вы хотите подсчитать число знаков. Нет ничего проще, правда?
Ниже вы найдете пару простых формул, чтобы получить общее представление о том, как это работает.
=ДЛСТР(123) — возвращает 3, потому что в текстовый аргумент передается 3 цифры .
=ДЛСТР(«яблоко») — возвращает 6, потому что слово яблоко состоит из 6 букв. Как и любая другая формула Excel, она требует заключения текстовых выражений в двойные кавычки, которые не учитываются при подсчёте.
Впрочем, на практике вы, скорее всего, будете указывать ссылки на ячейки, а не записывать какой-то текст в саму формулу.
Например, чтобы получить длину текста в A1, вы должны записать:
=ДЛСТР(A1)
Ниже приведены более содержательные примеры с подробными объяснениями и скриншотами.
На первый взгляд функция ДЛСТР выглядит настолько простой, что не требует дополнительных пояснений. Однако есть несколько полезных приемов, которые могут помочь вам настроить её под ваши конкретные нужды.
Определяем длину ячейки.
Как уже упоминалось, здесь учитываются абсолютно все символы, включая все пробелы — ведущие, конечные и между словами.
Например, чтобы получить длину A2, используем это выражение:
=ДЛСТР(A2)
Как показано на скриншоте ниже, получаем результат 32, включая 23 буквы, 2 цифры, 3 пробела и 1 знак пунктуации.
Чтобы сосчитать длину нескольких ячеек, выберите ту что с формулой и скопируйте ее в другие, например, перетащив маркер заполнения.
Как только она будет скопирована, получим длину для каждой ячейки отдельно.
Примечание. При копировании вниз по столбцу обязательно используйте относительную ссылку, например ДЛСТР(A1), или смешанную ссылку, например ДЛСТР($A1), фиксирующую только столбец, чтобы ваше выражение правильно адаптировалась для нового местоположения.
Считаем длину нескольких ячеек.
Самый очевидный способ сделать это — сложить несколько функций, например:
=ДЛСТР(A2)+ДЛСТР(A3)+ДЛСТР(A4)
Или используйте функцию СУММ для расчета длины текста, возвращаемой из нескольких ячеек:
=СУММ(ДЛСТР(A2); ДЛСТР(A3); ДЛСТР(A4); ДЛСТР(A5))
В любом случае программа ведет подсчет в каждой из указанных ячеек и возвращает общую длину строк в диапазоне:
Этот подход, несомненно, прост для понимания и использования, но это не лучший способ подсчета длины текста в диапазоне, состоящего, скажем, из 100 или 1000 ячеек.
Итак, приведенные выше формулы могут хорошо работать для небольшого диапазона. Чтобы подсчитать общую длину текста в большой области, нам лучше придумать что-нибудь более компактное, например, функцию СУММПРОИЗВ, которая умножает массивы и возвращает сумму этих произведений.
Вот как можно сделать подсчет в диапазоне:
= СУММПРОИЗВ(ДЛСТР( диапазон ))
И ваша реальная формула может выглядеть примерно так:
=СУММПРОИЗВ(ДЛСТР(A2:A5))
Другой способ сделать расчет в диапазоне — использовать ДЛСТР в сочетании с СУММ:
{=СУММ(ДЛСТР(A2:A5))}
В отличие от СУММПРОИЗВ, функция СУММ по умолчанию не работает с массивами, и вам нужно обязательно нажимать Ctrl + Shift + Enter после её ввода, чтобы превратить в формулу массива.
Как показано на следующем скриншоте, СУММ возвращает точно такой же результат:
Как это работает?
Это один из самых простых способов подсчета длины в текстовой строке. Мы вычисляем длину для каждой клетки в указанном диапазоне и возвращаем их в виде массива чисел. Затем СУММПРОИЗВ или СУММ складывают эти числа и возвращают общий итог.
В приведенном выше примере суммируется массив из 4 чисел, которые представляют длины строк в ячейках от В2 до В5:
=СУММПРОИЗВ({32;32;32;39})
Вы помните, что функция Excel ДЛСТР считает абсолютно все символы в каждой ячейке, включая буквы, числа, знаки препинания, специальные символы и все пробелы (ведущие, конечные и пробелы между словами). Но ведь это не всегда нам нужно. Как обойти эту особенность, поговорим далее.
Как подсчитать без начальных и конечных пробелов?
При работе с большими таблицами распространенной проблемой являются начальные или конечные пробелы, т.е. лишние пробелы в начале или в конце различных наименований или заголовков. Вы вряд ли заметите их на листе, но, столкнувшись с ними несколько раз, вы научитесь их опасаться.
Если вы подозреваете, что в ваших данных есть несколько невидимых пробелов, мы с этим легко справимся.
Чтобы получить длину текста без начальных и конечных пробелов, просто вставьте функцию СЖПРОБЕЛЫ (TRIM в английской версии) в ДЛСТР:
=ДЛСТР(СЖПРОБЕЛЫ(A2))
Как посчитать без любых пробелов?
Если ваша цель состоит в том, чтобы посчитать только буквы и цифры без каких-либо пробелов в начале, в конце или между ними, вам понадобится немного более сложное выражение:
=ДЛСТР(ПОДСТАВИТЬ(A2;» «;»»))
Как вы, наверное, знаете, функция ПОДСТАВИТЬ заменяет один текст другим. В приведенной выше формуле вы заменяете пробел (» «) ничем, то есть пустой текстовой строкой («»). И поскольку вы встраиваете ПОДСТАВИТЬ внутрь функции ДЛСТР, подстановка фактически не выполняется в ячейках, она просто указывает вычислить длину строки без пробелов.
Определяем длину текста до или после определённого знака.
Иногда вам может потребоваться узнать длину определенной части текста, а не подсчитывать его общую длину.
Предположим, у вас есть список артикулов различных товаров. И все действительные артикулы содержат ровно 5 знаков в первой группе (до тире). Как нам обнаружить ошибки при вводе? Ага, подсчитав сколько знаков перед первым тире.
Итак, наше выражение выглядит следующим образом:
=ДЛСТР(ЛЕВСИМВ($A2; ПОИСК(«-«; $A2)-1))
А теперь давайте разберем пошагово, чтобы вы могли понять логику работы.
- Вы используете функцию ПОИСК, чтобы вернуть позицию первого дефиса («-») в A2:
ПОИСК(«-«; $A2) - Затем вы применяете функцию ЛЕВСИМВ, чтобы извлечь подстроку определённой длины, начиная с левой стороны. И не забудьте вычесть 1 из результата, потому что вы не хотите включать тире:
ЛЕВСИМВ($A2; ПОИСК(«-«; $A2)-1) - И, наконец, у вас есть функция ДЛСТР, которая возвращает длину этого кусочка текста.
Теперь вы можете пойти дальше и выделить неправильные артикулы, установив простое правило условного форматирования с такой формулой, как =$B2<>5:
Или же вы можете в отдельном столбце указать на неправильные артикулы, вставив приведенную выше формулу в функцию ЕСЛИ :
=ЕСЛИ(ДЛСТР(ЛЕВСИМВ(A2; ПОИСК(«-«;A2)-1))<>5; «Неверно!»; «»)
Как показано на скриншоте ниже, формула идеально определяет недопустимые артикулы на основе длины строки. И вам даже не нужен отдельный столбец:
Аналогичным образом вы можете посчитать, сколько знаков находится после определенной позиции в строке.
Например, в списке имен нужно узнать, сколько букв содержит фамилия. Вот как мы это сделаем:
=ДЛСТР(ПРАВСИМВ(A2;ДЛСТР(A2)-ПОИСК(» «;A2)))
Как это работает:
- Сначала вы определяете позицию пробела (» «) в текстовой строке с помощью функции ПОИСК:
ПОИСК(» «;A2) - Затем подсчитываете, сколько знаков следует за пробелом. Для этого вы вычитаете позицию пробела из общей длины текста:
ДЛСТР(A2)-ПОИСК(» «;A2) - После этого применяем ПРАВСИМВ, чтобы вернуть всё после пробела, то есть фамилию.
- И, наконец, вы используете формулу ДЛСТР, чтобы получить длину фамилии, возвращенной ПРАВСИМВ.
Обратите внимание, что для правильной работы каждая ячейка должна содержать только один пробел, то есть только имя и фамилию, без отчества, заголовков или суффиксов.
Вот как можно использовать ДЛСТР в Excel. В следующей статье мы рассмотрим другие её возможности, и вы узнаете еще несколько полезных формул для подсчета символов в Excel.
[the_ad_group id=»48″]
Как быстро посчитать количество слов в Excel — В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в… 
Как быстро извлечь число из текста в Excel — В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно… 
Как удалить пробелы в ячейках Excel — Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов. В чем самая большая проблема с… 
Функция СЖПРОБЕЛЫ — как пользоваться и примеры — Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии) не работает и как… 
Функция ПРАВСИМВ в Excel — примеры и советы. — В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части… 
Функция ЛЕВСИМВ в Excel. Примеры использования и советы. — В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди… 
Как быстро сосчитать количество символов в ячейке Excel — В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…
Функция
ДЛСТР()
, английский вариант LEN(),
возвращает количество знаков в текстовой строке. Например, записав формулу
=ДЛСТР(«8 марта»)
, получим число 7 — количество знаков в текстовой строке с учетом пробела.
Синтаксис функции
ДЛСТР
(
текст
)
Текст
— текст, длину которого требуется определить. Пробелы также учитываются.
Если вместо текстового значения введено число, то функция возвращает количество цифр.
Контроль длины строк
Функцию
ДЛСТР()
можно использовать в
Условном форматировании
для выделения строк с длиной превышающей пороговое значение. Формула
=ДЛСТР(A2)>10
, введенная в правило
Условного форматирования
позволяет выделить все ячейки, содержащие тектовые строки длиной более 10 символов.
Чтобы ограничить ввод в ячейки строк с длиной не более 10 символов можно использовать
Проверку данных
. В этом случае использование
ДЛСТР()
не требуется, т.к.
Проверка данных
уже содержит преднастроенное правило.
Изменение строк
Функция
ДЛСТР()
часто используется при изменении строк. Например, когда требуется вывести часть строки.
Пусть в ячейке
А1
содержится имя файла «Расчет.xlsx», требуется извлечь его расширение. Формула
=ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК(«.»;A1))
решает эту задачу.
Функция ДЛСТР() в MS EXCEL
Смотрите также где N – дробное число из ТЕКСТ преобразует полученное помощью функции ДЛСТР) на подстроки с подстроки (первый символ из строки на 5.
Синтаксис функции
окна показан на текстовой строки в
Рисунок 1 – ПримерЧаще всего данная функция отобразить результаты формул, вставьте их в
Важно: длиной не болееФункция ДЛСТР(), английский вариант число байтов в
Контроль длины строк
диапазона допустимых значений, значение в текстовую указано число 100, наименованием и маркой в исходной строке); основе количества заданныхРисунок 5 – Окончательный
рисунке 3). другой и возвращающая расчетов. используется в связке
выделите их и ячейку A1 нового 10 символов можно LEN(), возвращает количество исходной строке, характеризующий дробная часть будет строку. которое в данном соответственно и записать
Изменение строк
2 – номер последней байт) не отличаются. результат.Рисунок 3 – Вид
начальную позицию найденнойПростой пересчет символов этой с другими функциями, нажмите клавишу F2, листа Excel. Чтобы
Эти функции могут быть
использовать Проверку данных.
excel2.ru
ДЛСТР, ДЛИНБ (функции ДЛСТР, ДЛИНБ)
знаков в текстовой количество байт в усечена;Для соединения (конкатенации) полученных примере гарантированно превышает полученные значения в позиции символа извлекаемой
Описание
Если на компьютереЧитайте также: окна «Создать правило».
строки. фразы (с учетом но бывают и
а затем — клавишу отобразить результаты формул,
-
доступны не на В этом случае строке. Например, записав
-
возвращаемой подстроке.число_знаков – обязательный аргумент, строк используются символы количество знаков в соответствующие столбцы таблицы. подстроки. используется двухбайтовый язык,Формула с ДЛСТРВ окне в блокеПолученные результаты проиллюстрированы на используемых пробелов) позволяет исключения. При работе ВВОД. При необходимости
выделите их и всех языках. использование ДЛСТР() не формулу =ДЛСТР(«8 марта»),Примечания:
Синтаксис
принимающий значение из
«&». В результате
изначальной строке.Вид таблицы данных:
-
Аналогичным способом выделим номера каждый символ при для подсчета количества «Выберите тип правила»
Пример
Пример 1: ДЛСТР
рисунке 2. убедиться в корректности с данной функцией измените ширину столбцов, нажмите клавишу F2,Функция ДЛИНБ отсчитывает по требуется, т.к. Проверка получим число 7Функция ПСТР вернет пустую диапазона неотрицательных чисел, вычислений получим:В результате расчетов получим:Для заполнения столбца «Наименование»
|
месяца и годы |
||
|
использовании ПСТРБ будет |
||
|
слов в ячейке |
||
|
выбираем «Использовать формулу |
Рисунок 2 – Результат |
работы используемой функции. |
|
необходимо задать длину |
чтобы видеть все |
а затем — клавишу |
|
два байта на |
данных уже содержит |
— количество знаков |
|
строку, если в |
которое характеризует длинуФункция имеет следующую синтаксическуюПример 3. В таблице |
используем следующую формулу: |
Пример 2: ДЛИНБ (язык по умолчанию поддерживает двухбайтовую кодировку)
для заполнения соответствующих считаться за два. Excel. для определения форматируемых выведения. текста. Функция ДЛСТР данные. ВВОД. При необходимости каждый символ, только преднастроенное правило. в текстовой строке качестве аргумента начальная_позиция
|
в символах возвращаемой |
||
|
запись: |
||
|
содержатся данные о |
||
|
=ПСТР(A2;1;НАЙТИ(» «;A2)) |
столбцов с учетом, |
Двухбайтовыми языками являются |
|
Функция ДЛСТР активно используется |
ячеек», в следующем |
Пример 3. Среди имеющегося |
|
Пример 2. Имеется строка, |
возвращает количество знаков |
Данные |
|
измените ширину столбцов, |
если языком поФункция ДЛСТР() часто используется с учетом пробела. |
было передано число, |
support.office.com
Примеры функции ДЛСТР для подсчета количества символов в Excel
подстроки. Если в=ПСТР(текст;начальная_позиция;число_знаков) сотрудниках в столбцахФункция НАЙТИ возвращает номер что номер месяца корейский, японский и в формулах Excel поле вводим формулу:
Описание принципа работы функции ФИШЕР в Excel
набора текстовых данных содержащая следующую имя с учетом пробелов.Мурманск чтобы видеть все умолчанию является язык при изменении строк.ДЛСТРтекст превышающее количество символов качестве этого аргументаОписание аргументов: ФИО и дата позиции символа пробела начинается с 4-го китайский.
при комбинации с =ДЛСТР(A2)>12, после чего
в таблице Excel файла с его Важным моментом является Оно данные.
с поддержкой двухбайтовой Например, когда требуется) в исходной строке. передано число 0текст – обязательный для рождения. Создать столбец, « » в символа в каждойПример 1. В столбце другими текстовыми функциями
нажимаем кнопку формат необходимо осуществить выделение
расширением: «Изменение.xlsx». Необходимо тот факт, чтоФормулаДанные кодировки. В противном
вывести часть строки.
Формула с текстовыми функциями ДЛСТР ПРАВСИМВ и ПОИСК
ТекстЕсли в качестве аргумента (нуль), функция ПСТР заполнения аргумент, принимающий в котором будет просматриваемой строке, который строке, а год таблицы содержатся даты, для решения более
и задаем необходимый тех ячеек, количество произвести отделение начальной данная функция можетОписаниеМурманск случае функция ДЛИНБПусть в ячейке — текст, длину которого
начальная_позиция было передано
- вернет пустую строку. ссылку на ячейку отображаться фамилия сотрудника принимается в качестве
- – с 7-го. записанные в виде сложных задач. Например, нам формат выбранных символов в которых части строки с
быть доступна неРезультат
Оно работает так же,
Логическая формула для функции ДЛСТР в условном форматировании
А1 требуется определить. Пробелы значение 1, а Если аргумент задан с текстом или и его возраст аргумента число_знаков функции
Используем следующие формулы: текстовых строк. Записать
| при подсчете количества |
| полей. Ориентировочный вид превышает 12. |
| именем файла (до |
| на всех языках. |
| =ДЛСТР(A2) |
| Формула |
как функция ДЛСТР,содержится имя файла также учитываются. аргумент число_знаков определен числом, превышающим количество текстовую строку, заключенную в формате «Иванов ПСТР. В результате=ПСТР(A2:A10;4;2) отдельно в соседних слов или символов
после заполнения данногоИсходные данные приведены в
точки) без расширенияРассмотрим применение данной функцииДлина первой строкиОписание и отсчитывает по «Расчет.xlsx», требуется извлечьЕсли вместо текстового значения числом, которое равно символов в строке, в кавычки, из – 27». расчетов получим:=ПСТР(A2:A10;7;4) столбцах номер дня,
в ячейке и окна показан выше таблице 1: .xlsx. на конкретных примерах.
7Результат одному байту на
его расширение. Формула введено число, то или больше общему будет возвращена вся которой будет извлеченаВид исходной таблицы:Для заполнения столбца «Марка»Вид заполненной таблицы данных:
месяца и год, т.п.
на рисунке.
Исходная строкаДля решения подобной задачиПример 1. Используя программу=ДЛСТР(A3)
=ДЛСТР(A2) каждый символ.=ПРАВСИМВ(A1;ДЛСТР(A1)-ПОИСК(«.»;A1)) функция возвращает количество числу знаков в часть строки начиная подстрока определенной длиныДля возврата строки с используем следующую формулуТаким образом нам удалось
exceltable.com
Пример функция ПСТР для разделения текста на части в Excel
выделенные из представленныхФункция ПСТР в ExcelПосле этого нажимаем наДобрый день, класс. Я необходимо выполнить следующие Excel, определить длинуДлина второй строкиДлина первой строки
Примеры использования функции ПСТР в Excel
К языкам, поддерживающим БДЦС,решает эту задачу. цифр. исходной строке, функция с указанной вторым начиная с указанной фамилией и текущим массива: разрезать на части дат. предназначена для выделения кнопку «Ок» и ваш новый ученик действия. В Excel фразы «Добрый день,47 относятся японский, китайский
Как разделить текст на несколько ячеек по столбцам в Excel?
В этой статье описаныФункцию ДЛСТР() можно использовать ПСТР вернет всю аргументом позиции. В позиции первого символа; возрастом используем следующую=ПСТР(A2:A8;НАЙТИ(» «;A2:A8)+1;100) текст в ячейкахВид исходной таблицы данных:
подстроки из строки
переходим в окноДобрый день, класс. в произвольной строке класс. Я ваш
=ДЛСТР(A4)
- =ДЛСТР(A3) (упрощенное письмо), китайский синтаксис и использование в Условном форматировании строку целиком.
- дробных числах, используемыхначальная_позиция – обязательный аргумент, формулу:Функция НАЙТИ возвращает позицию
- столбца A. УдалосьДля заполнения номера дня текста, переданной в
«Диспетчер правил условногоДобрый день ввести исходные данные, новый учитель.».Длина третьей строки, включающейДлина второй строки (традиционное письмо) и функций для выделения строкЕсли аргумент начальная_позиция был
в качестве данного
принимающий целые числа
Функция ПСТР возвращает часть
символа пробела. К отдельно каждую дату используем следующую формулу качестве первого аргумента, форматирования» (рисунок 4).Я ваш учитель после чего необходимоДля решения данной задачи в себя 5
4
Как вырезать часть текста ячейки в Excel?
корейский.ДЛСТР с длиной превышающей указан числом из аргумента, дробная часть из диапазона от строки до символа полученному числу прибавляется разделить на несколько (использовать в качестве
и возвращает требуемое
Рисунок 4 – ВидЯ ваш
в любой свободной
открываем Excel, в пробелов=ДЛСТР(A4)ДЛСТР(текст)и пороговое значение. Формула диапазона отрицательных чисел усекается.
1 до N, пробела, позиция которого единица для нахождения
ячеек по столбцам:
формулы массива): количество символов начиная окна «Диспетчер правилРешение данной задачи производится ячейке набрать следующую произвольной ячейке вводим11Длина третьей строки, включающейДЛИНБ(текст)ДЛИНБ =ДЛСТР(A2)>10, введенная в или 0 (нулем),Функция ПСТРБ имеет схожий где N – определяется функцией НАЙТИ. позиции первого символа день, месяц иОписание аргументов:
с заданной позиции.
Как посчитать возраст по дате рождения в Excel?
условного форматирования». путем создания правила формулу с функциями: фразу, длину которойФункция ДЛСТР выполняет возвращение в себя 5Аргументы этой функции описаныв Microsoft Excel. правило Условного форматирования функция ПСТР вернет
синтаксис:
длина строки, из Для нахождения возраста названия марки продукта. год.
A2:A10 – диапазон ячеекОдин символ в языкахВ столбце «Применяется к» условного форматирования. Нагде: необходимо определить, дальше количество знаков в пробелов ниже.Функция ДЛСТР возвращает числопозволяет выделить все ячейки, код ошибки #ЗНАЧ!.=ПСТРБ(текст;начальная_позиция;число_байтов) которой требуется извлечь
сотрудника используется функция Итоговое значение используется с текстовым представлением
Особенности использования функции ПСТР в Excel
с однобайтовой кодировкой задаем необходимый нам
вкладке «Главная» в
ПРАВСИМВ – функция, которая
- выбираем функцию ДЛСТР. текстовой строке. Иными11Текст символов в текстовой содержащие тектовые строкиЕсли аргумент число_знаков заданОна отличается единственным аргументом: подстроку заданного размера. ДОЛЯГОДА, полученное значение
- в качестве аргументаПример 2. В столбце дат, из которых соответствует 1 байту. диапазон ячеек с блоке инструментов «Стили» возвращает заданное число В качестве текста словами, автоматически определяетСкопируйте образец данных из Обязательный. Текст, длину которого строке. длиной более 10 отрицательным числом, результатомчисло_байтов – обязательный аргумент, Начальная позиция символа
- которой усекается до начальная_позиция функции ПСТР. таблицы хранятся текстовые будут выделены номера При работе с исходными данными таблицы выбираем «Условное форматирование», последних знаков текстовой выбираем ячейку с длину строки, автоматически следующей таблицы и требуется узнать. ПробелыФункция ДЛИНБ возвращает число символов. выполнения функции ПСТР принимающий целые числа в строке соответствует ближайшего меньшего целого, Для упрощения, вместо записи с наименованием дней;
такими языками результаты и нажимаем кнопку
в выпадающем меню
строки;
- исходной фразой и подсчитав количество символов, вставьте их в считаются символами. байтов, представляющих символыЧтобы ограничить ввод в будет код ошибки из диапазона от числу 1. Если
чтобы получить число
- поиска номера последней и маркой товаров.1 – номер начальной функций ПСТР и «Ок». Полученный результат указываем на опцию
- ПОИСК – функция, находящая контролируем полученный результат которые содержит исходная ячейку A1 новогоСкопируйте образец данных из в текстовой строке. ячейки строк с #ЗНАЧ!. 1 до N, данный аргумент принимает
- полных лет. Функция позиции (например, с Разделить имеющиеся строки позиции символа извлекаемой ПСТРБ (возвращает подстроку приведен на рисунке
- «Создать правило» (вид первое вхождение одной (см. рисунок 1). строка. листа Excel. Чтобы
exceltable.com
следующей таблицы и
- Что делает функция ДЛСТР?
- Синтаксис
- Форматирование
- Примеры применения ДЛСТР
- Пример 1. Посчитать слова
- Пример 2 – посчитать встречаемость фрагмента в тексте
- Пример 3 – извлечь или удалить последнее слово
- Пример 4 – удалить первые N символов / первое слово
- Пример 5 – последние символы ячейки
- Пример 6 – в формуле массива
- Пример 7 – извлечь цифры
| Раздел функций | Текстовые |
| Название на английском | LEN |
| Волатильность | Не волатильная |
| Похожие функции | СЧЁТЗ |
Что делает функция ДЛСТР?
Функцию часто называют “Длина строки”, что не совсем корректно, потому что речь не о строке, а о строковом выражении значения аргумента.
Эта функция позволяет посчитать количество символов в ячейке, включая пробелы и цифры, в этом самом строковом выражении. Обратите внимание на примеры ниже.
- Хотя визуально 2500% это 5 символов, при преобразовании в текстовую строку это всего лишь 25, поэтому ДЛСТР возвращает 2.
- Даты преобразуются в целые числа, где единицей является 1.01.1900 – вот почему для неё функция возвращает длину 1.
Есть символ, который функция ДЛСТР вообще не учитывает. Это апостроф (‘), когда он в начале ячейки. Если в начале ячейки идут два символа апострофа подряд, второй уже учитывается.
Почему так происходит? Дело в том, что апостроф в Excel — один из специальных символов-операторов, переводящий формат ячейки в текстовый.
Синтаксис
=ДЛСТР(Аргумент)Аргумент у функции один, он обязательный. В нем можно указывать как одну ячейку, так и другие вычисления с помощью формул. При использовании в функциях массива аргументом может быть диапазон ячеек.
Форматирование
Если входящее значение не в текстовом формате, оно конвертируется в него.
Ячейки в формате даты воспринимаются как числа, поэтому содержат обычно 5 знаков (если это не даты, близкие к 1900 году).
Процентный формат также конвертируется в натуральное число, например, 2500% = 25 (2 символа, см. таблицу примеров выше).
Примеры применения ДЛСТР
Функция часто используется как вспомогательная в составных формулах, в комбинации с другими текстовыми функциями.
При этом эти комбинации позволяют решать самые необычные задачи.
Пример 1. Посчитать слова
Как узнать, сколько слов в ячейке Excel? Обычно их на 1 больше, чем пробелов между ними. Если пробел один, значит слов – два, если два – то слов три, и так далее.
Если есть вероятность, что пробелов больше 1 между словами, или они есть в начале и конце ячейки, их можно устранить функцией СЖПРОБЕЛЫ.
Пробелы в строке можно посчитать, измерив длину строки в символах:
- с ними,
- без них,
- и вычислив разницу.
Как получить строку без пробелов? Тут поможет функция ПОДСТАВИТЬ.
Так будет выглядеть формула для ячейки A1, учитывающая все эти нюансы. СЖПРОБЕЛЫ удаляет лишние пробелы, ПОДСТАВИТЬ удаляет их, а ДЛСТР измеряет длину строк:
=ДЛСТР(A1) - ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";"")) + 1Пример 2 – посчитать встречаемость фрагмента в тексте
Есть ли в тексте определенный символ? И если есть, то сколько их?
Рассмотрим пару примеров формул.
Есть ли в ячейке лишние пробелы
Формула ниже проверит, есть ли в ячейке лишние пробелы. Это удобно, когда нужно не просто удалить пробелы, а узнать, были ли они вообще:
=ДЛСТР(СЖПРОБЕЛЫ(A1))<>ДЛСТР(A1)
Формула очевидна и довольно проста. Если длина ячейки после удаления лишних пробелов функцией СЖПРОБЕЛЫ равна исходной – значит, их в ней нет, а если наоборот, не равна – есть.
Обратите внимание, что, несмотря на то, что в самой постановке вопроса звучит условие ЕСЛИ, функция ЕСЛИ тут не нужна. Если равенство верное, Excel сам вставит в ячейку результат ИСТИНА, и ЛОЖЬ, если наоборот.
Сколько раз встречается символ или слово в тексте
По некой аналогии с предыдущим примером, мы можем производить не булевое (ДА-НЕТ, ИСТИНА-ЛОЖЬ) сравнение длин строки перед и после удаления символа или фрагмента, а вычислить разницу.
Если мы ищем один символ, это позволит сразу понять его встречаемость.
Для удаления произвольного символа из строки нам понадобится функция ПОДСТАВИТЬ.
Если же текстовый фрагмент длиной 2 и более символов – нам потребуется еще и поделить полученный результат на его длину:
=(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;B1;"")))/ДЛСТР(B1)В данной формуле исходный текст в ячейке A1, а подсчитываемый фрагмент – в ячейке B1.
Обратите внимание на 5 и 6 строки – поскольку функция “ПОДСТАВИТЬ” чувствительна к регистру, из текста ею не было удалено первое вхождение. Если нужно учитывать вхождения в любом регистре, могут понадобиться функции СТРОЧН или ПРОПИСН.
Подсчитываемый текстовый фрагмент может быть любой длины.
Еще момент – функция может быть непригодна для подсчета коротких слов, т.к. одно слово может быть частью другого.
Пример 3 – извлечь или удалить последнее слово
Зная количество пробелов в тексте, можно заменить последний из них на редкий символ, чтобы потом вычислить его позицию.
С этим нам, как и в предыдущем примере, поможет функция ПОДСТАВИТЬ – она позволяет заменить не только все определенные текстовые фрагменты в строке, но и определенное по порядку вхождение (первое, второе и т.д.).
Что это нам дает? Последний пробел – не что иное, как символ, отделяющий последнее слово от остальной части строки. И по его позиции далее уже нехитрыми манипуляциями удалить или извлечь его.
Подробно пример рассмотрен здесь: Удалить последнее слово в Excel
Пример 4 – удалить первые N символов / первое слово
Нехитрая комбинация функций ПРАВСИМВ, ПОИСК и ДЛСТР позволяет удалить из ячейки первое слово.
Механика проста:
- вычисляем позицию первого пробела функцией ПОИСК,
- вычитаем из длины строки (ДЛСТР) это число
- Извлекаем функцией ПРАВСИМВ полученную разницу из исходной строки
Подробнее пример рассмотрен в статье про функцию ПРАВСИМВ.
Если же количество символов уже известно заранее, формула еще проще – нужны только ДЛСТР и ПРАВСИМВ:
=ПРАВСИМВ(A1;ДЛСТР(A1)-4)
Подробнее можно почитать тут: удалить первые N символов в ячейке.
Пример 5 – последние символы ячейки
Как бы ни звучало очевидно, позиция последнего символа в строке равна ее длине в символах. А поэтому, зная длину строки, можно, например:
- удалить последние символы в ячейке
- извлечь последние символы из ячеек
А ниже примеры, как заменить последний символ в ней, взяв на 1 символ меньше с помощью ЛЕВСИМВ, и конкатенации с нужным текстом через амперсанд:
=ЛЕВСИМВ(A1;ДЛСТР(A1)-1)&"Текст вместо последнего символа"Или с помощью функции ЗАМЕНИТЬ, которая на вход возьмет длину строки как позицию заменяемого символа:
=ЗАМЕНИТЬ(A1;ДЛСТР(A1);1;"Текст вместо последнего символа")Пример 6 – в формуле массива
Как посчитать все символы в диапазоне ячеек или столбце?
Здесь поможет формула массива на основе комбинации функции ДЛСТР и функции СУММ. Первая создаст массив значений длины каждой из ячеек диапазона, а вторая просуммирует эти значения:
={СУММ(ДЛСТР(ДИАПАЗОН))}
Аналогичным способом функциями МАКС и МИН можно посчитать длины самой длинной и самой короткой строк в диапазоне.
Фигурные скобки вводить не нужно, они появятся сами при вводе формулы массива сочетанием клавиш Ctrl+Shift+Enter (вместо обычного Enter).
Пример 7 – извлечь цифры
Подробно процесс рассмотрен в разделе соответствующей статьи – как извлечь цифры из ячеек Excel формулой.
Понравилась статья? Поддержите ее автора!
Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!