Как удалить последний пробел в ячейке excel. Функция сжпробелы в excel и примеры ее использования

Многие ошибки возникают из-за того, что вы даже не можете увидеть: из-за пробела. Рассмотрим пример, показанный на рис. 198.1. Ячейка В2 содержит формулу, которая ищет название цвета в ячейке В1 и возвращает соответствующий код из таблицы: =ВПР(B1;D2:E9;2;ЛОЖЬ) .

На рис. 198.2 формула в ячейке В2 возвращает ошибку указывающую на то, что красный не был найден в таблице. Тысячи пользователей Excel потратили бы уйму времени, пытаясь выяснить, почему так произошло. В данном случае ответ прост: ячейка D7 не содержит слово Красный . Точнее, она содержит слово Красный , за которым идет пробел. Для Excel эти строки совершенно разные.

Если лист содержит тысячи текстовых записей и вам необходимо выполнить сравнение, то сначала нужно найти ячейки, которые содержат избыточные пробелы, а затем исправить их. Термин «избыточный пробел» означает текстовую запись, которая содержит что-нибудь из следующего набора: один или несколько пробелов в начале, один или несколько пробелов в конце либо от двух идущих вместе пробелов.

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

1. Выделите все текстовые ячейки, к которым нужно применить условное форматирование.
2. Выберите Главная Стили Условное форматирование Создать правило для открытия диалогового окна .
3. В верхней части окна выберите пункт Использовать формулу для определения форматирующих ячеек.
4. Введите формулу, аналогичную следующей, в нижней части диалогового окна: =А1СЖПРОБЕЛЫ(А1) . Заметьте: здесь предполагается, что ячейка А1 является верхней левой ячейкой в выделенном фрагменте. Если это не так, замените адрес верхней левой ячейки для набора ячеек, которые вы выбрали в шаге 1.
5. Нажмите кнопку Формат для открытия окна Формат ячеек и выберите тип форматирования для ячеек, содержащих избыточные пробелы. Например, задайте выделение желтым цветом.
6. Нажмите кнопку ОК , чтобы закрыть окно Формат ячеек , и снова нажмите ОК , чтобы закрыть окно Создание правила форматирования .

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

Из-за особенностей работы функции СЖПРОБЕЛЫ формула в шаге 4 также применяет условное форматирование ко всем числовым ячейкам. Вот немного более сложная формула, которая не применяет форматирование к числовым ячейкам: =ЕСЛИ(НЕ(ЕНЕТЕКСТ(А1));А1СЖПРОБЕЛЫ(А1))

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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



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



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



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



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

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



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



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



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

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

В процессе работы с любыми документами пользователь незаметно для себя может оставлять нежелательные пробелы между словами или числами. Существует множество способов исправления подобной ситуации. Рассмотрим их в статье.

Как удалить пробелы в Excel

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

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

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

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

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

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

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

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

После этого вы обнаружите, что пробелы между числами исчезли, соответственно, поставленная задача выполнена.

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

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

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

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

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

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

1. Установите курсор в той ячейке, которая находится параллельно столбцу или строке, пробелы из которых нужно убрать.
2. Откройте окно Мастера функций, нажав по соответствующей кнопке, находящейся рядом со строкой функций.
3. В списке «Категория» выберите "Текстовый", а в списке с перечнем функций выделите «СЖПРОБЕЛЫ» и нажмите «ОК».
4. Появится окно ввода функции. В нем вам нужно указать ячейку, в которой хотите убрать пробелы. Для этого просто кликните по ней левой кнопкой мыши.
5. Нажмите «ОК».

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

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

Получить слово до первого пробела достаточно просто:
=ПСТР(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}}