Удалить проблемы в экселе. Как убрать пробелы в числах в MS 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))

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

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

Из этой статьи Вы узнаете 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 – это первая ячейка столбца с числами или словами, в которой должны быть удалены все пробелы.

При работе с программой 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).

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

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

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

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

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

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

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

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

Как в Excel поставить пробел , пробел в формуле Excel, неразрывный пробел, как поставить или заменить пробел , т.д. – всё это и другое о пробелах в Excel здесь.
Пробел – это расстояние в предложении между словами, цифрами, т.д. По умолчанию, когда мы нажимаем на клавишу пробела, курсор передвигается на один пробел. Но, если мы удержим кнопку пробела чуть дольше или по ошибке нажмем несколько раз, то пробел будет длиннее, т.е. появятся лишние пробелы. Часто эти лишние пробелы мешают работе Excel. Например, при сравнивании списков, формулам при подсчете (выдает ошибку), т.д.
Но, иногда другой, не одиночный пробел помогает. Например – неразрывный пробел.
Итак, всё по порядку.
Как в Excel поставить пробел.
Нажимаем на клавишу «пробел» на компьютере, ноутбуке. Это внизу клавиатуры в центре длинная кнопка. Если нужно поставить несколько пробелов, нажимаем кнопку «Пробел» несколько раз.
Пробел в формуле Excel.
В формуле пробел напишем так: "" "" (прямые кавычки, нажимаем клавишу «Пробел», прямые кавычки).
Неразрывный пробел в Excel.
Неразрывный пробел – пробел, который не позволяет разрывать слова, цифры при переносе на другую строку. Например, при увеличении шрифта цифра не будет разбиваться на части и переходить на другую строку ячейки. Неразрывный пробел ставится в том случае, если часть сочетания слов переносится на другую строку.
Например, неразрывный пробел ставится между фамилией и инициалами, если инициалы переносятся на другую строку. Между числом и названием месяца, года, между знаком № и числом (№12), между суммой и названием валюты (12 руб.), если часть числа с разрядами переносится на другую строку, т.д.
Неразрывный пробел в Excel ставится так: нажимаем и удерживаем кнопки на клавиатуре «Ctrl» + «Shift», удерживая их нажатыми, нажимаем клавишу «Пробел».
Или, ставим курсор, где будет стоять неразрывный пробел, и нажимаем кнопку "Alt", удерживая её нажатой, набираем на клавиатуре 0160. Отпускаем клавишу "Alt".
Или на закладке «Вставка» в разделе «Символы» нажать на кнопку «Символ». В появившемся диалоговом окне перейти на закладку «Специальные знаки» и выбрать «Неразрывный пробел». Нажать кнопку «Вставить». Сколько раз нажмем кнопку «Вставить, столько неразрывных пробелов вставиться. Затем нажимаем кнопку «Закрыть», чтобы закрыть окно. Пример. В столбце В убрали пробелы лишние, неразрывные пробелы, другие невидимые знаки.