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

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

Способ 1: использование инструмента «Заменить»

С заменой двойных пробелов между словами на одинарные в Экселе отлично справляется инструмент «Заменить» .

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

Способ 2: удаление пробелов между разрядами

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

Разделения между разрядами будут удалены, а формулы начнут вычисляться корректно.

Способ 3: удаление разделителей между разрядами путем форматирования

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

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

Способ 4: удаление пробелов с помощью функции

Инструмент «Найти и заменить» отлично подходит для удаления лишних пробелов между символами. Но что делать, если их нужно убрать в начале или в конце выражения? В этом случае на помощь придет функция из текстовой группы операторов СЖПРОБЕЛЫ .

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

Синтаксис этого оператора довольно прост и имеет всего один аргумент:

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

В качестве аргумента «Текст» может выступать, как непосредственно текстовое выражение, так и ссылка на ячейку в котором оно содержится. Для нашего случая как раз последний вариант и будет рассматриваться.

1. Выделяем ячейку, расположенную параллельно столбцу или строке, где следует убрать пробелы. Кликаем по кнопке «Вставить функцию» , расположенную слева от строки формул.



2. Запускается окно Мастера функций. В категории «Полный алфавитный перечень» или «Текстовые» ищем элемент «СЖПРОБЕЛЫ» . Выделяем его и жмем на кнопку «OK» .



3. Открывается окошко аргументов функции. К сожалению, данная функция не предусматривает использования в качестве аргумента всего нужного нам диапазона. Поэтому устанавливаем курсор в поле аргумента, а затем выделяем самую первую ячейку диапазона, с которым мы работаем. После того, как адрес ячейки отобразился в поле, жмем на кнопку «OK» .



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



5. Как видим, после этих действий образуется новый заполненный диапазон, в котором находится все содержимое исходной области, но без лишних пробелов. Теперь перед нами стоит задача заменить преобразованными данными значения исходного диапазона. Если мы выполним простое копирование, то скопирована будет формула, а значит, вставка произойдет некорректно. Поэтому нам нужно произвести только копирование значений.

   Выделяем диапазон с преобразованными значениями. Жмем на кнопку «Копировать» , расположенную на ленте во вкладке «Главная» в группе инструментов «Буфер обмена» . Как альтернативный вариант можно после выделения набрать сочетание клавиш Ctrl+C .



6. Выделяем исходный диапазон данных. Кликаем по выделению правой кнопкой мыши. В контекстном меню в блоке «Параметры вставки» выбираем пункт «Значения» . Он изображен в виде квадратной пиктограммы с цифрами внутри.



7. Как видим, после вышеописанных действий значения с лишними пробелами были заменены идентичными данными без них. То есть, поставленная задача выполнена. Теперь можно удалить транзитную область, которая была использована для трансформации. Выделяем диапазон ячеек, в которых содержится формула СЖПРОБЕЛЫ . Кликаем по нему правой кнопкой мыши. В активировавшемся меню выбираем пункт «Очистить содержимое» .



8. После этого лишние данные будут убраны с листа. Если в таблице существуют другие диапазоны, которые содержат лишние пробелы, то с ними нужно поступить по точно такому же алгоритму, какой был описан выше.

Как видим, существует целый ряд способов быстрого удаления лишних пробелов в Экселе. Но все эти варианты реализуются при помощи всего двух инструментов – окна «Найти и заменить» и оператора СЖПРОБЕЛЫ . В отдельном случае можно использовать также форматирование. Не существует универсального способа, который было бы максимально удобно использовать во всех ситуациях. В одном случае будет оптимальным использовать один вариант, а во втором – другой и т.д. Например, с удалением двойного пробела между словами быстрее всего справится инструмент «Найти и заменить» , а вот убрать пробелы в начале и в конце ячейки корректно сможет только функция СЖПРОБЕЛЫ . Поэтому о применении конкретного способа пользователь должен принимать решение самостоятельно с учетом ситуации.

При работе с программой Microsoft Excel различных версий (2003, XP, 2007, 2010 и последующих) пользователи часто сталкиваются с проблемой, когда при вставке формулы для подсчета чисел, она не считает и выводит ошибку. Это связано с различными проблемами, но мы рассмотрим вариант, когда числа в Excel имеют пробел (неразрывный пробел). Именно по этой причине формула не воспринимает числовые значения в финансовом или числовом формате. Отсюда и ошибка, когда вставленная формула выдает ошибку, отображая значение «ноль» (при вставке формулы «Сумма») (см. Рис. 1). При вставке других формул при работе с числами, которые имеют пробелы, могут возникать другие ошибки, например, #ДЕЛ/О. Как убрать пробелы между числами, чтобы формула правильно считала?

Для начала давайте выясним, откуда взялись пробелы в числах? Существует несколько вариантов появления пробелов в числовых значениях.

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

Откуда берутся пробелы в числах?

1. Пробелы могут отображать разделение разрядов

Разделение разрядов - это визуальное отображение пробелов между числами. То есть для того чтобы пользователю визуально увидеть на экране большое число выбирается команда Формат с разделителями (см. Рис. 2). Формат с разделителями позволяет увидеть «тысячи» в числе.

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

Рис. 2. Разделение разрядов в числах Excel

2. Неразрывные пробелы в числах Excel

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

Являются специальными символами, которые чаще используются в программе Word или при публикации текста в блогах. Полезным (существует также ) может быть при обязательном соединении чисел, слов или дат, например, в предложении «ул. Сидорова» необходимо прикрепить «ул.» к слову «Сидорова». Поставить неразрывный пробел можно клавишами CTRL+SHIFT+ПРОБЕЛ.

Числа, которые имеют такие пробелы, могли быть вставлены из программы Word или Интернет.

Как убрать пробел (неразрывный пробел) в числах в Excel 2010?

Самый простой убрать пробел (неразрывный пробел) между числами в Excel - это использовать команду на вкладке Главная Найти/Заменить (см. Рис. 3).

Рис. 3 Как убрать пробелы и пустое пространство между цифрами в Excel?

Для того чтобы убрать пробелы в числах, необходимо:

1. Выделить мышью «пространство» между цифрами, например, между «4″ и «567″ в первом числе (см. Рис. 1).

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

3. Затем нажать клавиши Ctrl+С (команда позволяет скопировать в буфер обмена выделенный фрагмент, та же команда доступна на вкладке Главная/Копировать)

5. Вставить из буфера обмена скопированный неразрывный пробел в строку «Найти». В строке «Заменить на» оставить пустое значение (ничего не писать).

6. Нажать команду Заменить все

По завершении работы нажать Закрыть диалоговое окно.

Подписывайтесь на каналы "сайт" в T amTam или присоединяйтесь в

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

Способ 1: инструмент «Заменить»

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

1. Откройте нужную таблицу в программе.
2. Перейдите во вкладку «Главная».
3. На ленте инструментов отыщите и кликните левой кнопкой мыши по пункту «Найти и выделить».
4. В появившемся меню выберите опцию «Заменить».
5. В появившемся окне введите в графу «Найти» два пробела, просто дважды нажав по соответствующей клавише на клавиатуре.
6. В графу «Заменить на» впишите один пробел.

После этого появится отчет, сколько именно двойных пробелов было исправлено. Нажмите кнопку "ОК", чтобы принять его. После этого вы можете наблюдать, что все двойные пробелы были исправлены и ваш документ приведен в нормальный вид. Это первый способ, как удалить пробелы в числах в Excel, переходим к следующему.

Способ 2: удаление пробелов между разделителями

Если вы устанавливали не двойной, а одинарный пробел в числовых выражениях, то предыдущий метод вам не поможет. Но в него можно внести некоторые коррективы, которые помогут исправить сложившуюся ситуацию. Чтобы убрать пробелы в числах в Excel, выполните следующие действия:

1. Выделите часть таблицы с числовыми данными, в которых находятся лишние пробелы.
2. На ленте инструментов опять же кликните по кнопке «Найти и выделить» и выберите опцию «Заменить». Кстати, это действие можно произвести путем использования горячих клавиш Ctrl+H.
3. Появится уже знакомое окно, где нужно ввести две переменные.
4. В поле «Найти» введите одинарный пробел.
5. Поле «Заменить на» не трогайте, в нем ничего не должно находиться.
6. Нажмите кнопку «Заменить все».

Как и в прошлый раз, вам будет показан отчет, который можно закрыть, нажав кнопку "ОК". Все пробелы между числами будут удалены. Это отличный способ, как можно убрать пробелы в числах в Excel, однако он эффективен лишь тогда, когда эти пробелы были поставлены вручную. Если они установлены автоматически, то необходимо использовать совсем другую инструкцию. Именно о ней и пойдет речь дальше.

Способ 3: удаление путем форматирования

Если вы набираете большие числа в ячейках таблицы и видите, что после нажатия Enter между разделителями появляется пробел, то значит, у вас стоит соответствующая опция форматирования. Сейчас будет рассказано, как убрать пробелы в числах в Excel, отключив ее.

1. Откройте таблицу в программе.
2. Выделите ячейки, в которых стоит пробел между числами.
3. Нажмите правой кнопкой мыши по выделению.
4. Выберите в появившемся контекстном меню опцию «Формат ячеек».
5. В появившемся окне перейдите на вкладку «Число».
6. Откройте раздел «Числовой», находящийся на боковой панели.
7. В правой части окна снимите отметку напротив строки «Разделитель групп разрядов».
8. Нажмите "ОК".

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SUBSTITUTE(A1," ","")
=ПОДСТАВИТЬ(A1;" ";"")

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

Работа с VB проектом (12)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

Как получить слово после последнего пробела

Получить слово до первого пробела достаточно просто:
=ПСТР(A1 ;1;НАЙТИ(" ";A1 )-1)
Но куда чаще сложности возникают с получением слова(символа), находящегося на определенной позиции между пробелом. Я беру в качестве примера пробел, но на самом деле это может быть абсолютно любой символ. Например, для получения второго слова(т.е. слова между первым пробелом и третьим), можно составить такую формулу:
=ПСТР(A1 ;НАЙТИ(" ";A1 )+1;НАЙТИ(" ";A1 ;НАЙТИ(" ";A1 )+1)-НАЙТИ(" ";A1 )-1)
На мой взгляд, выглядит несколько закручено, хотя все предельно просто:

• НАЙТИ(" ";A1 )+1 - ищем позицию первого пробела;
• НАЙТИ(" ";A1;НАЙТИ(" ";A1 )+1) - ищем позицию второго пробела и затем из этой позиции вычитаем позицию первого пробела(-НАЙТИ(" ";A1 )).

Но есть проблема - если второго пробела нет, то формула выдаст ошибку #ЗНАЧ! . Тогда придется еще и проверку на ошибку делать, что явно не добавит формуле элегантности. Поэтому я предпочитаю использовать такую формулу:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(" "&ПОДСТАВИТЬ(A1 ;" ";ПОВТОР(" ";999));1;999*2);999);" ";"")
На первый взгляд куда кошмарнее, чем первая. Но у неё есть ряд преимуществ:
она не нуждается в проверке на отсутствие пробелов;
изменением одного числа можно получить не второе, а 3-е, 4-е и т.д. слово.
Разберем самое главное: чтобы получить первое слово от начала строки, нужно в блоке 999*2 заменить 2 на 1:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(" "&ПОДСТАВИТЬ(A1 ;" ";ПОВТОР(" ";999));1;999*1);999);" ";"")
Чтобы получить 5-е - на 5:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(" "&ПОДСТАВИТЬ(A1 ;" ";ПОВТОР(" ";999));1;999*5);999);" ";"")
И ТО, К ЧЕМУ ШЛИ - СЛОВО ПОСЛЕ ПОСЛЕДНЕГО ПРОБЕЛА
Если вдруг пробелов будет меньше, чем указанное число - то мы получим слово после последнего пробела (т.е. первое слово с конца строки) . Это значит, что если указать *999 - в большинстве случаев получим как раз последнее слово.
Как это работает: при помощи функции ПОДСТАВИТЬ мы заменяем все пробелы в тексте на 999 пробелов(число может быть меньше 999, но не должно быть меньше длины исходной строки). Далее при помощи функции ПСТР мы выдергиваем первые 999 символов, помноженные на число, обозначающее необходимое нам слово(999*1 - первое). По сути только то количество слов, которое указано(в данном случае одно - 999*1). Затем функция ПРАВСИМВ возвращает нам только последнее слово - т.е. нужное нам. А далее та же ПОДСТАВИТЬ убирает лишние теперь пробелы, заменяя их все на пустую строку - "" .
Вроде бы достигли того, что нам нужно было. Но вдруг необходимо получить второе слово с конца строки? Как тогда быть? Считать пробелы? А если у нас их штук 50 хотя бы? Можно использовать некую модификацию приведенной выше формулы, но которая как раз возвращает слово с конца строки:
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(" "&ПОДСТАВИТЬ(A1 ;" ";ПОВТОР(" ";999));999*1);1;999);" ";"")
Принцип тот же: если в блоке 999*1 заменить 1 на 5, то получим 5-е слово с конца строки.
Если необходимо выдергивать слова по пробелам, то лучше дополнить еще одной функцией - СЖПРОБЕЛЫ:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(" "&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";ПОВТОР(" ";999));1;999*1);999);" ";"")
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(" "&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";ПОВТОР(" ";999));999*1);1;999);" ";"")

Так же можно "вытянуть" определенное количество слов:
=СЖПРОБЕЛЫ(ПСТР(ПРАВСИМВ(" "&ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";999));999*3);1;999*2))
3 - третье слово с конца строки.
2 - количество слов.

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

Тогда для получения второй строки(ТЦ Таганка и ТЦ Опус) можно применить такую формулу:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(СИМВОЛ(10)&ПОДСТАВИТЬ(C2 ;СИМВОЛ(10);ПОВТОР(СИМВОЛ(10);999));1;999*2);999);СИМВОЛ(10);"")
СИМВОЛ(10) здесь означает перенос строки. Обычно эти переносы делаются с клавиатуры. Входим в режим редактирования ячейки, ставим курсор в нужное место строки и нажимаем Alt +Enter .
я для получения месяцев(Август 2015 г. и Сентябрь 2015 г.) - такую:
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ("/"&ПОДСТАВИТЬ(C2 ;"/";ПОВТОР("/";999));999*1);1;999);"/";"")
Если лень прописывать внутри формулы один символ несколько раз, его можно записать в ячейку(скажем, G1) и в формуле указать ссылку на эту ячейку:
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(G1 &ПОДСТАВИТЬ(C2 ; G1 ;ПОВТОР(G1 ;999));999*1);1;999); G1 ;"")
Теперь для изменения символа надо будет изменить его один раз в ячейке и формула "вытащит" нужное слово/строку, опираясь именно на этот символ.

Скачать пример:

(29,5 KiB, 2 157 скачиваний)

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}