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 |
Нужна дополнительная помощь?
Если Вам необходимо определить количество символов в тексте ячейки, то Эксель подготовил для Вас отличную функцию. Функция может быть полезна когда Вам нужно каким то образом обработать текстовые данные, к примеру отсортировать их по длине.
Рассмотрим простой пример.
1. Введите в ячейку А1 какой нибудь текст, к примеру «Текстовая строка».
2. В ячейку B1 внесите формулу =ДЛСТР(A1). Готово, в ячейке B1 Вы получите количество символов в тексте в ячейке A1. В моем примере результат равен 16.
Немного о синтаксисе функции:
=ДЛСТР(текст)
где текст — это либо ссылка на ячейку, содержащую текст, либо сама текстовая строка.
Надеюсь это статья Вам помогла. Есть вопросы — задавайте в комментариях.
Содержание
- Варианты изменения величины элементов листа
- Способ 1: перетаскивание границ
- Способ 2: изменение величины в числовом выражении
- Способ 3: автоматическое изменение размера
- Вопросы и ответы
Довольно часто во время работы с таблицами пользователям требуется изменить размер ячеек. Иногда данные не помещаются в элементы текущего размера и их приходится расширять. Нередко встречается и обратная ситуация, когда в целях экономии рабочего места на листе и обеспечения компактности размещения информации, требуется уменьшить размер ячеек. Определим действия, с помощью которых можно поменять размер ячеек в Экселе.
Читайте также: Как расширить ячейку в Экселе
Варианты изменения величины элементов листа
Сразу нужно отметить, что по естественным причинам изменить величину только одной ячейки не получится. Изменяя высоту одного элемента листа, мы тем самым изменяем высоту всей строки, где он расположен. Изменяя его ширину – мы изменяем ширину того столбца, где он находится. По большому счету в Экселе не так уж и много вариантов изменения размера ячейки. Это можно сделать либо вручную перетащив границы, либо задав конкретный размер в числовом выражении с помощью специальной формы. Давайте узнаем о каждом из этих вариантов более подробно.
Способ 1: перетаскивание границ
Изменение величины ячейки путем перетаскивания границ является наиболее простым и интуитивно понятным вариантом.
- Для того, чтобы увеличить или уменьшить высоту ячейки, наводим курсор на нижнюю границу сектора на вертикальной панели координат той строчки, в которой она находится. Курсор должен трансформироваться в стрелку, направленную в обе стороны. Делаем зажим левой кнопки мыши и тянем курсор вверх (если следует сузить) или вниз (если требуется расширить).
- После того, как высота ячейки достигла приемлемого уровня, отпускаем кнопку мыши.
Изменение ширины элементов листа путем перетягивания границ происходит по такому же принципу.
- Наводим курсор на правую границу сектора столбца на горизонтальной панели координат, где она находится. После преобразования курсора в двунаправленную стрелку производим зажим левой кнопки мыши и тащим его вправо (если границы требуется раздвинуть) или влево (если границы следует сузить).
- По достижении приемлемой величины объекта, у которого мы изменяем размер, отпускаем кнопку мышки.
Если вы хотите изменить размеры нескольких объектов одновременно, то в этом случае требуется сначала выделить соответствующие им сектора на вертикальной или горизонтальной панели координат, в зависимости от того, что требуется изменить в конкретном случае: ширину или высоту.
- Процедура выделения, как для строк, так и для столбцов практически одинакова. Если нужно увеличить расположенные подряд ячейки, то кликаем левой кнопкой мыши по тому сектору на соответствующей панели координат, в котором находится первая из них. После этого точно так же кликаем по последнему сектору, но на этот раз уже одновременно зажав клавишу Shift. Таким образом, будут выделены все строки или столбцы, которые расположены между этими секторами.
Если нужно выделить ячейки, которые не являются смежными между собой, то в этом случае алгоритм действий несколько иной. Кликаем левой кнопкой мыши по одному из секторов столбца или строки, которые следует выделить. Затем, зажав клавишу Ctrl, клацаем по всем остальным элементам, находящимся на определенной панели координат, которые соответствуют объектам, предназначенным для выделения. Все столбцы или строки, где находятся эти ячейки, будут выделены.
- Затем, нам следует для изменения размера нужных ячеек переместить границы. Выбираем соответствующую границу на панели координат и, дождавшись появления двунаправленной стрелки, зажимаем левую кнопку мыши. Затем передвигаем границу на панели координат в соответствии с тем, что именно нужно сделать (расширить (сузить) ширину или высоту элементов листа) точно так, как было описано в варианте с одиночным изменением размера.
- После того, как размер достигнет нужной величины, отпускаем мышку. Как можно увидеть, изменилась величина не только строки или столбца, с границами которых была произведена манипуляция, но и всех ранее выделенных элементов.
Способ 2: изменение величины в числовом выражении
Теперь давайте выясним, как можно изменить размер элементов листа, задав его конкретным числовым выражением в специально предназначенном для этих целей поле.
В Экселе по умолчанию размер элементов листа задается в специальных единицах измерения. Одна такая единица равна одному символу. По умолчанию ширина ячейки равна 8,43. То есть, в видимую часть одного элемента листа, если его не расширять, можно вписать чуть больше 8 символов. Максимальная ширина составляет 255. Большее количество символов в ячейку вписать не получится. Минимальная ширина равна нулю. Элемент с таким размером является скрытым.
Высота строки по умолчанию равна 15 пунктам. Её размер может варьироваться от 0 до 409 пунктов.
- Для того, чтобы изменить высоту элемента листа, выделяем его. Затем, расположившись во вкладке «Главная», клацаем по значку «Формат», который размещен на ленте в группе «Ячейки». Из выпадающего списка выбираем вариант «Высота строки».
- Открывается маленькое окошко с полем «Высота строки». Именно тут мы должны задать нужную величину в пунктах. Выполняем действие и клацаем по кнопке «OK».
- После этого высота строки, в которой находится выделенный элемент листа, будет изменена до указанной величины в пунктах.
Примерно таким же образом можно изменить и ширину столбца.
- Выделяем элемент листа, в котором следует изменить ширину. Пребывая во вкладке «Главная» щелкаем по кнопке «Формат». В открывшемся меню выбираем вариант «Ширина столбца…».
- Открывается практически идентичное окошко тому, которое мы наблюдали в предыдущем случае. Тут так же в поле нужно задать величину в специальных единицах, но только на этот раз она будет указывать ширину столбца. После выполнения данных действий жмем на кнопку «OK».
- После выполнения указанной операции ширина столбца, а значит и нужной нам ячейки, будет изменена.
Существуют и другой вариант изменить размер элементов листа, задав указанную величину в числовом выражении.
- Для этого следует выделить столбец или строку, в которой находится нужная ячейка, в зависимости от того, что вы хотите изменить: ширину и высоту. Выделение производится через панель координат с помощью тех вариантов, которые мы рассматривали в Способе 1. Затем клацаем по выделению правой кнопкой мыши. Активируется контекстное меню, где нужно выбрать пункт «Высота строки…» или «Ширина столбца…».
- Открывается окошко размера, о котором шла речь выше. В него нужно вписать желаемую высоту или ширину ячейки точно так же, как было описано ранее.
Впрочем, некоторых пользователей все-таки не устраивает принятая в Экселе система указания размера элементов листа в пунктах, выраженных в количестве символов. Для этих пользователей существует возможность переключения на другую величину измерения.
- Переходим во вкладку «Файл» и выбираем пункт «Параметры» в левом вертикальном меню.
- Запускается окно параметров. В его левой части расположено меню. Переходим в раздел «Дополнительно». В правой части окна расположены различные настройки. Прокручиваем полосу прокрутки вниз и ищем блок инструментов «Экран». В этом блоке расположено поле «Единицы на линейке». Кликаем по нему и из выпадающего списка выбираем более подходящую единицу измерения. Существуют следующие варианты:
- Сантиметры;
- Миллиметры;
- Дюймы;
- Единицы по умолчанию.
После того, как выбор сделан, для вступления изменений в силу жмем по кнопке «OK» в нижней части окна.
Теперь вы сможете регулировать изменение величины ячеек при помощи тех вариантов, которые указаны выше, оперируя выбранной единицей измерения.
Способ 3: автоматическое изменение размера
Но, согласитесь, что не совсем удобно всегда вручную менять размеры ячеек, подстраивая их под конкретное содержимое. К счастью, в Excel предусмотрена возможность автоматического изменения размеров элементов листа, согласно величине тех данных, которые они содержат.
- Выделяем ячейку или группу, данные в которой не помещаются в элемент листа, содержащего их. Во вкладке «Главная» клацаем по знакомой кнопке «Формат». В раскрывшемся меню выбираем тот вариант, который следует применить к конкретному объекту: «Автоподбор высоты строки» или «Автоподбор ширины столбца».
- После того, как был применен указанный параметр, размеры ячейки изменятся согласно их содержимому, по выбранному направлению.
Урок: Автоподбор высоты строки в Экселе
Как видим, изменить размер ячеек можно несколькими способами. Их можно разделить на две большие группы: перетягивание границ и ввод числового размера в специальное поле. Кроме того, можно установить автоподбор высоты или ширины строк и столбцов.
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. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…
- Что делает функция ДЛСТР?
- Синтаксис
- Форматирование
- Примеры применения ДЛСТР
- Пример 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 поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!