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

Как убрать излишние пробелы в Excel?

Приветствую всех, почетаемые читатели блога TutorExcel.Ru.

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

Довольно нередко излишние пробелы в Excel встречаются в тексте в последующих вариантах:

• Сначала либо в конце предложений;
• Дублирование пробелов;
• Невидимые знаки, в т.ч. неразрывный пробел;
• В числах (в разделителях разрядов).

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

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

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

Разглядим 2 главных метода, как можно удалить излишние знаки:

• Инструмент «Отыскать и поменять». Удаление пробелов через диалоговое окно;
• При помощи функций. Формульный вариант удаления.

Инструмент «Отыскать и поменять»

Представим, что у нас есть текст либо таблица со словами, в котором находится дублирование пробелов.

Для начала выделяем спектр ячеек с данными и избираем на панели вкладок Основная -> Редактирование -> Отыскать и выделить -> Поменять или просто жмем сочетание кнопок Ctrl + H:

Возникает обычное диалоговое окно подмены, потом вводим в поле Отыскать два пробела, а в Поменять на — один. Жмем Поменять все и повторяем операцию до того времени, пока Excel находит повторения.

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

При помощи функций

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

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

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

Неразрывный пробел в Excel

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

Данный знак снаружи смотрится как обыденный пробел, но его недозволено удалить при помощи функции СЖПРОБЕЛЫ либо инструмента «Отыскать и Поменять» (при вводе в поле для подмены обыденного пробела).

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

Любой знак в Excel имеет свою шифровку в таблице символов ANSI (для ОС Windows), в том числе и достойные внимания нам одиночный пробел (знак 32) и неразрывный (знак 160).

Потому при помощи функции ПОДСТАВИТЬ заменяем неразрывный пробел (знак 160) на обыденный (знак 32), а потом к приобретенному результату применяя функцию СЖПРОБЕЛЫ получаем требуемый итог:

По сути убрать неразрывный пробел можно и при помощи «Отыскать и поменять», для этого в поле Отыскать нужно ввести неразрывный пробел и поменять его или на обыденный, или удалить, оставив поле Поменять на пустым.

Возникает вопросец, как это создать?

1-ый метод — конкретно скопировать данный знак из текста и ввести в поле Отыскать.

2-ой метод — пользоваться Alt-кодом (для систем Windows) и ввести комбинацию Alt + 0160 (удерживая кнопку Alt необходимо набрать 0160 на цифровой клавиатуре).

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

Неувязка с удалением пробелов встречается и при работе с числами — в числах попадаются пробелы (например, заместо разделителей разрядов), из-за что они воспринимаются в Excel как текст.

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

Аналогичный итог можно получить и при помощи формулы:

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

Спасибо за внимание!
Если у вас есть мысли либо вопросцы по теме статьи — делитесь ими в комментах.

Удаление излишних пробелов функцией СЖПРОБЕЛЫ (TRIM) и формулами

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

Лечится эта неувязка весьма просто — специальной функцией СЖПРОБЕЛЫ (TRIM) . Она убирает все излишние пробелы, но оставляет по одному пробелу меж словами:

Просто и прекрасно. Но есть пара аспектов.

Неразрывные пробелы не удаляются

Чайной ложкой дегтя здесь представляется лишь невозможность удалять таковым методом неразрывные пробелы. Это особенный вариант пробела (снаружи неотличимый от обыденного), на котором не бывает переноса строчки. Обычно его употребляют в вариантах типа «г. Москва» либо «Иванов И.И.», чтоб эти фразы не разбивались меж строками. В Microsoft Word таковой спецсимвол вводится сочетанием кнопок Ctrl+Shift+Пробел и отображается в виде кружка, а не точки:

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

Формулы для удаления исходных и концевых пробелов

Если нужно удалить лишь исходные пробелы (т.е. не трогать концевые и пробелы меж словами), то придется созодать это хитрецкими формулами, т.к. готовой функции (по аналогии с функцией LTrim в VBA) в Microsoft Excel, к огорчению, нет.

Формула для удаления излишних пробелов лишь слева будет смотреться так:

В британской версии =MID(A1;FIND(LEFT(TRIM(A1));A1);LEN(A1))

Формула для удаления излишних пробелов лишь справа будет чуток посложнее и обязана вводиться уже как формула массива (с Ctrl+Shift+Enter):

В британском варианте это будет =LEFT(A1;MAX((MID(A1&REPT(» «;99);ROW(A1:A99),1);» «)*ROW(A1:A99)))

Ссылки по теме

Для удаления пробелов в начале и в конце строчки можно применять условный оператор ЕСЛИ.
Ранее лучше пару раз при помощи поиска и подмены убрать двойные пробелы.

Пробел в начале строчки: =ЕСЛИ(ЛЕВСИМВ(А1;1)=» «;ПРАВСИМВ(А1;ДЛСТР(А1)-1))
Пробел в конце строчки: =ЕСЛИ(ПРАВСИМВ(А1;1)=» «;ЛЕВСИМВ(А1;ДЛСТР(А1)-1))

А еще так можно .
Для удаления пробелов в начале и в конце строчки
можно применять функцию =СЦЕПИТЬ(). Допустим, ячейка A1 проблемная ,у неё пробел сначала и в конце, нам это не нужно.
Вставляем новейший столбец и обрабатываем формулой =СЖПРОБЕЛЫ(A1), дальше Выделить — Вст а вить зн а чения, сейчас циклических пробелов не будет, но в начале и в конце они остались.
Вставим очередной столбец, а в столбец формулу =СЦЕПИТЬ(«&&»;A1;»&&») , здесь основное чтоб && не встречался в тексте. Снова Выделить — Вст а вить зн а чения . Сейчас у нас в начале и конце строчки не пробел, а два раза амперсанд+ пробел.
Дальше Ctrl+F отыскиваем &&[пробел] — поменять ничем, снова отыскиваем [пробел]&& — поменять ничем, там где были пробелы они будут удалены вкупе с амперсандами, там где пробелов не было остались наши &&
Проводим крайний Поиск -замену Ctrl+F На й ти && Поменяет ь ничем.

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

© Николай Павлов, Planetaexcel, 2006-2021
info@planetaexcel.ru

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

За изображения спасибо Depositphotos.com

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРН 310633031600071

Удаление излишних пробелов в Microsoft Excel

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

Разработка удаления пробелов

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

Метод 1: внедрение инструмента «Поменять»

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

Находясь во вкладке «Основная», кликаем по кнопочке «Отыскать и выделить», которая расположена в блоке инструментов «Редактирование» на ленте. В выпадающем перечне избираем пункт «Поменять». Можно также заместо перечисленных выше действий просто набрать сочетание кнопок на клавиатуре Ctrl+H.

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

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

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

Выделяем столбец либо спектр, в котором необходимо удалить разделители меж цифрами. Этот момент весьма важен, потому что если спектр не будет выделен, то инструмент уберет все пробелы из документа, в том числе и меж словами, другими словами, там, где они вправду необходимы. Дальше, как и ранее, кликаем по кнопочке «Отыскать и выделить» в блоке инструментов «Редактирование» на ленте во вкладке «Основная». В дополнительном меню избираем пункт «Поменять».

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

Метод 3: удаление разделителей меж разрядами методом форматирования

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

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

Выделяем спектр чисел с разделителями. Кликаем по выделению правой клавишей мыши. В показавшемся меню избираем пункт «Формат ячеек…».

Метод 4: удаление пробелов при помощи функции

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

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

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

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

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

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

Мы рады, что смогли посодействовать Для вас в решении препядствия.

Кроме данной нам статьи, на веб-сайте еще 12327 инструкций.
Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

Отблагодарите создателя, поделитесь статьей в соц сетях.

Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.

2 метода удалить пробелы меж словами либо числами в ячейках Excel

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

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

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

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

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

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

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

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

1. Рядом со своими данными добавьте вспомогательный столбец. Сможете именовать его Trim.
2. В первой ячейке вспомогательного столбца (C2) введите формулу для удаления излишних пробелов:

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

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

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

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

1. Выделите один либо несколько столбцов с данными, в которых необходимо удалить излишние пробелы меж словами.
2. Нажмите Ctrl+H, чтоб раскрылось диалоговое окно Find & Replace (Отыскать и поменять).
3. Введите дважды пробел в поле Find What (Отыскать) и один раз в поле Replace With (Поменять на).
4. Нажмите клавишу Replace all (Поменять все), а потом ОК, чтоб закрыть показавшееся информационное окно.
5. Повторяйте шаг 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 – это 1-ая ячейка столбца с числами либо словами, в которой должны быть удалены все пробелы.