5 примеров с функцией длстр в excel

2 способа удалить пробелы между словами или числами в ячейках Excel

Из этой статьи Вы узнаете 2 быстрых способа удалить лишние пробелы между словами или все пробелы из ячеек Excel. Вы можете использовать функцию TRIM (СЖПРОБЕЛЫ) или инструмент Find & Replace (Найти и заменить), чтобы вычистить содержимое ячеек в Excel.

Когда Вы вставляете данные из внешнего источника на лист Excel (простой текст, числа и т.д.), то можете получить лишние пробелы вместе с важными данными. Это могут быть начальные и конечные пробелы, несколько пробелов между словами или разделители разрядов в числах.

Следовательно, таблица выглядит малость неопрятно и становится трудной для использования. Казалось бы, простая задача может стать непростой. Например, найти покупателя с именем John Doe (нет лишних пробелов между частями имени), в то время как в таблице он сохранён как «John Doe«. Или числа, которые не могут быть просуммированы, а виноваты в этом опять лишние пробелы.

Из этой статьи Вы узнаете, как очистить данные от лишних пробелов:

Удаляем все лишние пробелы между словами, отсекаем начальные и конечные пробелы

Предположим, есть таблица с двумя столбцами. В столбце Name в первой ячейке содержится имя John Doe, записанное корректно, т.е. без лишних пробелов. Все остальные ячейки содержат вариант записи с лишними пробелами между именем и фамилией, а также в начале и в конце (начальные и конечные пробелы). Во втором столбце, с названием Length, показано количество символов в каждом имени.

Используем функцию «СЖПРОБЕЛЫ» для удаления лишних пробелов

В Excel существует функция TRIM (СЖПРОБЕЛЫ), которая используется для удаления лишних пробелов из текста. Далее Вы найдёте пошаговую инструкцию по работе с этим инструментом:

1. Рядом со своими данными добавьте вспомогательный столбец. Можете назвать его Trim.
2. В первой ячейке вспомогательного столбца (C2) введите формулу для удаления лишних пробелов:

Готово! Мы удалили все лишние пробелы при помощи функции TRIM (СЖПРОБЕЛЫ). К сожалению, этот способ занимает много времени, особенно, когда таблица достаточно велика.

Замечание: Если после применения формулы Вы по-прежнему видите лишние пробелы, скорее всего в тексте содержатся неразрывные пробелы. Как их удалить, Вы можете узнать из этого примера.

Применяем инструмент «Найти и заменить», чтобы удалить лишние пробелы между словами

Этот вариант требует меньше действий, но позволяет удалить только лишние пробелы между словами. Начальные и конечные пробелы будут также отсечены до 1, но не удалены полностью.

1. Выделите один или несколько столбцов с данными, в которых нужно удалить лишние пробелы между словами.
2. Нажмите Ctrl+H, чтобы открылось диалоговое окно Find & Replace (Найти и заменить).
3. Введите два раза пробел в поле Find What (Найти) и один раз в поле Replace With (Заменить на).
4. Нажмите кнопку Replace all (Заменить все), а затем ОК, чтобы закрыть появившееся информационное окно.
5. Повторяйте шаг 4 до тех пор, пока не появится сообщение We couldn’t find anything to replace… (Мы не нашли ничего, что нужно было заменить…).

Удаляем все пробелы между числами

Предположим, у Вас есть таблица с числами, в которых группы разрядов (тысячи, миллионы, миллиарды) разделены пробелами. В этом случае Excel рассматривает числа как текст и никакая математическая операция выполнена быть не может.

Простейший способ избавиться от лишних пробелов – это воспользоваться стандартным инструментом Excel – Find & Replace (Найти и заменить).

• Нажмите Ctrl+Space (Пробел), чтобы выделить все ячейки в столбце.
• Нажмите Ctrl+H, чтобы открыть диалоговое окно Find & Replace (Найти и заменить).
• В поле Find What (Найти) введите один пробел. Убедитесь, что поле Replace With (Заменить на) – пустое.
• Нажмите кнопку Replace all (Заменить все), затем ОК. Вуаля! Все пробелы удалены.

Удаляем все пробелы при помощи формулы

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

Здесь A1 – это первая ячейка столбца с числами или словами, в которой должны быть удалены все пробелы.

Синтаксис.

Подсчитывает и возвращает длину текста. Есть только один аргумент, который, разумеется, обязателен:

Где текст — это текстовая строка, для которой вы хотите подсчитать количество символов. Нет ничего проще, правда?

Ниже вы найдете несколько простых формул, чтобы получить общее представление о том, как это работает.

= DLSTR (123) — возвращает 3, поскольку в текстовом аргументе переданы 3 цифры .

= DLSTR («яблоко») — возвращает 6, потому что в яблоке 6 букв. Как и любая другая формула Excel, она требует, чтобы текстовые выражения были заключены в кавычки, которые не учитываются при вычислении.

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

Например, чтобы получить длину текста в формате A1, вы должны написать:

= DLSTR (A1)

Ниже приведены более информативные примеры с подробными пояснениями и скриншотами.

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

Счет определенных символов в ячейке

​End If​​ не ВБА, как​​, здравствуйте​​- это не​ ​_Boroda_​​кол-во цифр ?​ вопроса имеют отличие​

​ с числами. ​ ​ вхождения буквы в​​Определим, сколько букв Z​​аррр​ темы в окно​ Format(Cells(i, 4), «’0000000000000»)​

​End Sub​​ до 12-ти​ Excel показать количество​:​Next​ Вы его понимаете​Я прекрасно понимаю,​

​ набор случайных символов.​​:​кол-во математических знаков?​ в решении, тогда​

​Пусть в диапазоне​​ слове или в​

​ в слове Zigzag​: Спасибо!​ поиска и выбирайте,​Next i​Я определил сколько​Мне необходимо в​ определённых символов. В​

​kim​​MsgBox x​​Hugo​​ что этот вопрос​​Это совокупность математических​

​Quote​ (включая и «=»​​ просьба к модераторам​

​А7:A11​​ текстовой строке можно​​ (слово находится в​​Numit​​ что Вам больше​End Sub​ нулей добавить, но​​ столбец Е добавить​​ частности есть таблица​​,​​End Sub​​: VBA​​ не вписывается в​​ действий с конкретными​​(_Boroda_)200?’200px’:»+(this.scrollHeight+5)+’px’);»>Вы хотите выковырять​​ )​ перенести 2-ой вопрос​содержатся числа 1;​​ прочитать здесь.​​ ячейке​​: Помогите пожалуйста,​​ подходит.​​AVI​​ как заставить их​​ эти же числа​​ с размерами. Нужно​​Спасибо,​​_Boroda_​

​Запятую грубо уничтожаем.​​ =ЕСЛИ(. )​​ числами и ячейками.​​ кол-во цифр из​​кол-во цифр: A1=(​​ в отдельную тему​ ​ 22; 123; 8;​​Найдем количество вхождений определенного​A1​как формулой посчитать​Guest​​:​​ добавиться я не​ так, что бы​

​ суммировать сколько например​​Интересное решение со​​: Формулами​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub tt()​​Учитывая, то обстоятельство​ И если топнуть​ формулы, которая выдает​47​_Boroda_​​ 5. Формула, приведенная​ символа в диапазоне​).​​ количество знаков в​: Спасибо нашел. =ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;».»;»»))​_Boroda_​ знаю​

​ общее их количество​​ размеров L и​ ​ знаками​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММ(—(ЕЧИСЛО(-ПСТР(A1;СТРОКА(ДВССЫЛ(«A1:A»&ДЛСТР(A1)));1))+ЕОШИБКА(-ПСТР(A1&» «;СТРОКА(ДВССЫЛ(«A2:A»&ДЛСТР(A1)+1));1))=2))-СУММ(—(ЕЧИСЛО(-ПСТР(A1;СТРОКА(ДВССЫЛ(«A1:A»&ДЛСТР(A1)));1))+(ПСТР(A1&» «;СТРОКА(ДВССЫЛ(«A2:A»&ДЛСТР(A1)+1));1)=»,»)+ЕЧИСЛО(-ПСТР(A1&»»;СТРОКА(ДВССЫЛ(«A3:A»&ДЛСТР(A1)+2));1))=3))​Dim s As​​ , что это​ на Enter то​ в ячейке​+В3-​: Пара вариантов по​ выше, вернет 5,​ ячеек.​Формула для подсчета символа​​ ячейке?​​ — работает как​, Е-мае, точно. Спасибо.​StoTisteg​ было 13, а​ сколько XL. Беда​​Numit​Последний кусок — исключительно​​ String, i As​ вопрос не из​​ в ячейка А1​значение​2​​ первому вопросу во​ т.к. в диапазоне​Определим, сколько букв Z​

​ Z в слове​​Заранее спасибо​​ часы ))​​Марат​​:​ недостающие заменить нулями​ в том, что​: Помогите пожалуйста,​​ для борьбы с​ Long, x As​ серии «МНЕ ОБЯЗАТЕЛЬНО​ будет результат вычисления​(то есть формат​)/D5-​ вложении​ содержится две цифры​ содержится в диапазоне​ Zigzag (с учетом​mazayZR​Юрий М​: Добрый день​Cells(i, 5)=Cstr(Trim(Cells(i, 5)))​ в начале числа.​

​ формулы которые я​​как формулой посчитать​ запятой. Если все​ Long​ НАДО. Я СКОПИРУЮ​ (напр 53,6451)​ ячейки не текстовый)?​

​12​​=ДЛСТР(B1)-ДЛСТР(ПОДСТАВИТЬ(B1;B2;»»))​ 1 (в числах​А7:A11​

​ регистра), см. файл​​: =ДЛСТР(A1)​​: А если всегда​ ​Помогите подсчитать количество​​For j=13 to​То есть 41533892​ нашёл​ количество знаков в​​ числа целые, то​Dim trig As​​ И ВСТАВЛЮ . ​P.S.​kim​=​​<=СУММ(—(ПСТР(B1;СТРОКА(ДВССЫЛ(«A1:A»&ДЛСТР(B1)));1)=B2))>​ 1 и 123)​​.​ примера:​​Numit​ начинать с поиска?​​ символов в ячейке.​ Len(Cells(i, 5).Value) Step​ должно поменяться на​например​​ ячейке?​​ попроще​​ Boolean​​ » а относится​​Оказывается , я​​:​​3​

​По второму — Вы​​ и три цифры​ ​Формула для подсчета (с​

​=ДЛСТР(A1) — ДЛСТР(ПОДСТАВИТЬ(A1;»Z»;»»))​: Спасибо, а то​ :-))​ Пример ячейка A1​ -1​​ 0000041533892, а 31404053​

​=СУММПРОИЗВ (ДЛСТР ($E$2:$E$47)-ДЛСТР​

​s = «За​​ к «ХОЧУ ЗНАТЬ»​​ так же не​​0mega​​кол-во математических знаков?​​ хотите выковырять кол-во​​ 2 (в числах​ учетом РЕгиСТра), см.​

​Формула для подсчета символа​​ прям вся измучалась​​Сергей​​ «1.09» и A2​​Cells(i, 5)=»0″ &​

​ на 0000031404053​​ (ПОДСТАВИТЬ (ПРОПИСН ($E$2:$E$47);<«L»>;»»)))​​mazayZR​​формулы массива​​ 2 дня оштрафовали​​ и еще сюда​ могу выковырять цифры​

Извлекаем в EXCEL число из конца текстовой строки

Пусть текстовая строка Филатова123 находится в ячейке A1 . Чтобы извлечь число 123, расположенное справа, запишем формулу массива : =1*ПСТР(A1; ПОИСКПОЗ(ЛОЖЬ;ЕОШИБКА(1*ПСТР(A1;СТРОКА(ДВССЫЛ(«A1:A»&ДЛСТР(A1)));1));0); 255)

Если число расположено в начале или середине значения, то формула работать не будет (см. файл примера ).

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

О построении этой формулы смотрите ]]> здесь ]]> . Это также формула массива — не забудьте нажать CRTL+SHIFT+ENTER .

Несколько чисел в текстовой строке (через WORD)

Если в текстовой строке расположено несколько чисел, то вышеуказанные формулы работать не будут. В этом случае можно предложить следующий подход:

• скопируйте столбец с текстовыми строками, содержащие числа, в MS WORD;
• нажмите CTRL+H , т.е. вызовите инструмент Найти и Заменить ;
• В поле Найти введите ^$ (любая буква) или его выберите из меню (см. рисунок ниже);

• в поле Заменить на: оставьте пустым (если все числа в строке нужно вывести одним числом, т.е. текстовая строка 123Филато11в6а будет преобразована в 123116 ) или введите пробел (если в дальнейшем потребуется вывести числа в отдельные ячейки);
• нажмите ОК, буквы будут заменены пробелами или просто убраны;
• скопируйте столбец обратно в MS EXCEL.

Если требуется вывести полученные числа в отдельные ячейки, то используйте инструмент Текст-по-столбцам (мастер текстов) или материал статьи Разнесение текстовых строк по столбцам.

Несколько чисел в текстовой строке (через формулы, все числа склеиваются в одно)

Если в текстовой строке расположено несколько чисел, то для извлечения чисел можно вывести каждый символ текстовой строки в отдельную ячейку (см. файл примера, лист Общий случай ) для этого:

• подсчитайте количество букв в текстовой строке (ячейка А3 ) с помощью функции ДЛСТР() в ячейке B3 ;
• создайте табличку, состоящую из количества столбцов = количеству букв в текстовой строке;
• заголовкам столбцов присвойте порядковые номера ;
• в ячейку С3 введите формулу =ПСТР($A3;C$2;1) и протяните ее вправо, заполнив все столбцы.

Заменив формулу =ПСТР($A3;C$2;1) на =ЕСЛИ(ЕОШ(—ПСТР($A3;C$2;1));»»;—ПСТР($A3;C$2;1)) можно вывести только числовые значения. Собрать все числовые значения в одну ячейку можно с помощью формулы =—Т(C7&D7&E7&F7&G7&H7&I7&J7&K7&L7&M7&N7&O7&P7&Q7) или =СЦЕПИТЬ(C7;D7;E7;F7;G7;H7;I7;J7;K7;L7;M7;N7;O7;P7;Q7)+0

Несколько чисел в текстовой строке (через формулы, все числа размещаются в разные ячейки)

Если в текстовой строке расположено несколько чисел и их нужно вывести в разные ячейки, то можно предложить следующий алгоритм (см. файл примера, лист Общий случай ):

• как в предыдущем примере каждый символ текстовой строки выводим в отдельную ячейку;
• с помощью функций СЖПРОБЕЛЫ() и ( СЦЕПИТЬ() или аперсанда &) выводим текстовую строку без букв (т.е. только числа), между числами — 1 пробел;
• с помощью функции ПОИСК() находим начальные позиции каждого числа;
• с помощью функции ПСТР(), ЛЕВСИМВ(), ПРАВСИМВ() выводим числа в отдельные ячейки.

Решение из файла примера позволяет извлекать от 2-х до 4-х чисел из текстовых строк длиной до 15 символов. При желании решение можно легко расширить на большее количество символов и чисел.

Совет: В статье Извлекаем число из начала текстовой строки приведено решение соответствующей задачи. В статье Извлекаем число из середины текстовой строки приведено решение соответствующей задачи.

Подсчет вхождений определенных символов в ячейку Excel

В этом руководстве показано, как применять формулы на основе функций LEN и SUSTITUTE для подсчета количества раз, когда определенный символ появляется в ячейке Excel.

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

Общая формула

=LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref, «character»,»»))

аргументы

Cell_ref: Ячейка содержит текст, внутри которого вы будете считать определенный символ. Вы также можете напрямую заменить cell_ref текстом, заключенным в кавычки;

Характер: Персонаж, которого вы хотите посчитать. Это может быть: 1. Символ, заключенный в кавычки; 2. Или ссылку на ячейку, содержащую символ.

Как пользоваться этой формулой?

1. Выберите пустую ячейку, скопируйте в нее формулу ниже, чтобы получить результат.

=LEN(B3)-LEN(SUBSTITUTE(B3,»e»,»»))

В этом случае он возвращает результат 3, что означает, что символ «e» встречается в B3 три раза.

Tips: Вы можете напрямую ссылаться на символьную ячейку в формуле, как показано ниже. Получив первый результат, перетащите маркер заполнения, чтобы применить формулу к другим ячейкам.=LEN(B3)-LEN(SUBSTITUTE(B3,C3,»»))

Заметки:

• 1. Если конкретный символ не найден, он вернет 0;
• 2. Функция ЗАМЕНА чувствительна к регистру;
• 3. Если вы хотите подсчитать определенный символ в ячейке и проигнорировать регистр, используйте функцию ВЕРХНИЙ внутри ПОДСТАВКИ, как показано ниже:
• =LEN(B3)-LEN(SUBSTITUTE(UPPER(B3),C3,»»))

Как работает эта формула?

• 1. LEN(B3): Эта функция LEN вычисляет общую длину текста B3 и возвращает результат 20.
• 2. LEN(SUBSTITUTE(B3,»e»,»»)): Функция ЗАМЕНА удаляет символ «e» из ячейки B3, а затем функция LEN вычисляет длину текста для ячейки B3 без специального символа «e». Здесь результат 17;
• 3. Наконец, длина текста без символа «e» вычитается из общей длины текста исходной текстовой строки и получается общее количество «e» в ячейке B3 (20-17 = 3).

Связанные функции

Функция ДЛСТР в Excel Функция LEN возвращает количество символов в текстовой строке.

Функция ПОДСТАВИТЬ в Excel Функция SUBSTITUTE заменяет текст или символы в текстовой строке другим текстом или символами.

Родственные формулы

Подсчет определенных символов в диапазоне ячеек В этом руководстве представлены некоторые формулы для подсчета количества раз, когда определенный символ появляется в диапазоне в Excel.

Подсчитайте конкретные слова в ячейке в Excel В этой статье будет представлена ​​формула для подсчета количества раз, когда определенное слово появляется в ячейке Excel.

Подсчитайте конкретные слова в диапазоне в Excel В этой статье объясняется формула для подсчета количества раз, когда определенное слово появляется в диапазоне ячеек в Excel.

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

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

ЗАМЕНИТЬ

Заменяет символы, расположенные в заранее известном месте строки, на требуемое значение. В Excel текстовую функцию ЗАМЕНИТЬ применяют, когда известно где располагается текст, при этом сам он не важен.

Формула в примере ниже заменяет 4 символа, расположенные, начиная с седьмой позиции, на значение “2013”. Применительно к нашему примеру, формула заменит “2010” на “2013”.

Заменяет первые пять символов текстовой строки, т.е. слово “Excel”, на “Word”.

Вот и все! Мы познакомились с 15-ю текстовыми функциями Microsoft Excel и посмотрели их действие на простых примерах. Надеюсь, что данный урок пришелся Вам как раз кстати, и Вы получили от него хотя бы малость полезной информации. Всего доброго и успехов в изучении Excel!

Считаем длину нескольких ячеек.

Самый очевидный способ сделать это — сложить несколько функций, например:

Или используйте функцию СУММ для расчета длины текста, возвращаемой из нескольких ячеек:

В любом случае программа ведет подсчет в каждой из указанных ячеек и возвращает общую длину строк в диапазоне:

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

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

Вот как можно сделать подсчет в диапазоне:

И ваша реальная формула может выглядеть примерно так:

Другой способ сделать расчет в диапазоне — использовать ДЛСТР в сочетании с СУММ:

В отличие от СУММПРОИЗВ, функция СУММ по умолчанию не работает с массивами, и вам нужно обязательно нажимать Ctrl + Shift + Enter после её ввода, чтобы превратить в формулу массива.

Как показано на следующем скриншоте, СУММ возвращает точно такой же результат:

Как это работает?

Это один из самых простых способов подсчета длины в текстовой строке. Мы вычисляем длину для каждой клетки в указанном диапазоне и возвращаем их в виде массива чисел. Затем СУММПРОИЗВ или СУММ складывают эти числа и возвращают общий итог.

В приведенном выше примере суммируется массив из 4 чисел, которые представляют длины строк в ячейках от В2 до В5:

Вы помните, что функция Excel ДЛСТР считает абсолютно все символы в каждой ячейке, включая буквы, числа, знаки препинания, специальные символы и все пробелы (ведущие, конечные и пробелы между словами). Но ведь это не всегда нам нужно. Как обойти эту особенность, поговорим далее.

Синтаксис.

Подсчитывает и возвращает длину текста. Есть только один аргумент, который, разумеется, обязателен:

Где текст — это текстовая строка, для которой вы хотите подсчитать количество символов. Нет ничего проще, правда?

Ниже вы найдете несколько простых формул, чтобы получить общее представление о том, как это работает.

= DLSTR (123) — возвращает 3, поскольку в текстовом аргументе переданы 3 цифры .

= DLSTR («яблоко») — возвращает 6, потому что слово яблоко состоит из 6 букв. Как и любая другая формула Excel, она требует, чтобы текстовые выражения были заключены в кавычки, которые не учитываются при вычислении.

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

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

= DLSTR (A1)

Ниже приведены более информативные примеры с подробными пояснениями и скриншотами.

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