Иногда у пользователя Excel появляется проблема: в начале и конце ячейки показываются ненужные пробелы или появляются непонятные расстояния между словами. Их основная проблема в том, что они нередко незаметны для человека. Поэтому часто он ничего не подозревает, особенно если глаз достаточно замыленный большими объемами данных. Проблема еще в том, что не существует метода обнаружить визуально наличие лишних пробелов, находящихся в конце ячеек при режиме отображения «часть текста». Сегодня мы детально разберемся в том, что можно сделать для того, чтобы убрать все пробелы.
Содержание
- Как убрать все пробелы в конце ячеек при помощи «Найти и заменить»
- Удаление двойных интервалов
- Удаление всех пробелов в тексте
- Как удалить пробелы в ячейках при помощи формулы СЖПРОБЕЛЫ
- Как удалить пробелы в начале и в конце ячейки формулами
- Как удалить разрыв строки и непечатаемые символы
- Удаление неразрывных пробелов
- Как найти и удалить непечатаемый символ
- Преобразование цифр с пробелами в число
- Как посчитать пробелы при помощи формул
Как убрать все пробелы в конце ячеек при помощи «Найти и заменить»
Этот метод можно использовать в некоторых ситуациях, потому что для него характерна особая быстрота и простота.
Удаление двойных интервалов
Если в начале или конце ячейки стоит пробел, не рекомендуется пользоваться этим способом. Все потому, что один из них все равно будет. Тем не менее, рассмотрим, как пользоваться этим методом для того, чтобы убрать двойные пробелы:
- Сделайте клик по ячейке, в которой нужно убрать ненужные пробелы. Также можно выделить целый диапазон значений.
- Откройте главное меню. Там есть группа инструментов «Редактирование» и там нужно нажать на кнопку «Найти и выделить». После этого появится небольшая менюшка, в которой нужно выбрать «Заменить». Также возможно использование комбинации горячих клавиш CTRL+H.
- В появившемся окне нужно ввести в поле «Найти» двойной пробел и заменить его на одинарный.
После того, как мы введем все необходимые данные, нужно просто нажать на кнопку «Заменить все». А вот нажимать на кнопку «Заменить» не рекомендуется, потому что она заменяет только одну ошибку, и ее нужно нажимать много раз для исправления.
Бывают и более сложные ситуации. Например, у пользователя в ячейке три интервала, поэтому при выполнении в точности всех описанных выше действий останется два, поскольку описанный ранее метод убирает лишь один пропуск. Поэтому рекомендуется выполнить эту операцию несколько раз, чтобы оставшиеся пробелы были убраны.
Вы можете сами понять недостатки этого метода. Перед некоторыми словами начальный пробел остается. Точно так же некоторые конечные пробелы остались видны, просто их нельзя увидеть на глаз. Поэтому если выражение текстовое в ячейке, использовать этот способ нежелательно.
Удаление всех пробелов в тексте
Может понадобиться и удаление всех пробелов. Если это надо сделать, то нужно выполнить такие шаги:
- Выделить ячейки, с которыми мы будем выполнять эту операцию.
- Открываем вкладку «Главная», и там нажимаем на кнопку «Заменить» так, как мы это делали в предыдущем примере. Сочетание клавиш то же самое.
- В окне вводим следующие данные:
- Найти: одинарный пробел.
- Поле «Заменить» не заполняем.
- После этого нажимаем на «Заменить все».
Этот способ позволяет удалить все пробелы, которые есть в ячейке. Недостаток также понятен. С помощью данного метода можно удалить и нужные пробелы, которые ставятся между словами. Поэтому перед тем, как его использовать, нужно удостовериться, что все ячейки в диапазоне содержат не больше одного слова. По этой причине данный метод лучше всего подходит для числовых операций.
В ряде случаев могут возникать ненужные пробелы между разрядами. Очень часто это происходит, когда пользователь пытается импортировать информацию из другой программы. Также ячейка может быть в текстовом формате. На данном этапе это не является проблемой, а потом мы разберемся, что делать для того, чтобы изменить формат на числовой.
Как удалить пробелы в ячейках при помощи формулы СЖПРОБЕЛЫ
Также есть специальная функция, предназначенная для того, чтобы убрать ненужные пробелы. Называется она СЖПРОБЕЛЫ. Она дает возможность убрать их за раз как начальные, так и ведущие. Также она способна подставить под нож промежуточные. После нее только один интервал остается, поэтому нужно быть в определенной степени осторожным. Синтаксис этой функции следующий: =СЖПРОБЕЛЫ(A2).
В скобках надо указать ячейку, из какой мы будем удалять пробелы. Так, как видно на следующей картинке, этот оператор смог легко удалить все пробелы в начале и конце строки, записанной в ячейке, а также – ненужные интервалы внутри строки.
Далее необходимо совершить замену тех значений, которые содержатся в первоначальной колонке, на обновленные. Самый простой метод, как сделать это – «Специальная вставка». Там нужно выбрать пункт «Значения».
Как удалить пробелы в начале и в конце ячейки формулами
Что касается исключительно пробелов, стоящих в начале и конце, то есть специальные формулы, с использованием которых можно их удалить. На этом скриншоте отчетливо видно формулу, с использованием которой мы достигли этой цели.
Так как вы поняли из описанного ранее, функция СЖПРОБЕЛЫ не подходит для удаления пробелов лишь в начале и конце строки. Чтобы оставить нетронутыми срединные пробелы, формула будет значительно сложнее и включает в себя сразу несколько операторов, как мы видим из скриншота выше.
Работает оно следующим образом: оно определяет, в каком месте находится первый символ текстовой строки. После этого данное число принимается в качестве аргумента функцией ПСТР, которая затем передает строку полностью, начиная с первой позиции.
В свою очередь, оператор ДЛСТР определяет то, насколько большой является определенная строка. После того, как мы ввели эту формулу, проверяем результат. Если пробелы исчезли, то мы все сделали правильно. Только в качестве финального этапа необходимо заменить первоначальный текст на те значения, которые мы получили выше.
Если же нам необходимо только конечные пробелы убрать, то нам надо использовать еще более сложную формулу: =ЛЕВСИМВ(A2;МАКС((ПСТР(A2&ПОВТОР(» «;99);СТРОКА(A2:A100);1)<>» «)*СТРОКА(A2:A100))).
Важно: эта формула должна вводиться в качестве формулы массива. То есть, необходимо использовать комбинацию клавиш Ctrl + Shift + Enter. У нас не очень хорошо удалось выровнять по правому краю значение, находящееся в первой колонке, потому что количество пробелов в конце каждой строки было различным. Во втором столбце мы видим, что у нас получилось решить эту проблему и текст был красиво расположен.
Как удалить разрыв строки и непечатаемые символы
В ходе переноса информации с других программ нередко появляются непечатаемые символы, которые выглядят, как пробел или же немного по-другому, но они занимают место в ячейке. Это разнообразные возвраты каретки, переводы строк, табуляция и так далее.
Описанная ранее функция СЖПРОБЕЛЫ прекрасно справляется с тем, чтобы убрать из ячейки пробелы, но она не подходит для очистки ее от непечатаемых символов. Фактически она нужна только для того, чтобы убирать символ с кодом 32 в ASCII таблице 7-битного разряда. Именно так кодируется этот символ. Но есть и значения с другими кодами, для которых нужно использовать функцию ПЕЧСИМВ. В английской локализации программы она называется CLEAN. Как мы можем понять из названия, с помощью этого оператора можно очистить ячейку от всевозможного хлама и удалять первые 32 непечатаемые символы.
Допустим, нам нужно убрать и пробелы, и непечатаемые символы в ячейке A2. В этом случае наша задача – использовать сначала формулу ПЕЧСИМВ для того, чтобы убрать непечатные знаки, после чего передать эту строку функции СЖПРОБЕЛЫ, которая убирает оставшиеся пробелы. Результат использования этой комбинации экселевских операторов мы видим на скриншоте.
По сути, каждый раз, когда с использованием описанного выше метода вы убираете разрывы строки, слова, разделенные ими, склеиваются. Как же исправить эту ситуацию?
- Открываем диалоговое окно «Найти и заменить» методом, описанным выше. В поле «Найти» указываем символ возврата каретки. Для этого вводим комбинацию клавиш Ctrl + J. В свою очередь, в поле «Заменить» используем символ пробела. Когда мы нажимаем кнопку «Заменить все» все разрывы автоматически заменяются на пробелы.
- Также можно заменять возврат каретки на пробелы с помощью этой формулы: =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2; СИМВОЛ(13);» «); СИМВОЛ(10); » «)) . Естественно, надо адрес ячейки в функции ПОДСТАВИТЬ заменить на тот, который нужен конкретно в вашей ситуации.
Как видим, теперь проблема с почтовым адресом оказалась успешно решенной.
Удаление неразрывных пробелов
Очень часто пользователь, который использует функцию СЖПРОБЕЛЫ или ПЕЧСИМВ обнаруживает, что они не срабатывают. Все потому, что есть еще один непечатаемый символ, который обозначает неразрывные пробелы. Очень часто такие символы появляются, когда пользователь пытается вставить информацию из интернета сразу в таблицу Excel.
Для этого необходимо заменить символ неразрывного пробела (он имеет код 160) на пробел с помощью функции ПОДСТАВИТЬ. После этого пробел убирается таким же образом, как и раньше. Если оказывается, что рабочий лист еще и содержит непечатаемые символы, то также необходимо использовать такую формулу: =СЖПРОБЕЛЫ(ПЕЧСИМВ((ПОДСТАВИТЬ(A2;СИМВОЛ(160);» «)))).
Как найти и удалить непечатаемый символ
А что делать, если мы уже знаем, какой конкретной непечатаемый символ нам мешает? Или же тот символ, который нам надо удалить, не находится в пределах первых 32 в таблице ASCII? В этом случае нужно использовать функцию ПОДСТАВИТЬ, чтобы заменить его на пробел.
Если же мы не знаем код непечатаемого символа, который нам необходимо убрать, то нужно воспользоваться функцией КОДСИМВ, который позволяет его получить. Причем нам не нужно использовать функции КОДСИМВ и ПОДСТАВИТЬ по отдельности, их можно сразу использовать в одной формуле, чтобы компьютер сначала определил код мешающего символа, а потом записал его в качестве аргумента ПОДСТАВИТЬ.
Сама структура использования функции в нашем случае довольно сложная. Для того, чтобы получить тот символ, который нам нужен, нам необходимо использовать операторы ЛЕВСИМВ, ПРАВСИМВ или ПСТР. Давайте более детально раскроем, что и в каких случаях нужно использовать:
- Если непонятный непечатаемый символ, который нам нужно удалить, находится в самом начале, необходимо использовать такую формулу: =КОДСИМВ(ЛЕВСИМВ(A2;1))
- Если же он располагается в конце строки, формула аналогичная, но используется другой оператор: = КОДСИМВ(ПРАВСИМВ(A2;1))
- Если же он находится в середине строки, то нам необходимо использовать функцию ПСТР, где в качестве значения n выступает номер знака, где находится проблемный символ.
Комбинации могут быть самыми причудливыми. Например, с помощью этой формулы пользователь может определить нужные коды символов, заменить их на пробелы и сразу же их уничтожить. И все это в одной формуле: =СЖПРОБЕЛЫ(ПЕЧСИМВ((ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;СИМВОЛ(127);» «);СИМВОЛ(160);» «)))).
Преобразование цифр с пробелами в число
Иногда может понадобиться удалить даже те пробелы, которые используются для разделения разрядов чисел. Конечно, они позволяют значительно упростить чтение больших чисел, но при этом они не дают формулам правильно работать. Поэтому нельзя использовать пробелы в качестве разделителей, нужно применять средства форматирования для этого. Для этого нужно воспользоваться меню «Формат ячейки», которое вызывается с помощью контекстного меню.
Как посчитать пробелы при помощи формул
Иногда же пользователю необходимо определить общее количество пробелов в формуле. Для этого нужно выполнить следующую формулу: =ДЛСТР(A3) — ДЛСТР(ПОДСТАВИТЬ(A3;» «;»»)). Если же нам нужно определить, сколько всего ненужных символов в ячейке, необходимо воспользоваться такой формулой: =ДЛСТР(A3)-ДЛСТР(СЖПРОБЕЛЫ(A3)).
Как видим, вовсе несложно удалить ненужные пробелы из таблицы, даже если они скрыты или находятся на нестандартной позиции. Что касается удаления одного или нескольких пробелов, которые находятся в начале или конце строчки, так это и вовсе тривиальная задача.
Оцените качество статьи. Нам важно ваше мнение:
2 способа удалить пробелы между словами или числами в ячейках Excel
Смотрите также не буду) по какой либо на тулбаре. которой приходится не выдала по ним Проверим это наверняка, в Excel это но там есть той же функцией. В2, чтобы написать=ПОДСТАВИТЬ(A1;» «;»») в которых группы
пробелы. Как их. узнаете, как очиститьИз этой статьи ВыЕще раз СПАСИБО! пробел в строкеwadim82 одноратно производит выгрузку результат. Чтобы исправить изменив диапазон вНа небольшом примере рассмотрим, может обернуться серьезной пробелы, непечатаемые знаки,
В ячейке В6 формулу, которая удалитЗдесь разрядов (тысячи, миллионы, удалить, Вы можетеВ первой ячейке вспомогательного данные от лишних узнаете 2 быстрыхSerge_007 именно с конца?: Точно, в exel (2007). это, добавим к команде СУММЕСЛИ. Вместо как пользоваться функцией проблемой, потому что неразрывный пробел, т.д. написали такую формулу. лишние пробелы. ЗаходимA1
миллиарды) разделены пробелами. узнать из этого столбца (C2) введите пробелов:
- способа удалить лишние: Конечно можноНадеюсь не запуталсразу почему то
- Строки имеют следующий
- ВПР функцию СЖПРОБЕЛЫ B3:B14 пропишем D3:D14 СЖПРОБЕЛЫ. Имеем таблицу,
- при выполнении командТеперь на закладке «Главная»
Удаляем все лишние пробелы между словами, отсекаем начальные и конечные пробелы
=СЖПРОБЕЛЫ(A6) на закладке «Формулы»– это первая В этом случае примера. формулу для удаленияУдаляем все лишние пробелы пробелы между словамиПланета — это ). не догадался. формат «название номенклатуры, и посмотрим, что и посмотрим результат. в которой указаны программа просто не нажимаем кнопку «НайтиКак удалить непечатаемые знаки в раздел «Библиотека ячейка столбца с Excel рассматривает числаЭтот вариант требует меньше
Используем функцию «СЖПРОБЕЛЫ» для удаления лишних пробелов
лишних пробелов: между словами, а или все пробелы наш дружественный сайтБлагодарю, если ктоА если в шт» или «название получится. Теперь плюшевых зайцев
- наименования детских игрушек будет учитывать ячейки и заменить». Вставляем в Excel функций», выбираем «Текстовые».
- числами или словами, как текст и действий, но позволяет=TRIM(A2)
также отсекаем начальные
из ячеек Excel.
- 3Ы Автора только откликнется… конце строки пробел номенклатуры , шт»,Т.к. пробелы нам нужно действительно 5, медведей и их количество.
- с дополнительными пробелами. скопированное в строку. И здесь нажимаем в которой должны никакая математическая операция удалить только лишние=СЖПРОБЕЛЫ(A2) и конечные пробелы Вы можете использовать укажитеSerge_007 только. или «название номенклатуры удалить в искомом – 6 и Не очень грамотныйКак удалить лишние пробелы
- «Найти».
Их тоже можно на функцию «СЖПРОБЕЛЫ». быть удалены все выполнена быть не пробелы между словами.Скопируйте эту формулу вИспользуем функцию TRIM (СЖПРОБЕЛЫ) функциюshirna
: Можноvikttur ,шт». значении, команду СЖПРОБЕЛЫ т.п. Получается, символы оператор вел примитивный в Excel вВ строке «Заменить» ничего занести в таблицу Эта функция поможет
Применяем инструмент «Найти и заменить», чтобы удалить лишние пробелы между словами
пробелы. может. Начальные и конечные остальные ячейки столбца.Применяем инструмент Find &TRIM: Serge_007 ИсправилсяПримеров на форуме: =ЛЕВСИМВ(A1;ДЛСТР(A1)-4)
- Как быстро произвести поставим на первое табуляции играют важную учет: при появлении автоматическом режиме? Чтобы
- не пишем, оставляем Excel, скопировав данныеудалить лишние пробелы ExcelДалее выполните те жеПростейший способ избавиться от пробелы будут также
- Вы можете использовать Replace (Найти и(СЖПРОБЕЛЫ) или инструментvikttur сотниwadim82 удаление в множествах
- место в синтаксисе роль, и их дополнительных единиц игрушек не испортить зрение, пустую. Нажимаем кнопку из Word, если, оставляя один пробел.
- шаги, что и лишних пробелов – отсечены до 1, советы из статьи заменить)Find & Replace: Держите «сороковую раму»:shirna
Удаляем все пробелы между числами
: Всем спасибо за ячейках в конце ВПР. Формула немного нужно подчищать. он просто впечатывал вглядываясь в каждую «Заменить все». Нажимаем мы их самиВ появившемся диалоговом окне в разделе об это воспользоваться стандартным
но не удалены Как вставить одинаковуюУдаляем ВСЕ пробелы между(Найти и заменить),200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;99));99*2+50;999)): Спасибо, ёмко!
- помощь. строки: видоизменилась, и еслиИспользование функции СЖПРОБЕЛЫ вместе новые позиции, несмотря
- ячейку и убирая кнопку «Закрыть». Остался не ставили. Непечатаемые в строке «Текст» удалении всех лишних инструментом Excel –
- полностью. формулу сразу во словами или числами чтобы вычистить содержимоеAlexMЗато теперь точноВсе отлично работает.1. «, шт»
- теперь мы протянем с другими функциями на то, что лишние доступы клавишей один пробел. Получилось знаки – это
Удаляем все пробелы при помощи формулы
указываем адрес первой пробелов между словамиFind & ReplaceВыделите один или несколько все выбранные ячейки.Предположим, есть таблица с ячеек в Excel.
: Виктор, в задании
знаю, что можно!!!
ikki . «название номенклатуры» ее до низа расширяет возможности пользователя. аналогичные наименования уже Backspace, можно воспользоваться так. разрывы строк, перенос
ячейки из нашего при помощи формулы.(Найти и заменить). столбцов с данными,Замените исходный столбец полученными двумя столбцами. В
Когда Вы вставляете данные отмечено
))
: а не проще
— оставляем в
office-guru.ru
Как удалить лишние пробелы в Excel.
таблицы, функция проставит Логично, что она есть. Наша задача: специальной функцией –В Excel можно выделять текста на другую списка. Мы указалиУрок подготовлен для ВасНажмите в которых нужно данными. Для этого столбце из внешнего источникаПоэтому формула должнаНе примите за ли один раз конце названия без нам все значения. будет использоваться вместе подсчитать общую сумму
СЖПРОБЕЛЫ. ячейки с лишними
страницу, конец абзаца, адрес ячейки А2. командой сайта office-guru.ruCtrl+Space удалить лишние пробелы
выделите все ячейкиName на лист Excel быть чуть другой лентяя, честно «отсидел» изменить настройку отчета пробелов, без запятой Теперь все правильно. с теми функциями, каждого вида.Да, проговорить название команды пробелами в момент
табуляция, др.Нажимаем «ОК». Теперь формулуИсточник: https://www.ablebits.com/office-addins-blog/2013/11/15/trim-spaces-between-words/(Пробел), чтобы выделить между словами. вспомогательного столбца и
в первой ячейке (простой текст, числа200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЖПРОБЕЛЫ(ПРАВБ(ПОДСТАВИТЬ(A1;» «;ПОВТОР(» «;99));99*2)) на форуме больше 1С и сохранить и шт.
Избавиться от лишних пробелов которые исследуют массивыНевооруженным глазом видно, что
может оказаться непросто. заполнения ячейки таблицы,Чтобы копируем вниз поПеревел: Антон Андронов все ячейки вНажмите нажмите содержится имя и т.д.), тоДвоечка в конце часа ) Научился
её? :)))2. » , можно и без и возвращают данные. некоторые позиции написаны Но вот понять списка, бланка. Какзаменить непечатаемые знаки на
столбцу всего списка.Автор: Антон Андронов столбце.Ctrl+HCtrl+CJohn Doe
можете получить лишние формулы указывает номер удалять по количеству
а завтра появится шт». «название номенклатуры» использования функции СЖПРОБЕЛЫ. В частности, это с явными лишними ее синтаксис и для этого настроить пробел в Excel В столбце ВКак убратьНажмите
, чтобы открылось диалоговое, чтобы скопировать данные, записанное корректно, т.е. пробелы вместе с проделя с конца
символов — тоже
единица измерения «тыс. — оставляем в Воспользуемся старым проверенным функции НАЙТИ, ЛЕВСИМВ, пробелами. Рассмотрим, к принцип работы очень таблицу, смотрите в, пишем в соседнюю
получился такой список.лишние пробелы в ExcelCtrl+H окно в буфер обмена.Далее без лишних пробелов. важными данными. Это строки, по которому полезно, но не шт.» или «пачка»,… конце названия без способом, который нам ПРАВСИМВ и др. чему это может легко. Если начать статье «Как найти ячейку такую формулу. Лишние пробелы удалились., которые мешают работе
, чтобы открыть диалоговоеFind & Replace выделите первую ячейку Все остальные ячейки могут быть начальные
делится текст. то.и что тогда? пробелов, без запятой знаком еще из На практике оказывается, привести.
вводить команду, то пробел в Excel».=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A6;СИМВОЛ(160);СИМВОЛ(32)))Как убрать пробелы в фильтра, сортировке, сравнить окно(Найти и заменить). исходного столбца (в содержат вариант записи
и конечные пробелы,shirnaPelenaclauser и шт. WORD – команда что чаще всегоПодсчитывать общее количество позиций подсветится следующее: =СЖПРОБЕЛЫ(текст).Часто можно услышатьМы написали в ячейку числах Excel списки, мешают посчитатьFind & ReplaceВведите два раза пробел нашем случае A2), с лишними пробелами несколько пробелов между: И еще раз
excel-office.ru
Функция СЖПРОБЕЛЫ в Excel и примеры ее использования
: Уже вроде ответили: Функция ВБА Replace().3. 2. « НАЙТИ-ЗАМЕНИТЬ. СЖПРОБЕЛЫ используется вместе по каждому виду Т.е. в скобках среди тех, кто В6 формулу. В. формуле, другое. Например,(Найти и заменить).
в поле нажмите между именем и словами или разделители спасибо! Все формулыhttp://www.planetaexcel.ru/forum….D=51712 Синтаксис смотрите в ,шт» . «названиеПример останется тот же с ВПР.
Синтаксис функции СЖПРОБЕЛЫ в Excel
игрушек будем через нужно всего лишь работает в Excel формуле: символ 160Если формула не нам нужно сравнитьВ полеFind WhatShift+F10 фамилией, а также разрядов в числах. себе в отдельныйSerge_007
хелпе. номенклатуры» — оставляем самый. Выделяем столбец,Рассмотрим на примере той функцию СУММЕСЛИ. Вводим
задать ячейку (ячейки), слова «горячие клавиши» –это код неразрывного считает и выдает два списка, ноFind What(Найти) и одинили клавишу вызова в начале иСледовательно, таблица выглядит малость файл записываю: См. вложениеshirna
в конце названия
Пример использования функции СЖПРОБЕЛЫ
в котором прописаны же таблицы. Задача: ее, протягиваем на в которых необходимо или «сочетание клавиш». пробела, а символ ошибку, то одной лишние пробелы не(Найти) введите один раз в поле контекстного меню, а в конце (начальные неопрятно и становитсяshirnashirna: Уважаемые знатоки, прошу без пробелов, без
наименования игрушек и проставить цену напротив остальные ячейки и удалить пробелы. В Excel определенные 32 – это
из причин может дадут этого сделать пробел. Убедитесь, чтоReplace With затем клавишу и конечные пробелы). трудной для использования.: для AlexM: Да, точно. помочь решением. запятой и шт. нажимаем CTRL+H. В каждой позиции. Справа смотрим результат. Программа
Примечание: функция убирает все функции можно вызвать, код лишних пробелов быть лишний пробел правильно. Поэтому лишние
поле(Заменить на).V Во втором столбце, Казалось бы, простая200?’200px’:»+(this.scrollHeight+5)+’px’);»>=TRIM(RIGHTB(SUBSTITUTE(A1,» «,REPT(» «,99)),99*2))Если кому надо,Есть текстовая иСпасибо за помощь. появившемся окне напротив дана вспомогательная таблица, выдала ответ по лишние пробелы, за нажав на определенную (начальных и промежуточных). в формуле, в пробелы нужно убрать.
СЖПРОБЕЛЫ с другими функциями
Replace WithНажмите кнопку(З). с названием задача может статьПочему то не то вот: числовая информация вКазанский НАЙТИ проставляем пробел, в которой прописана плюшевым зайцам: 3. исключением того, который функциональную клавишу илиЕщё один способ
числе, если число Но, иногда пробелы(Заменить на) –Replace allУдалите вспомогательный столбец.Length непростой. Например, найти работает, может делаю200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЛЕВБ(A1;ПОИСК(«»;ПОДСТАВИТЬ(A1;» «;»»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))-1))-1) строке. Например: Мама: Вряд ли такие а напротив ЗАМЕНИТЬ цена каждой игрушки.
Хотя мы видим, ставится по умолчанию сочетание нескольких клавиш.
удалить лишние пробелыExcel написано с разрядами, нужны, например - пустое.(Заменить все), аГотово! Мы удалили все, показано количество символов покупателя с именем что не так…это 2 пробела 2 (мыла) раму
сочетания встречаются где-то НА не пишем Теоретически мы можем что их должно между словами. Это удобно при– это воспользоваться но не функцией неразрывный пробел. КакиеНажмите кнопку затем лишние пробелы при
Как еще можно удалить лишние пробелы в Excel?
в каждом имени.John Doeданные вставляю в с конца строки 40 кроме конца строки. ничего. вручную перепечатать нужные
быть 3+2=5. ВТе, кто хорошо владеет работе, как на функцией «Найти и «Разряды», а мы бывают пробелы, какReplace allОК помощи функцииВ Excel существует функция
(нет лишних пробелов ячейку А1 убирает )Можно ли формулой Поэтому Ctrl+H, заменитьПроблема такого способа в нам цифры, но чем проблема? В техникой печатания, наверняка
exceltable.com
Как удалить пробел, запятую, шт. в конце строки.
ноутбуке, так и заменить». О работе
сами разделили число их заменить, где(Заменить все), затем, чтобы закрыть появившееся
TRIMTRIM между частями имени),китинshirna убрать «раму 40″
«, шт» на том, что он раз мы в лишних пробелах.
недоумевают, как вообще на ПК, быстрее с пробелом. применить, смотрите вОК
информационное окно.(СЖПРОБЕЛЫ). К сожалению,(СЖПРОБЕЛЫ), которая используется в то время: shirna, а вы: Serge_007 Отдельное спасибо
указав границу удаления ничто, заменить « убирает все, в Excel, будем использоватьЗапишем функцию в ячейке могут появиться лишние
вызвать поределенную функцию.
функцией Excel «Найти иИли, если в статье «Пробел в. Вуаля! Все пробелыПовторяйте шаг 4 до этот способ занимает для удаления лишних как в таблице перевести формулу попробуйте(кнопочка за файл! Это как второй пробел
, шт» на том числе между ВПР. D3. В качестве пробелы. Но опытные Про эти волшебные
заменить» числе стоит неразрывный
Excel». удалены.
тех пор, пока много времени, особенно, пробелов из текста.
он сохранён как там есть с
точно то, что с конца строки, ничто, заменить «
словами. Поэтому этот
Если мы протянем формулу аргумента введем ячейку пользователи знают, что кнопки читайте вчитайте в статье пробел. Например, при
Как убрать лишние пробелыВозможно, Вам пригодится способ не появится сообщение
когда таблица достаточно
Далее Вы найдёте « флажком.нажмите ее) нужно! Буду хранить
planetaexcel.ru
убрать текст по кол. пробелов с конца строки (Формулы)
чтобы осталось: Мама ,шт» на ничто путь используют, преимущественно,
до конца таблицы, B3, в которой информация для Excel статье «Горячие клавиши «Поиск в Excel».
копировании данных из в Excel. удалить все пробелыWe couldn’t find anything велика. пошаговую инструкцию поJohn Doeshirna на флешке в 2 (мыла) ? (без кавычек). для удаления лишних увидим следующее: значится наименование игрушки. часто копируется из
Excel». Сначала копируем пробел
Word, перешел иУ нас есть
при помощи формулы. to replace…
Замечание: работе с этим
«. Или числа, которые: для КИТИН
сейфе ) И наоборот, убратьЗапишите эти действия
пробелов в составеРозовым цветом мы специальноТеперь протянем функцию до разных источников, которыеЛишние пробелы в тексте (место между цифрами). неразрывный пробел. В такой список с
Для этого Вы(Мы не нашли
Если после применения
инструментом: не могут быть
Вот теперь яА можно Ваш
все чтобы осталось в макрос, размещенный чисел.
подсветили те позиции, 14 строки и могли заполняться дилетантами.
замечают не все. Пробел там есть, Word часто пользуются лишними пробелами в можете создать вспомогательный ничего, что нужно формулы Вы по-прежнему
Рядом со своими данными просуммированы, а виноваты знаю, что это файл выложить в только «раму 40″
в Личной книге
wadim82 которые написаны с
увидим, что тексты Поэтому в тексте
И не для просто он не
неразрывными пробелами. ячейках А2 и
столбец и ввести было заменить…).
видите лишние пробелы,
добавьте вспомогательный столбец. в этом опять кнопка )) Все
мой вопрос на но опять же
макросов, создайте сочетание
: Вопрос, лишними пробелами. Поэтому действительно выровнялись, потому и встречаются опечатки всех это, в
виден. Получается, мыУбрать такие пробелы А3. такую формулу:Предположим, у Вас есть
скорее всего в Можете назвать его
лишние пробелы. получилось. planetaexcel.ru ?(без разрешения указав границу удаления
клавиш или кнопкуЕсть 1С, с
команда ВПР не что пробелы удалились. и ненужные пробелы. общем-то, важно. Но скопировали пустое место,
в числах можноСтавим курсор в ячейке
=SUBSTITUTE(A1,» «,»») таблица с числами, тексте содержатся неразрывныеTrim
Из этой статьи Вы
excelworld.ru
Спасибо!
- О вреде лишних пробелов
- Способы удаления лишних пробелов
- С помощью процедуры “Найти и заменить”
- Функцией СЖПРОБЕЛЫ
- С помощью готовой процедуры в !SEMTools
- Удалить пробелы в конце строки
- Удалить пробелы в начале строки
Основная и досадная проблема с лишними пробелами, которая мешает их удалению вручную — пробел является невидимым символом.
Между слов лишние пробелы еще заметны по увеличивающемуся между ними расстоянию, пробел с начала строки тоже легко выявить, т.к. строка смещается от левой границы ячейки. А вот если пробел в строке идет после последнего символа, или в ячейке в принципе одни пробелы, их не видно вовсе.
О вреде лишних пробелов
Наличие лишних пробелов может помешать работе в следующих случаях:
- Требуется сравнить ячейки между собой. Это может быть как поиск повторяющихся значений внутри диапазона, так и сравнение двух списков чтобы проверить наличие схожих позиций в них;
- При построении сводных таблиц строки “схлопываются”, если являются полностью идентичными, и разница в один пробел не позволит им “схлопнуться”. Алгоритм построения сводных таблиц также сравнивает ячейки между собой;
- С помощью “ВПР” или сочетания ИНДЕКС/ПОИСКПОЗ можно “подтянуть” данные из одной таблицы в другую. Эти функции тоже сравнивают ячейки при поиске и учитывают разницу в виде пробелов.
Обычно лишние пробелы не несут никакой пользы, и популярная задача – удалить их из текста. Но удалить именно лишние, чтобы фразы не склеивались в одно слово.
Способы удаления лишних пробелов
От обычных пробелов можно избавиться тремя известными мне способами. Рассмотрим их подробнее.
С помощью процедуры “Найти и заменить”
Оговорюсь сразу — самый большой недостаток этого подхода в том, что единичные пробелы в начале и конце строк он не удалит. И если для вас они тоже лишние, вам этот вариант не подходит — смотрите варианты ниже.
Тем не менее, на многих сайтах рекомендуется этот способ. Вызвать процедуру замены сочетанием CTRL + F, вбить в поля «Найти» и «Заменить на» 2 пробела и 1 пробел соответственно и произвести замену, нажав кнопку “Заменить все”.
Важно делать это неоднократно — до тех пор, пока процедура не выведет сообщение:
Почему так? Потому что всегда есть вероятность, что между словами было 3 и более пробелов.
Функцией СЖПРОБЕЛЫ
СЖПРОБЕЛЫ — полезная функция, но она требует создания дополнительного столбца, применения функции в нем и далее удаления исходного. Формулы на её основе замечательно удаляют все пробелы, кроме единичных между словами:
- после последнего слова;
- перед первым словом;
- заменяя два и более пробела между словами на один.
С помощью готовой процедуры в !SEMTools
Наверняка многим хотелось бы удалять лишние пробелы без применения функций и создания дополнительных столбцов, меняя строки на их же месте.
Для таких случаев и была создана процедура удаления лишних пробелов в !SEMTools. Выделяем список ячеек, жмем на кнопку в меню. Вуаля!
Удалить пробелы в конце строки
Довольно гипотетическая, но все же возможная ситуация – когда нужно удалить только пробелы в конце строки, а если есть два или более пробелов подряд в середине строки, такие не удалять. Здесь снова на помощь придут регулярные выражения:
=REGEXREPLACE(A1;"s+$";"")
Как видно, регулярное выражение заменяет пробел или несколько пробелов на пустоту, если они идут в конце строки.
Удалить пробелы в начале строки
Аналогично, если задача удалить только пробелы в начале строк, а другие лишние пробелы не трогать. Синтаксис будет чуть другим:
=REGEXREPLACE(A1;"^s+";"")
Напомню, функции регулярных выражений не включены в Excel по умолчанию, но их поддержку можно включить с помощью некоторого кодинга на VBA. Если у вас !SEMTools, и это не потребуется. Использование функций на листе не ограничено и доступно даже в базовой бесплатной версии. Достаточно установить Excel-надстройку.
Скачайте надстройку !SEMTools и удаляйте лишние пробелы и переносы слов без сложных функций.
!SEMTools поможет решить эту и сотни других задач за пару кликов!
|
Zhukov_K Пользователь Сообщений: 711 |
Как можно формулой убрать пробелы в текстовой ячейке только в конце (далее примеры в кавычках — в реальных значениях кавычек нет) нужно убрать формулой пробелы только в конце, т.е. вот так получается: Изменено: Zhukov_K — 16.04.2013 13:02:32 |
|
LVL Пользователь Сообщений: 903 |
|
|
Zhukov_K Пользователь Сообщений: 711 |
когда создавал первое сообщение в этой теме — там после текста было несколько пробелов, а после отсылки сообщения, видать внутренняя система форума их «порезала» |
|
Zhukov_K Пользователь Сообщений: 711 |
СЖПРОБЕЛЫ() сжимает, ну то есть удаляет все пробелы в текстовой ячейке, а все удалять не нужно (по разным причинам), нужно удалить только те пробелы, которые в конце, то есть только конечные пробелы |
|
vikttur Пользователь Сообщений: 47199 |
Функция удаляет не все пробелы. Одинарные в тексте остаются. В конце один лишний пробел или несколько? Во всех ячейках одинаково? |
|
jakim Пользователь Сообщений: 1947 |
Я предлагаю так. Прикрепленные файлы
|
|
Юрий М  Модератор Сообщений: 60585 Контакты см. в профиле |
jakim, кавычки в примере только для форума)) |
|
jakim Пользователь Сообщений: 1947 |
Извините,но я не понял вопроса и не понимаю,как должно быть. |
|
Юрий М Модератор Сообщений: 60585 Контакты см. в профиле |
В ячейках только текст (без кавычек). Требуется убрать пробел после последнего символа. |
|
Zhukov_K Пользователь Сообщений: 711 |
|
|
Микки  Пользователь Сообщений: 3280 |
Почти в тему еще раз всем настоятельно рекомендую БЕСПЛАТНУЮ надстройку Trim Spaces for Excel |
|
LVL Пользователь Сообщений: 903 |
с помощью UDF Прикрепленные файлы
|
|
Супер!!!! Работает! Спасибо! |
|
|
jakim Пользователь Сообщений: 1947 |
|
|
vikttur Пользователь Сообщений: 47199 |
jakim, во второй строке неправильно. Найдите сами ошибку |
|
Zhukov_K Пользователь Сообщений: 711 |
за надстройку спасибо, но по причине того, что обрабатывать нужно значения в облсти сводной (а как известно, она не дает править внутри себя ячейки) я не могу ее использовать |
|
LVL Пользователь Сообщений: 903 |
Обрабатывайте UDF в чем проблема? |
|
GIG_ant  Пользователь Сообщений: 3102 |
Формула массива: |
|
Михаил С. Пользователь Сообщений: 10514 |
#19 16.04.2013 15:28:38
удаляет все пробелы в конце строки. |
||
|
Zhukov_K Пользователь Сообщений: 711 |
GIG_ant, работает как надо, спасибо! ) Михаил С., практически то что надо! единственный «-» в том, что формула оставляет в конце текста один пробел Изменено: Zhukov_K — 16.04.2013 16:59:00 |
|
ZVI  Пользователь Сообщений: 4328 |
#21 16.04.2013 17:11:35 UDF может быть очень простой:
Использование: =S(A2) Изменено: ZVI — 16.04.2013 17:12:44 |
||
|
moestrokir Пользователь Сообщений: 8 |
Zhukov_K
с маленьким дополнением к формуле Михаила, удаляет последний пробел)) =ЛЕВСИМВ(RC[-1];ПОИСК(ПРАВСИМВ(СЖПРОБЕЛЫ(RC[-1]);5);RC[-1])+4) Изменено: moestrokir — 14.08.2015 12:50:52 |
|
fjeka Пользователь Сообщений: 15 |
Как то странно эта формула удаляет пробел |
|
vikttur Пользователь Сообщений: 47199 |
Как просите, так и работает. Зачем весь этот огород. если нужно удалить пробел в конце строки? |
|
fjeka Пользователь Сообщений: 15 |
=ЛЕВСИМВ(I2;7) показывает «бассейн» А каким способом еще удалить пробелы на конце? GIG_ant тоже не работает С этой формулой еще один вопрос. В каком формате она записана в файле Последние_пробелы_ненужны (1).xls ? |
|
vikttur Пользователь Сообщений: 47199 |
#26 06.02.2018 23:51:27
А Вы что хотели получить от функции, которая берет из текста 7 левых символов?
СЖПРОБЕЛЫ удаляет пробелы слева, справа и множественный пробел в тексте превращает в один пробел. Это подходит?
это признак того, что формула введена как формула массива. Поищите по форуму, как вводится формула массива. |
||||||||
|
fjeka Пользователь Сообщений: 15 |
vikttur, Большое спасибо! |
|
Jack Famous  Пользователь Сообщений: 10852 OS: Win 8.1 Корп. x64 | Excel 2016 x64: | Browser: Chrome |
#28 07.02.2018 10:07:07
вам её в первом же ответе предложили Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄ |
||
|
Hadgar  Пользователь Сообщений: 4 |
Добрый день. А можете подсказать формулу, которая удаляет пробелы в конце строки, но при этом не удаляет дублирующиеся пробелы. В 1С номенклатура часто встречается с дублирующимися пробелами и чтобы ее списком сравнить с чем-то еще, мне нужно их сохранять, но при этом удить «, » или просто «,» в конце строки, там по разному бывает. |
|
RAN  Пользователь Сообщений: 7091 |
#30 26.01.2021 13:16:48 А вы можете прочитать тему, и взять нужную формулу? |
|
Удаление пробелов в конце слов в ячейке |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Skip to content
Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов.
В чем самая большая проблема с пробелами? Часто они невидимы для человеческого глаза. Внимательный пользователь может иногда заметить пустой интервал, скрывающийся перед текстом, или несколько ненужных пустот между словами. Но нет никакого способа визуально обнаружить конечные пробелы, те, которые находятся вне поля зрения в конце ячеек.
- Удаляем все пробелы при помощи «Найти и заменить»
- Используем формулу СЖПРОБЕЛЫ
- Удаление начальных и концевых пробелов формулой
- Как убрать разрыв строки и непечатаемые символы
- Формула для удаления неразрывных пробелов
- Как найти и удалить непечатаемый символ
- Как цифры с пробелами преобразовать в число
- Считаем пробелы при помощи формулы
- Простой способ удаления пробелов без формул.
Не было бы большой проблемы, если бы эти лишние интервалы просто существовали, но они могут повлиять на результаты ваших формул. Дело в том, что две ячейки, содержащие один и тот же текст с пробелами и без них, даже если это всего лишь один знак, считаются разными значениями. В результате вы можете ломать голову, пытаясь понять, почему очевидно правильная формула не может соответствовать двум, казалось бы, идентичным записям.
Теперь, когда вы полностью осознаете проблему, пришло время найти решение. Есть несколько способов удалить пробелы из строки, и это руководство поможет вам выбрать метод, наиболее подходящий для вашей конкретной задачи и типа данных, с которым вы работаете.
Как убрать пробелы в Excel при помощи «Найти и заменить»
Это более быстрый метод, который может быть полезен в следующих ситуациях:
- Удаление двойных интервалов.
Обратите внимание, что этот метод не рекомендуется использовать для удаления начальных или конечных пробелов, так как хотя бы один их них там все равно останется.
Итак, найдем и заменим двойные интервалы независимо от их расположения.
Вот как это можно сделать:
- Выделите ячейки, из которых вы хотите их удалить.
- Перейдите на главное меню -> Найти и выбрать -> Заменить.(Также можно использовать сочетание клавиш —
CTRL + H). - В диалоговом окне «Найти и заменить» введите:
- Найти: Двойной пробел.
- Заменить на: Одинарный.
Нажмите «Заменить все».
Обратите внимание, что если у вас есть три интервала между двумя словами, то теперь вы получите два (один будет удален). В таких случаях вы можете повторить эту операцию снова, чтобы удалить любые двойные пробелы, которые ещё могли остаться.
Недостатки этого метода вы видите сами: перед некоторыми из слов остался начальный пробел. Аналогично остались и конечные пробелы после текста, просто они не видны явно. Поэтому для текстовых выражений я не рекомендовал бы применение этого способа.
- Чтобы удалить все пробелы в тексте, выполните следующие действия:
- Выделите нужные ячейки.
- Перейдите в меню Главная -> Найти и выбрать -> Заменить. (Также можно использовать сочетание клавиш —
CTRL + H). - В диалоговом окне «Найти и заменить» введите:
Найти: одинарный пробел.
Заменить на: оставьте это поле пустым.
- Нажмите «Заменить все».
Это удалит все пробелы в выбранном диапазоне.
Вряд ли стоит производить такие манипуляции с текстовыми данными, а вот для чисел вполне подойдет. Интервалы между разрядами часто возникают при импорте данных из других программ через .csv файл. Правда, на рисунке вы видите, что цифры все равно остались записаны в виде текста, но вид их стал более упорядоченным. Как превратить их в настоящие числа — поговорим далее отдельно.
Функция СЖПРОБЕЛЫ.
Если ваш набор данных содержит лишние пробелы, функция СЖПРОБЕЛЫ может помочь вам удалить их все одним махом — ведущие, конечные и несколько промежуточных, оставив только один интервал между словами.
Стандартный синтаксис очень простой:
=СЖПРОБЕЛЫ(A2)
Где A2 — ячейка, из которой вы хотите удалить.
Как показано на следующем скриншоте, СЖПРОБЕЛЫ успешно удалила всё до и после текста, а также лишние интервалы в середине строки.
И теперь вам нужно только заменить значения в исходном столбце новыми. Самый простой способ сделать это — использовать Специальная вставка > Значения.
Формулы для удаления начальных и концевых пробелов.
В некоторых ситуациях вы можете вводить двойные или даже тройные интервалы между словами, чтобы ваши данные были более удобочитаемыми. Однако вам нужно избавиться от ведущих пробелов (находящихся в начале), например:
Как вы уже знаете, функция СЖПРОБЕЛЫ удаляет лишние интервалы в середине текстовых строк, чего мы в данном случае не хотим. Чтобы сохранить их нетронутыми, мы будем использовать немного более сложную конструкцию:
=ПСТР(A2;НАЙТИ(ПСТР(СЖПРОБЕЛЫ(A2);1;1);A2);ДЛСТР(A2))
Это выражение в начале вычисляет позицию первого знака в строке. Затем вы передаете это число другой функции ПСТР, чтобы она возвращала всю текстовую строку (длина строки рассчитывается с помощью ДЛСТР), начиная с позиции первого знака.
Вы видите, что все ведущие пробелы исчезли, хотя несколько интервалов между словами всё же остались.
В качестве последнего штриха замените исходный текст полученными значениями, как это было описано выше.
Если же нужно удалить только пробелы в конце каждой ячейки, то формула будет немного сложнее:
=ЛЕВСИМВ(A2;МАКС((ПСТР(A2&ПОВТОР(» «;99);СТРОКА(A2:A100);1)<>» «)*СТРОКА(A2:A100)))
И обратите снимание, что ее нужно вводить как формулу массива (с Ctrl+Shift+Enter). В столбце A выровнять по правому краю получилось плохо из-за разного количества концевых пробелов в каждой ячейке. В столбце B эта проблема решена, и можно красиво расположить текст.
Как удалить разрывы строк и непечатаемые символы
При импорте данных из внешних источников появляются не только лишние пробелы, но и различные непечатаемые символы, такие как возврат каретки, перевод строки, вертикальная или горизонтальная табуляция и т.д.
Функция СЖПРОБЕЛЫ может избавиться от пробелов, но не может устранить непечатаемые символы. Технически она предназначена для удаления только значения 32 в 7-битной системе ASCII , которое как раз и является кодом пробела.
Чтобы удалить ещё и непечатаемые символы в строке, используйте её в сочетании с функцией ПЕЧСИМВ (CLEAN в английской версии). Как следует из названия, ПЕЧСИМВ предназначена для очистки данных от ненужного «мусора» и умеет удалять любой из первых 32 непечатаемых символов в 7-битном наборе ASCII (значения от 0 до 31), включая разрыв строки (значение 10).
Предполагая, что очищаемые данные находятся в ячейке A2, формула будет следующей:
=СЖПРОБЕЛЫ(ПЕЧСИМВ(A2))
Практически всегда, когда при помощи приведенного выше выражения вы удаляете разрывы строки, то отдельные слова оказываются «склеенными» друг с другом. Вы можете исправить это, используя один из следующих способов:
- Воспользуйтесь инструментом Excel «Заменить все»: в поле «Найти» введите возврат каретки, нажав сочетание клавиш
Ctrl+J. И в поле «Заменить» введите пробел. При нажатии кнопки «Заменить все» все разрывы строк в выбранном диапазоне заменяются пробелами. - Используйте следующую формулу для замены возврата каретки (код 13) и перевода строки (код 10) на пробелы:
=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2; СИМВОЛ(13);» «); СИМВОЛ(10); » «))
Как видите, почтовый адрес в колонке С выглядит вполне читаемо.
Как убрать неразрывные пробелы в Excel?
Если после использования формулы СЖПРОБЕЛЫ и ПЕЧСИМВ некоторые упрямые знаки все еще остаются, то скорее всего, вы скопировали / вставили данные из интернета или из другой программы, и несколько неразрывных пробелов все же прокрались в вашу таблицу.
Чтобы избавиться от неразрывных пробелов (html-код ), замените их обычными, а затем попросите функцию СЖПРОБЕЛЫ удалить их:
=СЖПРОБЕЛЫ((ПОДСТАВИТЬ(A2;СИМВОЛ(160);» «)))
Чтобы лучше понять логику, давайте разберем формулу:
- Неразрывный пробел имеет код 160 в 7-битной системе ASCII, поэтому вы можете определить его с помощью СИМВОЛ(160).
- Функция ПОДСТАВИТЬ используется для превращения неразрывных пробелов в обычные.
- И, наконец, вы вставляете ПОДСТАВИТЬ в СЖПРОБЕЛЫ, чтобы окончательно удалить всё лишнее.
Если ваш рабочий лист также содержит непечатаемые символы, дополнительно к описанному выше используйте ещё функцию ПЕЧСИМВ, чтобы избавиться от пробелов и других ненужных знаков одним махом:
=СЖПРОБЕЛЫ(ПЕЧСИМВ((ПОДСТАВИТЬ(A2;СИМВОЛ(160);» «))))
Следующий скриншот демонстрирует разницу в результатах:
Как удалить определенный непечатаемый символ
Если взаимодействие трех функций, описанных в приведенном выше примере, не смогло устранить весь мусор из текста, то это означает, что оставшиеся знаки имеют значения ASCII, отличные от 0 до 32 (непечатаемые символы) или 160 (неразрывный пробел).
В этом случае используйте функцию КОДСИМВ, чтобы сначала идентифицировать код мешающего вам знака, а затем используйте ПОДСТАВИТЬ, чтобы заменить его обычным пробелом. А затем при помощи СЖПРОБЕЛЫ удалите его.
Предполагая, что нежелательные символы, от которых вы хотите избавиться, находятся в ячейке A2, вы пишете два выражения:
- В ячейке С2 определите код проблемного знака, используя одну из следующих функций:
- Ведущий пробел или непечатаемый символ в начале строки:
=КОДСИМВ(ЛЕВСИМВ(A2;1))
- Конечный пробел или непечатаемый символ в конце строки:
= КОДСИМВ(ПРАВСИМВ(A2;1))
- Пробел или непечатаемый символ в середине строки, где n — позиция проблемного знака:
= КОДСИМВ(ПСТР(A2, n, 1)))
В этом примере у нас есть неизвестный знак в середине текста, в 11-й позиции, и мы определим его код:
=КОДСИМВ(ПСТР(A2;11;1))
Получаем значение 127 (см. скриншот ниже).
- В ячейке C3 вы заменяете СИМВОЛ(127) обычным пробелом (» «), а затем просто удаляете его:
=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;СИМВОЛ(127); » «))
Если ваши данные содержат несколько разных непечатаемых символов, а также неразрывные пробелы, вы можете вложить две или более функции ПОДСТАВИТЬ друг в друга для одновременного удаления всех нежелательных знаков:
=СЖПРОБЕЛЫ(ПЕЧСИМВ((ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;СИМВОЛ(127);» «);СИМВОЛ(160);» «))))
Результат должен выглядеть примерно так:
Эту универсальную формулу мы и использовали. Как видите, успешно.
Как удалить все пробелы
В некоторых ситуациях может потребоваться удалить абсолютно все пробелы в ячейке, включая те, что находятся между словами или числами. Например, если вы импортировали в вашу таблицу числовой столбец, в котором пробелы используются в качестве разделителей тысяч. Конечно, разделители разрядов упрощают чтение больших чисел, но они же препятствуют вычислению ваших формул. Такие разделители нужно создавать при помощи форматирования, а не вручную.
Чтобы удалить все пробелы одним махом, используйте ПОДСТАВИТЬ, как описано в предыдущем примере, с той лишь разницей, что вы заменяете знак пробела, возвращаемый при помощи СИМВОЛ(32), ничем («»):
=ПОДСТАВИТЬ(A2; СИМВОЛ(32); «»)
Или вы можете просто ввести его (» «) в формуле, например:
=ПОДСТАВИТЬ(A2; “ “; «»)
Результатом этого будет текст, состоящий из цифр. Если же в качестве результата вам нужны именно числа, добавьте перед формулой два знака “-“ (минус). Любая математическая операция автоматически превращает цифры в число. А дважды применив минус, то есть дважды умножив на минус 1, мы не изменим величину числа и его знак.
Как посчитать пробелы в Excel
Чтобы получить общее количество пробелов в ячейке, выполните следующие действия:
- Вычислите всю длину строки с помощью функции ДЛСТР: ДЛСТР (A2)
- Замените все пробелы ничем: ПОДСТАВИТЬ(A2; » «; «»)
- Вычислить длину строки без пробелов: ДЛСТР(ПОДСТАВИТЬ(A2; » «; «»))
- Вычтите этот результат из первоначальной длины.
Предполагая, что исходная текстовая строка находится в ячейке A3, полная формула выглядит следующим образом:
=ДЛСТР(A3) — ДЛСТР(ПОДСТАВИТЬ(A3;» «;»»))
Чтобы узнать, сколько лишних пробелов в ячейке, получите длину текста без них, а затем вычтите ее из первоначальной длины:
=ДЛСТР(A3)-ДЛСТР(СЖПРОБЕЛЫ(A3))
На рисунке показаны обе формулы в действии:
Теперь, когда вы знаете, сколько пробелов содержит каждая ячейка, вы можете безопасно удалить лишние, используя функцию СЖПРОБЕЛЫ.
Простой способ удаления пробелов без формул.
Как вы уже знаете, лишние пробелы и другие нежелательные символы могут незаметно скрываться на ваших листах, особенно если вы импортируете данные из внешних источников. Вы также знаете, как удалять пробелы в Excel с помощью формул. Конечно, изучение нескольких формул – хорошее упражнение для оттачивания ваших навыков, но это может занять много времени.
Пользователи Excel, которые ценят свое время и ценят удобство, могут воспользоваться текстовыми инструментами, включенными в надстройку Ultimate Suite for Excel . Один из этих удобных инструментов позволяет удалять пробелы и непечатаемые символы одним нажатием кнопки.
После установки Ultimate Suite добавляет на ленту Excel несколько полезных кнопок, таких как «Удалить пробелы» , «Удалить символы» , «Преобразовать текст» и многое другое.
Всякий раз, когда вы хотите удалить лишние пробелы в таблицах Excel, выполните следующие 4 быстрых шага:
- Выделите ячейки (диапазон, весь столбец или строку), в которых вы хотите удалить лишние пробелы.
- Нажмите кнопку «Trim Spaces» на вкладке «Ablebits Data».
- Выберите один или несколько вариантов:
- Обрезать начальные и конечные пробелы.
- Удалить лишние пробелы между словами, кроме одного.
- Удалить неразрывные пробелы ( )
- Удалить лишние переносы строк до и после значений, между значениями оставить только один.
- Удалить все переносы строк в ячейке.
- Нажмите кнопку «Trim» .
Готово! Все лишние пробелы удалены одним щелчком мыши:
Здесь мы вместе с лишними пробелами удалили еще и переводы строки, чтобы значения располагались в привычном виде, в одну строку.
Вот как можно быстро удалить пробелы в ячейках Excel. Если вам интересно изучить другие возможности работы с текстовыми значениями (и не только), вы можете загрузить
ознакомительную версию Ultimate Suite. Благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!
Формула ЗАМЕНИТЬ и ПОДСТАВИТЬ для текста и чисел — В статье объясняется на примерах как работают функции Excel ЗАМЕНИТЬ (REPLACE в английской версии) и ПОДСТАВИТЬ (SUBSTITUTE по-английски). Мы покажем, как использовать функцию ЗАМЕНИТЬ с текстом, числами и датами, а также… 
Как убрать пробелы в числах в Excel — Представляем 4 быстрых способа удалить лишние пробелы между цифрами в ячейках Excel. Вы можете использовать формулы, инструмент «Найти и заменить» или попробовать изменить формат ячейки. Когда вы вставляете данные из… 
Функция СЖПРОБЕЛЫ — как пользоваться и примеры — Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии) не работает и как… 
Функция ПРАВСИМВ в Excel — примеры и советы. — В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части… 
Функция ЛЕВСИМВ в Excel. Примеры использования и советы. — В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди… 
5 примеров с функцией ДЛСТР в Excel. — Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)… 
Как быстро сосчитать количество символов в ячейке Excel — В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…
Содержание
- Технология удаления пробелов
- Способ 1: использование инструмента «Заменить»
- Способ 2: удаление пробелов между разрядами
- Способ 3: удаление разделителей между разрядами путем форматирования
- Способ 4: удаление пробелов с помощью функции
- Вопросы и ответы
Лишние пробелы в тексте не красят любой документ. Особенно их не нужно допускать в таблицах, которые предоставляются руководству или общественности. Но даже, если вы собираетесь использовать данные только в личных целях, лишние пробелы способствуют увеличению объема документа, что является негативным фактором. Кроме того, наличие подобных лишних элементов затрудняет поиск по файлу, применение фильтров, использование сортировки и некоторых других инструментов. Давайте выясним, какими способами их можно быстро найти и убрать.
Урок: Удаление больших пробелов в Microsoft Word
Технология удаления пробелов
Сразу нужно сказать, что пробелы в Excel могут быть разного типа. Это могут быть пробелы между словами, пробел в начале значения и в конце, разделители между разрядами числовых выражений и т.д. Соответственно и алгоритм их устранения в этих случаях различный.
Способ 1: использование инструмента «Заменить»
С заменой двойных пробелов между словами на одинарные в Экселе отлично справляется инструмент «Заменить».
- Находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая размещена в блоке инструментов «Редактирование» на ленте. В выпадающем списке выбираем пункт «Заменить». Можно также вместо вышеперечисленных действий просто набрать сочетание клавиш на клавиатуре Ctrl+H.
- В любом из вариантов открывается окно «Найти и заменить» во вкладке «Заменить». В поле «Найти» устанавливаем курсор и дважды кликаем по кнопке «Пробел» на клавиатуре. В поле «Заменить на» вставляем один пробел. Затем жмем по кнопке «Заменить все».
- Программа производит замену двойного пробела на одинарный. После этого появляется окно с отчетом о проделанной работе. Жмем на кнопку «OK».
- Далее опять появляется окно «Найти и заменить». Выполняем в этом окне точно такие же действия, как описаны во втором пункте данной инструкции до тех пор, пока не появится сообщение о том, что искомые данные не найдены.
Таким образом, мы избавились от лишних двойных пробелов между словами в документе.
Урок: Замена символов в Excel
Способ 2: удаление пробелов между разрядами
В некоторых случаях между разрядами в числах устанавливаются пробелы. Это не ошибка, просто для визуального восприятия больших чисел именно такой вид написания более удобен. Но, все-таки далеко не всегда это приемлемо. Например, если ячейка не отформатирована под числовой формат, добавление разделителя может негативно отразиться на корректности вычислений в формулах. Поэтому актуальным становится вопрос об удалении таких разделителей. Эту задачу можно выполнить при помощи все того же инструмента «Найти и заменить».
- Выделяем столбец или диапазон, в котором нужно удалить разделители между цифрами. Этот момент очень важен, так как если диапазон не будет выделен, то инструмент уберет все пробелы из документа, в том числе и между словами, то есть, там, где они действительно нужны. Далее, как и ранее, кликаем по кнопке «Найти и выделить» в блоке инструментов «Редактирование» на ленте во вкладке «Главная». В добавочном меню выбираем пункт «Заменить».
- Снова запускается окно «Найти и заменить» во вкладке «Заменить». Но на этот раз мы будем вносить в поля немного другие значения. В поле «Найти» устанавливаем один пробел, а поле «Заменить на» оставляем вообще незаполненным. Чтобы удостовериться на предмет того, что в этом поле нет пробелов, устанавливаем курсор в него и зажимаем кнопку backspace (в виде стрелки) на клавиатуре. Держим кнопку до тех пор, пока курсор не упрется в левую границу поля. После этого жмем на кнопку «Заменить все».
- Программа выполнит операцию удаления пробелов между цифрами. Как и в предыдущем способе, чтобы удостоверится, что задача полностью выполнена, производим повторный поиск, пока не появится сообщение, что искомое значение не найдено.
Разделения между разрядами будут удалены, а формулы начнут вычисляться корректно.
Способ 3: удаление разделителей между разрядами путем форматирования
Но бывают ситуации, когда вы четко видите, что на листе разряды разделены в числах пробелами, а поиск не дает результатов. Это говорит о том, что в данном случае разделение было выполнено путем форматирования. Такой вариант пробела не повлияет на корректность отображения формул, но в то же время некоторые пользователи считают, что без него таблица будет выглядеть лучше. Давайте рассмотрим, как убрать такой вариант разделения.
Так как пробелы были сделаны с помощью инструментов форматирования, то только при помощи этих же инструментов их можно убрать.
- Выделяем диапазон чисел с разделителями. Кликаем по выделению правой кнопкой мыши. В появившемся меню выбираем пункт «Формат ячеек…».
- Происходит запуск окна форматирования. Переходим во вкладку «Число», в случае если открытие произошло в другом месте. Если разделение было задано при помощи форматирования, то в блоке параметров «Числовые форматы» должен быть установлен вариант «Числовой». В правой части окна располагаются точные настройки данного формата. Около пункта «Разделитель групп рядов ()» вам просто нужно будет снять галочку. Затем, чтобы произведенные изменения вступили в силу, жмите на кнопку «OK».
- Окно форматирования закроется, а разделения между разрядами чисел в выбранном диапазоне будут убраны.
Урок: Форматирование таблиц в Excel
Способ 4: удаление пробелов с помощью функции
Инструмент «Найти и заменить» отлично подходит для удаления лишних пробелов между символами. Но что делать, если их нужно убрать в начале или в конце выражения? В этом случае на помощь придет функция из текстовой группы операторов СЖПРОБЕЛЫ.
Данная функция удаляет все пробелы из текста выделенного диапазона, кроме одиночных пробелов между словами. То есть, она способна решить проблему с пробелами в начале слова в ячейке, в конце слова, а также удалить двойные пробелы.
Синтаксис этого оператора довольно прост и имеет всего один аргумент:
=СЖПРОБЕЛЫ(текст)
В качестве аргумента «Текст» может выступать, как непосредственно текстовое выражение, так и ссылка на ячейку в котором оно содержится. Для нашего случая как раз последний вариант и будет рассматриваться.
- Выделяем ячейку, расположенную параллельно столбцу или строке, где следует убрать пробелы. Кликаем по кнопке «Вставить функцию», расположенную слева от строки формул.
- Запускается окно Мастера функций. В категории «Полный алфавитный перечень» или «Текстовые» ищем элемент «СЖПРОБЕЛЫ». Выделяем его и жмем на кнопку «OK».
- Открывается окошко аргументов функции. К сожалению, данная функция не предусматривает использования в качестве аргумента всего нужного нам диапазона. Поэтому устанавливаем курсор в поле аргумента, а затем выделяем самую первую ячейку диапазона, с которым мы работаем. После того, как адрес ячейки отобразился в поле, жмем на кнопку «OK».
- Как видим, содержимое ячейки отобразилось в области, в которой находится функция, но уже без лишних пробелов. Мы выполнили удаление пробелов только для одного элемента диапазона. Чтобы удалить их и в других ячейках, нужно провести аналогичные действия и с другими ячейками. Конечно, можно проводить с каждой ячейкой отдельную операцию, но это может отнять очень много времени, особенно, если диапазон большой. Существует способ значительно ускорить процесс. Устанавливаем курсор в нижний правый угол ячейки, в которой уже содержится формула. Курсор трансформируется в небольшой крестик. Он носит название маркера заполнения. Зажимаем левую кнопку мыши и тянем маркер заполнения параллельно тому диапазону, в котором нужно убрать пробелы.
- Как видим, после этих действий образуется новый заполненный диапазон, в котором находится все содержимое исходной области, но без лишних пробелов. Теперь перед нами стоит задача заменить преобразованными данными значения исходного диапазона. Если мы выполним простое копирование, то скопирована будет формула, а значит, вставка произойдет некорректно. Поэтому нам нужно произвести только копирование значений.
Выделяем диапазон с преобразованными значениями. Жмем на кнопку «Копировать», расположенную на ленте во вкладке «Главная» в группе инструментов «Буфер обмена». Как альтернативный вариант можно после выделения набрать сочетание клавиш Ctrl+C.
- Выделяем исходный диапазон данных. Кликаем по выделению правой кнопкой мыши. В контекстном меню в блоке «Параметры вставки» выбираем пункт «Значения». Он изображен в виде квадратной пиктограммы с цифрами внутри.
- Как видим, после вышеописанных действий значения с лишними пробелами были заменены идентичными данными без них. То есть, поставленная задача выполнена. Теперь можно удалить транзитную область, которая была использована для трансформации. Выделяем диапазон ячеек, в которых содержится формула СЖПРОБЕЛЫ. Кликаем по нему правой кнопкой мыши. В активировавшемся меню выбираем пункт «Очистить содержимое».
- После этого лишние данные будут убраны с листа. Если в таблице существуют другие диапазоны, которые содержат лишние пробелы, то с ними нужно поступить по точно такому же алгоритму, какой был описан выше.
Урок: Мастер функций в Excel
Урок: Как сделать автозаполнение в Excel
Как видим, существует целый ряд способов быстрого удаления лишних пробелов в Экселе. Но все эти варианты реализуются при помощи всего двух инструментов – окна «Найти и заменить» и оператора СЖПРОБЕЛЫ. В отдельном случае можно использовать также форматирование. Не существует универсального способа, который было бы максимально удобно использовать во всех ситуациях. В одном случае будет оптимальным использовать один вариант, а во втором – другой и т.д. Например, с удалением двойного пробела между словами быстрее всего справится инструмент «Найти и заменить», а вот убрать пробелы в начале и в конце ячейки корректно сможет только функция СЖПРОБЕЛЫ. Поэтому о применении конкретного способа пользователь должен принимать решение самостоятельно с учетом ситуации.
Всем привет! В сегодняшнем нашем уроке мы посмотрим, как можно удалить лишние пробелы в ячейках Excel. С данной проблемjq сталкиваются почти все, и пробелы действительно сильно мешают в дальнейшей работе. Во-первых, при сортировке или при поиске информации частенько мы не может найти необходимую информацию. Особенно это болезненно при работе с большими документами. Во-вторых, документ при этом смотрится некрасиво и визуально сложно с ним работать. В-третьих, документ таким образом весит больше. Особенно это заметно при копировании большого количества элементов.
Именно поэтому нужно сразу же убрать лишние пробелы в ячейках Excel, как только они начали появляться. Нужно понимать, что пробелы могут быть разного типа – между слов, в начале или в конце. Еще более серьезные пробелы можно увидеть между цифрами или разрядами числа. В таком случае вы можете столкнуться с проблемами при арифметических подсчетах. И ладно, если будет вылезать ошибка, а что, если подсчеты будут неверные. В статье мы рассмотрим несколько способов. Поехали!
Содержание
- Способ 1: Замена
- Способ 2: Удаление пробела между цифрами
- Способ 3: Использование функции
- Задать вопрос автору статьи
Способ 1: Замена
- Перейдите на вкладку «Главная».
- Теперь в правом верхнем углу окна в разделе «Редактирования» нажмите по значку лупы – далее из выпадающего меню выбираем инструмент «Заменить».
- В первой строке ставим текстовый курсор, нажав левую кнопку мыши и два раза жмем на «Пробел». Как вы уже догадались в нижней строке нужно поставить только один «Пробел». Теперь жмем по кнопке «Заменить все».
- Если в документе будут найдены такие элементы, то они будут заменены на обычный пробел. Если же их обнаружено не будет, то вы увидите соответствующее сообщение.
Таким же образом можно заменять любые символы, например, звездочку (*) на пробел или тире (-). Очень полезная функция, которая есть во всех программах, работающих с текстом, символами и цифрами. Она также есть и в Word.
Способ 2: Удаление пробела между цифрами
Давайте рассмотрим еще одну очень популярную ситуацию, которая чаще всего возникает при копировании данных из другого места. Представим себе, что у нас есть большое количество чисел, где каждые три разряда разделены пробелом. Для человека подобные цифры воспринимаются удобно, но вот некоторые версии Excel не могут работать с подобными цифрами и считают такие ячейки с наличием символов. Давайте посмотрим, как можно удалить и полностью убрать пробелы в числах в Excel.
ПРИМЕЧАНИЕ! Новые версии Excel спокойно работают с такими цифрами, поэтому перед удалением пробелов проверьте – работают ли формулы вычисления с такими ячейками. Если они работают, то удалять их не нужно.
- Выделяем те ячейки, в которых находятся подобные цифры.
- Открываем тот же самый инструмент.
- Теперь в первую строку вводим только один пробел, а вот со второй нужно все стереть с помощью кнопки «Backspace».
- После замены пробелы должны удалиться.
Если вы видите сообщение – «Мы не нашли ничего, что нужно было заменить» – значит данные пропуски не воспринимаются программой как пробелы и просто визуально разграничивают числа для удобства. С ними можно спокойно работать при любом вычислении.
Если же они вам не нравятся, то их можно убрать с помощью настроек отображения.
- Выделяем эти ячейки.
- Жмем правой кнопкой и заходим в «Формат ячейки».
- В настройках в «Числовом» разделе просто уберите галочку «Разделитель групп разрядов».
- В конце нажмите «ОК».
Оба предыдущих способа не дают нам возможность, например, удалить один пробел в начале или в конце строки. Конечно, он может мешать не всем, но при добавлении такого пробела в число может возникнуть проблема с подсчетом, а также автоматическом определении формата ячейки. Поэтому лучше всего воспользоваться функцией:
=СЖПРОБЕЛЫ(***)
Дополнительно она удаляет двойные или тройные пробелы из всех ячеек. Вместо звездочек (***) мы будем использовать или просто текст, или адрес ячейки.
- Выделите ячейку, параллельную той, которую вы хотите исправить.
- Нажмите по кнопке «Функции» рядом со строкой значения.
- Выберите «Текстовую» категорию и установите нужную нам функцию.
- Жмем «ОК».
- Теперь нам нужно установить адрес – выделите адрес ячейки.
- Теперь растяните формулу на другие ячейки.
- Теперь нам нужно скопировать (Ctrl + C) все исправленные значения в старые ячейки (Ctrl + V). При вставке выберите режим «Значения».
- Эти данные можно удалить – кликаем правой кнопкой по выделению и выбираем команду «Очистить содержимое».
Способ не такой удобный, но зато помогает избавиться от лишних пропусков в начале и конце строки. На этом все, дорогие друзья портала WiFiGiD.RU. Надеюсь, урок вам понравился и был для вас полезным. Пишите свои дополнения и вопросы в комментариях. Всем добра!