Excel удалить пробелы в конце ячейки
Как убрать лишние пробелы в Excel?
Рассмотрим способы удаления пробелов в Excel (например, между словами, в начале или конце предложений, в числах).
Приветствую всех, уважаемые читатели блога TutorExcel.Ru.
Зачастую при копировании текста или переносе таблиц возникают проблемы с наличием лишних символов, которые могут появиться по самым разным причинам: неправильный ввод данных, импортирование данных из внешних источников и т.д.
Достаточно часто лишние пробелы в Excel встречаются в тексте в следующих случаях:
- В начале или в конце предложений;
- Дублирование пробелов;
- Невидимые символы, в т.ч. неразрывный пробел;
- В числах (в разделителях разрядов).
Помимо того, что данные могут стать трудночитаемыми, так и использование обычных инструментов Excel (к примеру, сортировка, фильтрация, поиск, применение функций и т.д.) становится затруднительным и некорректным.
Поэтому, чтобы при работе избежать подобных проблем, для корректной работы необходимо удалять лишние символы.
Как удалить пробелы в Excel?
Рассмотрим 2 основных способа, как можно удалить лишние символы:
- Инструмент «Найти и заменить». Удаление пробелов через диалоговое окно;
- С помощью функций. Формульный вариант удаления.
Инструмент «Найти и заменить»
Предположим, что у нас есть текст или таблица со словами, в котором присутствует дублирование пробелов.
Для начала выделяем диапазон ячеек с данными и выбираем на панели вкладок Главная -> Редактирование -> Найти и выделить -> Заменить либо просто нажимаем сочетание клавиш Ctrl + H:
Появляется стандартное диалоговое окно замены, затем вводим в поле Найти два пробела, а в Заменить на — один. Нажимаем Заменить все и повторяем операцию до тех пор, пока Excel находит повторения.
Как видим алгоритм достаточно простой, однако данный способ удаления пробелов не срабатывает, если они присутствуют в конце или в начале текста.
С помощью функций
В этом случае можно воспользоваться стандартной функцией СЖПРОБЕЛЫ, которая удаляет все пробелы, за исключением одиночных между словами.
Предположим, что у нас имеется список фамилий (столбец A) содержащий лишние пробелы как между словами, так и в начале или конце фамилии. Давайте создадим вспомогательный столбец (столбец B), куда введем формулу СЖПРОБЕЛЫ ссылающуюся на столбец с фамилиями:
Далее копируем содержимое столбца B и вставляем значения без связей в столбец A, после чего удаляем вспомогательный столбец.
Минусом данного способа является его неудобство при работе с большими объемами данных, так как для каждой редактируемой ячейки необходимо создавать дополнительное место где будет обрабатываться исходная ячейка.
Неразрывный пробел в Excel
В случае если после совершенных действий лишние пробелы все же не получилось удалить, то вероятно в тексте могут содержаться неразрывные пробелы.
Данный символ внешне выглядит как обычный пробел, однако его нельзя удалить с помощью функции СЖПРОБЕЛЫ или инструмента «Найти и Заменить» (при вводе в поле для замены обычного пробела).
Как видим в ячейке A2 содержатся лишние пробелы, но функция СЖПРОБЕЛЫ их не удаляет.
Каждый символ в Excel имеет свою кодировку в таблице знаков ANSI (для ОС Windows), в том числе и интересные нам одиночный пробел (символ 32) и неразрывный (символ 160).
Поэтому с помощью функции ПОДСТАВИТЬ заменяем неразрывный пробел (символ 160) на обычный (символ 32), а затем к полученному результату применяя функцию СЖПРОБЕЛЫ получаем требуемый результат:
На самом деле убрать неразрывный пробел можно и с помощью «Найти и заменить», для этого в поле Найти необходимо ввести неразрывный пробел и заменить его либо на обычный, либо удалить, оставив поле Заменить на пустым.
Возникает вопрос, как это сделать?
Первый способ — непосредственно скопировать данный символ из текста и ввести в поле Найти.
Второй способ — воспользоваться Alt-кодом (для систем Windows) и ввести комбинацию Alt + 0160 (удерживая клавишу Alt нужно набрать 0160 на цифровой клавиатуре).
Как убрать пробелы в числах в Excel?
Проблема с удалением пробелов встречается и при работе с числами — в числах попадаются пробелы (к примеру, вместо разделителей разрядов), из-за чего они воспринимаются в Excel как текст.
Предположим, что у нас имеется таблица с числами, где содержатся пробелы. Выделяем диапазон с данными и с помощью инструмента «Найти и Заменить» удаляем пробелы, подставив вместо одиночного пробела пустое поле, как показано в примере выше.
Аналогичный результат можно получить и с помощью формулы: 
При этом не забудьте умножить результат на 1, чтобы перевести формулу из текста в число.
Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — делитесь ими в комментариях.
Источник: tutorexcel.ru
2 способа удалить пробелы между словами или числами в ячейках Excel
Из этой статьи Вы узнаете 2 быстрых способа удалить лишние пробелы между словами или все пробелы из ячеек Excel. Вы можете использовать функцию TRIM (СЖПРОБЕЛЫ) или инструмент Find & Replace (Найти и заменить), чтобы вычистить содержимое ячеек в Excel.
Когда Вы вставляете данные из внешнего источника на лист Excel (простой текст, числа и т.д.), то можете получить лишние пробелы вместе с важными данными. Это могут быть начальные и конечные пробелы, несколько пробелов между словами или разделители разрядов в числах.
Следовательно, таблица выглядит малость неопрятно и становится трудной для использования. Казалось бы, простая задача может стать непростой. Например, найти покупателя с именем John Doe (нет лишних пробелов между частями имени), в то время как в таблице он сохранён как “John Doe“. Или числа, которые не могут быть просуммированы, а виноваты в этом опять лишние пробелы.
Из этой статьи Вы узнаете, как очистить данные от лишних пробелов:
Удаляем все лишние пробелы между словами, отсекаем начальные и конечные пробелы
Предположим, есть таблица с двумя столбцами. В столбце Name в первой ячейке содержится имя John Doe, записанное корректно, т.е. без лишних пробелов. Все остальные ячейки содержат вариант записи с лишними пробелами между именем и фамилией, а также в начале и в конце (начальные и конечные пробелы). Во втором столбце, с названием Length, показано количество символов в каждом имени.
Используем функцию «СЖПРОБЕЛЫ» для удаления лишних пробелов
В Excel существует функция TRIM (СЖПРОБЕЛЫ), которая используется для удаления лишних пробелов из текста. Далее Вы найдёте пошаговую инструкцию по работе с этим инструментом:
- Рядом со своими данными добавьте вспомогательный столбец. Можете назвать его Trim.
- В первой ячейке вспомогательного столбца (C2) введите формулу для удаления лишних пробелов:
Готово! Мы удалили все лишние пробелы при помощи функции TRIM (СЖПРОБЕЛЫ). К сожалению, этот способ занимает много времени, особенно, когда таблица достаточно велика.
Замечание: Если после применения формулы Вы по-прежнему видите лишние пробелы, скорее всего в тексте содержатся неразрывные пробелы. Как их удалить, Вы можете узнать из этого примера.
Применяем инструмент «Найти и заменить», чтобы удалить лишние пробелы между словами
Этот вариант требует меньше действий, но позволяет удалить только лишние пробелы между словами. Начальные и конечные пробелы будут также отсечены до 1, но не удалены полностью.
- Выделите один или несколько столбцов с данными, в которых нужно удалить лишние пробелы между словами.
- Нажмите Ctrl+H, чтобы открылось диалоговое окно Find & Replace (Найти и заменить).
- Введите два раза пробел в поле Find What (Найти) и один раз в поле Replace With (Заменить на).
- Нажмите кнопку Replace all (Заменить все), а затем ОК, чтобы закрыть появившееся информационное окно.
- Повторяйте шаг 4 до тех пор, пока не появится сообщение We couldn’t find anything to replace… (Мы не нашли ничего, что нужно было заменить…).
Удаляем все пробелы между числами
Предположим, у Вас есть таблица с числами, в которых группы разрядов (тысячи, миллионы, миллиарды) разделены пробелами. В этом случае Excel рассматривает числа как текст и никакая математическая операция выполнена быть не может.
Простейший способ избавиться от лишних пробелов – это воспользоваться стандартным инструментом Excel – Find & Replace (Найти и заменить).
- Нажмите Ctrl+Space (Пробел), чтобы выделить все ячейки в столбце.
- Нажмите Ctrl+H, чтобы открыть диалоговое окно Find & Replace (Найти и заменить).
- В поле Find What (Найти) введите один пробел. Убедитесь, что поле Replace With (Заменить на) – пустое.
- Нажмите кнопку Replace all (Заменить все), затем ОК. Вуаля! Все пробелы удалены.
Удаляем все пробелы при помощи формулы
Возможно, Вам пригодится способ удалить все пробелы при помощи формулы. Для этого Вы можете создать вспомогательный столбец и ввести такую формулу:
Здесь A1 – это первая ячейка столбца с числами или словами, в которой должны быть удалены все пробелы.
Источник: office-guru.ru
Как удалить пробелы в Excel
Как удалить пробелы в Excel
Добрый день, уважаемые читатели блога и его подписчики! Темой сегодняшнего поста будет, на первый взгляд, проста я и незамысловатая вещь — удаление пробелов в таблицах Excel.
Сразу скажу, что способов несколько, мы рассмотрим наиболее простые и понятные.
Способ №1 (подходит для пробелов и специальных символов)
Воспользуемся стандартным способом замены:
- на вкладке «Главная», в блоке кнопок «Редактирование» нажмём «Найти и выделить»;
- выберем вариант «Заменить».
Появится следующее окно.
Теперь самое трудное. Необходимо скопировать символ пробела (как бы нелепо это ни звучало). В первой ячейке таблицы я специально поставил везде два пробела. Их и нужно скопировать. Лучше воспользоваться сочетанием клавиш Ctrl + C.
Вставляем скопированные символы в поле замены, в поле «Заменить на:» поставим просто пробел, чтобы избежать удаления всех проблем, нажимаем кнопку «Заменить все».
Поскольку был выбран вариант «Заменить все» программа сжала двойной пробел до стандарта — одного.
Повторюсь, способ можно использовать и для специальных символов (табуляции, абзаца и т.д.).
Способ № 2
Программа Excel для таких случаев содержит специальную формулу =СЖПРОБЕЛЫ().
В отдельной ячейке вне зависимости от количества пробелов их значения будут приведены к стандарту. В ячейке два раза повторяется тройной пробел, формула устранит эту проблему.
Способ № 3
Воспользуемся уже известной нам формулой =ПОДСТАВИТЬ().
Главное помнить о том, что в качестве «Старого_текста» нужно в кавычках указать количество искомых пробелов. Вместо «Нового_текста» просто поставить кавычки, чтобы количество пробелов сократилось до одного. «Номер_вхождения» оставить пустым, чтобы обработались все вхождения «старого текста».
Если знаете ещё какой-либо способ — отпишите в комментарии! Всем удачи!
Источник: pcandlife.ru
2 способа удалить пробелы между словами или числами в ячейках Excel
Из этой статьи Вы узнаете 2 быстрых способа удалить лишние пробелы между словами или все пробелы из ячеек Excel. Вы можете использовать функцию TRIM (СЖПРОБЕЛЫ) или инструмент Find & Replace (Найти и заменить), чтобы вычистить содержимое ячеек в Excel.
Когда Вы вставляете данные из внешнего источника на лист Excel (простой текст, числа и т.д.), то можете получить лишние пробелы вместе с важными данными. Это могут быть начальные и конечные пробелы, несколько пробелов между словами или разделители разрядов в числах.
Следовательно, таблица выглядит малость неопрятно и становится трудной для использования. Казалось бы, простая задача может стать непростой. Например, найти покупателя с именем John Doe (нет лишних пробелов между частями имени), в то время как в таблице он сохранён как “John Doe“. Или числа, которые не могут быть просуммированы, а виноваты в этом опять лишние пробелы.
Из этой статьи Вы узнаете, как очистить данные от лишних пробелов:
Удаляем все лишние пробелы между словами, отсекаем начальные и конечные пробелы
Предположим, есть таблица с двумя столбцами. В столбце Name в первой ячейке содержится имя John Doe, записанное корректно, т.е. без лишних пробелов. Все остальные ячейки содержат вариант записи с лишними пробелами между именем и фамилией, а также в начале и в конце (начальные и конечные пробелы). Во втором столбце, с названием Length, показано количество символов в каждом имени.
Используем функцию «СЖПРОБЕЛЫ» для удаления лишних пробелов
В Excel существует функция TRIM (СЖПРОБЕЛЫ), которая используется для удаления лишних пробелов из текста. Далее Вы найдёте пошаговую инструкцию по работе с этим инструментом:
- Рядом со своими данными добавьте вспомогательный столбец. Можете назвать его Trim.
- В первой ячейке вспомогательного столбца (C2) введите формулу для удаления лишних пробелов:
Готово! Мы удалили все лишние пробелы при помощи функции TRIM (СЖПРОБЕЛЫ). К сожалению, этот способ занимает много времени, особенно, когда таблица достаточно велика.
Замечание: Если после применения формулы Вы по-прежнему видите лишние пробелы, скорее всего в тексте содержатся неразрывные пробелы. Как их удалить, Вы можете узнать из этого примера.
Применяем инструмент «Найти и заменить», чтобы удалить лишние пробелы между словами
Этот вариант требует меньше действий, но позволяет удалить только лишние пробелы между словами. Начальные и конечные пробелы будут также отсечены до 1, но не удалены полностью.
- Выделите один или несколько столбцов с данными, в которых нужно удалить лишние пробелы между словами.
- Нажмите Ctrl+H, чтобы открылось диалоговое окно Find & Replace (Найти и заменить).
- Введите два раза пробел в поле Find What (Найти) и один раз в поле Replace With (Заменить на).
- Нажмите кнопку Replace all (Заменить все), а затем ОК, чтобы закрыть появившееся информационное окно.
- Повторяйте шаг 4 до тех пор, пока не появится сообщение We couldn’t find anything to replace… (Мы не нашли ничего, что нужно было заменить…).
Удаляем все пробелы между числами
Предположим, у Вас есть таблица с числами, в которых группы разрядов (тысячи, миллионы, миллиарды) разделены пробелами. В этом случае Excel рассматривает числа как текст и никакая математическая операция выполнена быть не может.
Простейший способ избавиться от лишних пробелов – это воспользоваться стандартным инструментом Excel – Find & Replace (Найти и заменить).
- Нажмите Ctrl+Space (Пробел), чтобы выделить все ячейки в столбце.
- Нажмите Ctrl+H, чтобы открыть диалоговое окно Find & Replace (Найти и заменить).
- В поле Find What (Найти) введите один пробел. Убедитесь, что поле Replace With (Заменить на) – пустое.
- Нажмите кнопку Replace all (Заменить все), затем ОК. Вуаля! Все пробелы удалены.
Удаляем все пробелы при помощи формулы
Возможно, Вам пригодится способ удалить все пробелы при помощи формулы. Для этого Вы можете создать вспомогательный столбец и ввести такую формулу:
Здесь A1 – это первая ячейка столбца с числами или словами, в которой должны быть удалены все пробелы.
Источник: office-guru.ru
Удалить лишние пробелы в Excel
Основная и досадная проблема с лишними пробелами, которая мешает их удалению вручную — пробел является невидимым символом.
Между слов лишние пробелы еще заметны по увеличивающемуся между ними расстоянию, пробел с начала строки тоже легко выявить, т.к. строка смещается от левой границы ячейки. А вот если пробел в строке идет после последнего символа, или в ячейке в принципе одни пробелы, их не видно вовсе.
О вреде лишних пробелов
Наличие лишних пробелов может помешать работе в следующих случаях:
- Требуется сравнить ячейки между собой. Это может быть как поиск дубликатов внутри диапазона, так и сравнение двух списков и поиск повторяющихся значений между ними
- При построении сводных таблиц строки «схлопываются», если являются полностью идентичными, и разница в один пробел не позволит им «схлопнуться». Алгоритм построения сводных таблиц также сравнивает ячейки между собой.
- С помощью «ВПР» или сочетания ИНДЕКС/ПОИСКПОЗ можно «подтянуть» данные из одной таблицы в другую. Эти функции тоже сравнивают ячейки при поиске и учитывают разницу в виде пробелов.
Обычно лишние пробелы не несут значимости в тексте, и популярная задача — удалить их из текста. Но удалить именно лишние, чтобы фразы не склеивались в одно слово.
Удалить лишние пробелы с помощью процедуры «Найти и заменить».
Оговорюсь сразу — самый большой недостаток этого подхода в том, что единичные пробелы в начале и конце строк он не удалит. И если для вас они тоже лишние, вам этот вариант не подходит — смотрите варианты ниже.
Тем не менее, на многих сайтах рекомендуется этот способ. Вызвать процедуру замены сочетанием CTRL+F, вбить в поля «Найти» и «Заменить на» 2 пробела и 1 пробел соответственно и произвести замену, нажав кнопку «Заменить все».
Важно делать это неоднократно — до тех пор, пока процедура не выведет сообщение:
Почему так? Потому что всегда есть вероятность, что между словами было 3 и более пробелов.
Удалить лишние пробелы формулой
В Excel для этого есть функция СЖПРОБЕЛЫ. Полезная функция, но она требует создания дополнительного столбца, применения функции в нем и далее удаления исходного, если принципиальной разницы для пользователя между столбцами нет. Формула замечательно удаляет все пробелы, кроме единичных между словами.
Функция СЖПРОБЕЛЫ
Удалить лишние пробелы в столбце, не создавая дополнительный
Наверняка многим хотелось бы удалять лишние пробелы без применения функций и создания дополнительных столбцов, меняя строки на их же месте.
Для таких случаев и была создана процедура удаления лишних пробелов в !SEMTools. Выделяем список ячеек, жмем на кнопку в меню, готово!
Удаление лишних пробелов в 1 клик
Удалить переносы строк внутри ячеек («энтеры»)
Помимо обычных пробелов, бывают другие разделители, похожие по своей сути на пробел. К таковым относятся разрывы строк. Если их просто удалить, значения строк могут склеиться между собой, поэтому предварительно лучше заменить их на свой символ-разделитель.
В !SEMTools такая возможность тоже есть! Т.к. надстройка добавляет регулярные выражения в Excel. В синтаксисе регулярных выражений s (space) обозначает любые виды пробелов, включая переносы строк. Заменой можно преобразовать их в обычные пробелы, и если останутся лишние, уже повторить шаг выше.
Удалить переносы (разрывы) строк в ячейках столбца в Excel
Удалить пробелы в конце строки
Довольно гипотетическая, но все же возможная ситуация — когда нужно удалить только пробелы в конце строки, а если есть два или более пробелов подряд в середине строки, такие не удалять. Здесь снова на помощь придут регулярные выражения:
Как видно, регулярное выражение заменяет пробел или несколько пробелов на пустоту, если они идут в конце строки.
Удалить пробелы в начале строки
Аналогично, если задача удалить только пробелы в начале строк, а другие лишние пробелы не трогать. Синтаксис будет чуть другим:
Напомню, функции регулярных выражений не включены в Excel по умолчанию, но их поддержку можно включить с помощью некоторого кодинга на VBA. Если у вас !SEMTools, и это не потребуется. Использование функций на листе не ограничено и доступно даже в базовой бесплатной версии. Достаточно установить Excel-надстройку.
Источник: semtools.guru
Как в экселе убрать пробелы в начале и в конце ячейки?
Часто при переносе информации из одной программы в программу эксель, данные встают в ячейку не совсем корректно, в т.ч. можно увидеть лишние пробелы в начале или в конце ячейки. Давайте разберемся, каким способами можно справиться с ненужными пробелами.
Перед нами таблица, в которой в первом столбце содержаться названия городов, при этом перед словами и после них, есть лишние пробелы. Для первых двух строк мы используем один метод, а для двух других строк второй метод.
Первый метод. Выделим диапазон ячеек с «А2» по «А3», после нажмем на клавиатуре одновременно клавиши: «Ctrl+H», в результате появится меню «Найти и заменить». В строке «Найти» поставим пробел, а строку «Заменить на» не будем трогать и нажмем на кнопку «Заменить всё».
В итоге программа удалить лишние пробелы. Но правда нужно знать о недостатках такого метода, он хорошо подходить если в ячейке одно слово, но если их два, то он уберет проблем и между этими словами, поэтому нужно знать и альтернативный способ борьбы с пробелами.
Второй метод. В программе эксель существует специальная функция СЖПРОБЕЛЫ, которая как раз корректно работает, если в ячейке находиться больше одного пробела. В нашем примере в ячейке «С4» пишем формулу =СЖПРОБЕЛЫ(A4). Копируем данную формулу в ячейку «С5».
Дальше необходимо перенести данные из ячеек «С4» и «С5» в ячейки «А4» и «А5». Для этого выделяем диапазон ячеек с «С4» по «С5» и нажимаем сочетание клавиш «Ctrl+C», после ставим курсор в ячейку «А4» и нажимаем на правую клавишу мыши и в меню выбираем строку «Специальная вставка».
В появившемся меню «Специальная вставка» в разделе «Вставить» ставим точку напротив строки «Значения» и после через кнопку «ОК» закрываем данное меню.
В результате все данные теперь корректно отображаются, а данные из ячеек «С4» и «С5» мы удалили.
Источник: portalonline.ru
