Функция СЖПРОБЕЛЫ в Excel. Как использовать

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

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

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

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

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

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

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

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

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

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

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

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

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Найти и заменить

«Найти и заменить» — это самый простой из всех возможных методов убрать пробелы в ячейке Excel. Чтобы все получилось, нужно соблюдать следующий порядок действий:

Выделить ячейку, столбец, строчку или некоторое их количество (где следует убрать «пробелы»).
После этого нужно зажать последовательность клавиш на клавиатуре Ctrl+H. Данное сочетание клавиш вызовет специальное окно инструмента, которое называется «Найти и заменить» или «Find & Replace» (в англоязычной версии).
В графу «Найти» следует ввести двойной пробел, а в нижнюю «Заменить на» только один. Также в строке «Найти» можно установить один пробел, а «Заменить на» оставить пустым, если требуется избавиться от промежутков вообще.
После этого остается нажать «Заменить все».

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

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

Читайте так же:

Windows 10 — управляем параметрами загрузки системы

=СЖПРОБЕЛЫ(» Доход за первый квартал «)

Удаляет начальные и конечные пробелы из текста в формуле («Доход за первый квартал»)

Вчера в марафоне 30 функций Excel за 30 дней мы наткнулись на ленивого шурина по имени AREAS (ОБЛАСТИ). Эта функция не часто используется на практике, но с ее помощью мы увидели, как работают три оператора ссылок в Excel. Надеюсь, что этот урок был для Вас полезен.

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

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

Функция 03: TRIM (СЖПРОБЕЛЫ)

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

Как можно использовать функцию TRIM (СЖПРОБЕЛЫ)?

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

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

Синтаксис TRIM (СЖПРОБЕЛЫ)

Функция TRIM (СЖПРОБЕЛЫ) имеет вот такой синтаксис:

text (текст) – это ссылка на ячейку или текстовая строка, из которой надо удалить пробелы

Ловушки TRIM (СЖПРОБЕЛЫ)

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

Удаление лишних пробелов функцией СЖПРОБЕЛЫ (TRIM) и формулами

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

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

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

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

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

Читайте так же:

Скачивание и установка драйверов для ноутбука Lenovo Z50-70

К сожалению, функция СЖПРОБЕЛЫ (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 На й ти && Заменит ь ничем.

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

Читайте так же:

Как перезагрузить смартфон Xiaomi если он завис

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

За изображения спасибо Depositphotos.com

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРН 310633031600071

Удаление апострофов в начале ячеек

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

По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная → Число → Текстовый), и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим.

Но иногда апостроф оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert → Module) и введите туда его текст.

Sub Apostrophe_Remove() For Each cell In Selection If Not cell.HasFormula Then v = cell.Value cell.Clear cell.Formula = v End If Next End Sub

Теперь если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик → кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.

Как убрать пробелы в числах в Excel?

Проблема с удалением пробелов встречается и при работе с числами — в числах попадаются пробелы (к примеру, вместо разделителей разрядов), из-за чего они воспринимаются в Excel как текст.

Предположим, что у нас имеется таблица с числами, где содержатся пробелы. Выделяем диапазон с данными и с помощью инструмента «Найти и Заменить» удаляем пробелы, подставив вместо одиночного пробела пустое поле, как показано в примере выше.

Аналогичный результат можно получить и с помощью формулы:

При этом не забудьте умножить результат на 1, чтобы перевести формулу из текста в число.

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — делитесь ими в комментариях.

Иногда в различных выгрузках данных, рабочих данных, тексте занесенном сотрудниками появляются лишние пробелы. Возможно в некоторых случаях нам будет все равно есть пробелы или нет, но зачастую лишние пробелы будут мешать сравнению данных и их преобразованию. Так например, если ВПР-ить по фаилии «Иванов» данные из списка фамилий, в которой Иванов записан как " Иванов " нужного результата мы можем и не получить.

Читайте так же:

Смена браузера по умолчанию на Xiaomi

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

Удаление пробелов вручную через замену не во всех случаях может подходить нам, этот вариант актуален в тех случаях, когда мы заведомо знаем, что в тексте есть пробелы идущие подряд по три (" ") по два (« ») и другие комбинации (возможно вперемешку с какими-то символами, которые также нужно убрать). Чтобы воспользоваться заменой и убрать лишние пробелы сперва выделим диапазон ячеек для работы (если это весь лист, то можно не производить выделения). Теперь вызовем диалоговое окно замены сочетанием клавиш CTRL+H (в зависимости от версии Excel вид окна может немного отличаться):

Диалоговое окно замены

Укажем в поле «Найти» два пробела (« »), а в поле «Заменить на» ничего указывать не будем.

Кликнем «Заменить все», теперь все двойные пробелы исчезли (т.е. были заменены на «пусто»).

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

Пример удаления пробелов функцией:

Если наша статься помогла Вам, будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.

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

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

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

Читайте так же:

PDF to Word

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

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

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

В английском варианте это будет =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 На й ти && Заменит ь ничем.

голоса

Рейтинг статьи