Функция сжпробелы в excel

Функция сжпробелы в excel

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

СЖПРОБЕЛЫ ( текст )

Текст — текстовая строка, из которого удаляются пробелы.

Пример

Формула =СЖПРОБЕЛЫ(" Доход за первый квартал ") вернет " Доход за первый квартал ", т.е. удалены все пробелы за исключением одиночных пробелов между словами. Также удалены пробелы перед первым и после последнего слова.

Символ неразрывного пробела

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

Кроме того, при вводе в программе MS WORD чисел с разделителями разряда, MS WORD автоматически заменяет пробел, который отделяет разряды, на символ неразрывного пробела. После копирования таких чисел в EXCEL через Буфер обмена они вставляются как текст. Как бороться с неразрывным пробелом в этом случае — читайте в этой статье .

Функция СЖПРОБЕЛЫ (TRIM) в Excel используется для удаления лишних пробелов в тексте.

Что возвращает функция

Текст без лишних пробелов.

Синтаксис

=TRIM(text) – английская версия

=СЖПРОБЕЛЫ(текст) – русская версия

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

  • text (текст) – текст из которого нужно удалить лишние пробелы. Он может быть введен вручную, быть ссылкой на ячейку с текстом. Например, если нам нужно удалить лишние пробелы из текста в ячейке “А1”, то нам поможет формула: =TRIM(A1) или =СЖПРОБЕЛЫ(A1) .

Примеры использования функции СЖПРОБЕЛЫ в Excel

Пример 1. Убираем лишние пробелы перед словами, после слов, двойные пробелы

На примере ниже текст наполненный массой лишних пробелов.

Читайте также:  Изменение формата даты в excel

С помощью функции СЖПРОБЕЛЫ мы легко уберем все лишнее:

=TRIM(A1) – английская версия

=СЖПРОБЕЛЫ(A1) – русская версия

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

Пример 2. Корректно подсчитываем количество слов в тексте с учетом лишних пробелов

Пробелы это хороший индикатор при подсчете слов в тексте.

Например, если нам нужно понять, какое количество слов во фразе “Как посчитать количество слов в тексте?” то нам поможет формула:

=LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))+1 – английская версия

=ДЛСТР(A1)-ДЛСТР(СЦЕПИТЬ(A1;” “;””))+1 – русская версия

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

Если в тексте есть лишние пробелы, то формула будет считать слова не корректно.

Избежать ошибки и убрать лишние пробелы мы можем с помощью функции СЖПРОБЕЛЫ :

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,” “,””))+1 – английская версия

=ДЛСТР(СЖПРОБЕЛЫ(A1))-ДЛСТР(СЦЕПИТЬ(A1;” “;””))+1 – русская версия

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

Лечится эта проблема очень легко — специальной функцией СЖПРОБЕЛЫ (TRIM) . Она убирает все лишние пробелы, но оставляет по одному пробелу между словами:

Просто и красиво. Но есть пара нюансов.

Неразрывные пробелы не удаляются

Чайной ложкой дегтя тут представляется только невозможность удалять таким способом неразрывные пробелы. Это особый вариант пробела (внешне неотличимый от обычного), на котором не бывает переноса строки. Обычно его используют в случаях типа "г. Москва" или "Иванов И.И.", чтобы эти фразы не разбивались между строчками. В Microsoft Word такой спецсимвол вводится сочетанием клавиш Ctrl+Shift+Пробел и отображается в виде кружка, а не точки:

Читайте также:  Настройка нового компьютера windows 10

К сожалению, функция СЖПРОБЕЛЫ (TRIM) удаляет только обычные пробелы. Для удаления неразрывных нужно использовать другие функции или макросы.

Формулы для удаления начальных и концевых пробелов

Если необходимо удалить только начальные пробелы (т.е. не трогать концевые и пробелы между словами), то придется делать это хитрыми формулами, т.к. готовой функции (по аналогии с функцией LTrim в VBA) в Microsoft Excel, к сожалению, нет.

Формула для удаления лишних пробелов только слева будет выглядеть так:

В английской версии =MID(A1;FIND(LEFT(TRIM(A1));A1);LEN(A1))

Формула для удаления лишних пробелов только справа будет чуть посложнее и должна вводиться уже как формула массива (с Ctrl+Shift+Enter):

В английском варианте это будет =LEFT(A1;MAX((MID(A1&REPT(" ";99);ROW(A1:A99),1);" ")*ROW(A1:A99)))

Ссылки по теме

Для удаления пробелов в начале и в конце строки можно использовать условный оператор ЕСЛИ.
До этого желательно несколько раз с помощью поиска и замены убрать двойные пробелы.

Пробел в начале строки: =ЕСЛИ(ЛЕВСИМВ(А1;1)=" ";ПРАВСИМВ(А1;ДЛСТР(А1)-1))
Пробел в конце строки: =ЕСЛИ(ПРАВСИМВ(А1;1)=" ";ЛЕВСИМВ(А1;ДЛСТР(А1)-1))

А еще так можно .
Для удаления пробелов в начале и в конце строки
можно использовать функцию =СЦЕПИТЬ(). Допустим, ячейка A1 проблемная ,у неё пробел вначале и в конце, нам это не надо.
Вставляем новый столбец и обрабатываем формулой =СЖПРОБЕЛЫ(A1), далее Выделить — Вст а вить зн а чения, теперь повторяющихся пробелов не будет, но в начале и в конце они остались.
Вставим еще один столбец, а в столбец формулу =СЦЕПИТЬ("&&";A1;"&&") , тут главное чтобы && не встречался в тексте. Опять Выделить — Вст а вить зн а чения . Теперь у нас в начале и конце строки не пробел, а дважды амперсанд+ пробел.
Далее Ctrl+F ищем &&[пробел] — заменить ничем, опять ищем [пробел]&& — заменить ничем, там где были пробелы они будут удалены вместе с амперсандами, там где пробелов не было остались наши &&
Проводим последний Поиск -замену Ctrl+F На й ти && Заменит ь ничем.

Читайте также:  Землетрясение в иркутске в 2008 году

Не удобно искать лишние пробелы в чистом виде, а в сочетании с оригинальными символами это просто, мы их как бы помечаем,так можно выделять ненужные символы не только в одной ячейке, но и в массиве данных и разных столбцах. Иногда редактируемые данные не в одном столбце , а в разных. Вместо сложных вычислений первой/последней непустой ячейки, данные сначала объединяем в одну ячейку, с использованием хорошего разделителя, обрабатываем, а потом снова разделяем через "Текст по столбцам".

Ссылка на основную публикацию
Фиксированная шапка сайта при прокрутке
Допустим у вас важная информация например контакты находятся в шапке и вы хотите что бы они всегда были на веду...
Удаление последнего элемента списка
Введение. Основные операции О дносвязный список – структура данных, в которой каждый элемент (узел) хранит информацию, а также ссылку на...
Удаление дубликатов фотографий на русском бесплатно
Здравствуйте Уважаемый Друг. У каждого из нас на компьютере хранится большое количество различных фотографий изображений и тому подобных картинок. Парой...
Фиксированное меню при скролле
Создаём эффект залипания при прокручивании страницы на блоках меню навигации, бокового виджета и меню с помощью jQuery и без него....
Adblock detector