Удалить излишние пробелы в Excel

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

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

О вреде излишних пробелов

Наличие излишних пробелов может помешать работе в последующих вариантах:

Требуется сопоставить ячейки меж собой. Это быть может как поиск дубликатов снутри спектра, так и сопоставление 2-ух списков и поиск циклических значений меж ними
При построении сводных таблиц строчки «схлопываются», если являются на сто процентов схожими, и разница в один пробел не дозволит им «схлопнуться». Метод построения сводных таблиц также ассоциирует ячейки меж собой.
При помощи «ВПР» либо сочетания ИНДЕКС/ПОИСКПОЗ можно «подтянуть» данные из одной таблицы в другую. Эти функции тоже ассоциируют ячейки при поиске и учитывают разницу в виде пробелов.

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

Методы удаления излишних пробелов

От обыденных пробелов можно избавиться 3-мя известными мне методами. Разглядим их подробнее.

При помощи процедуры «Отыскать и поменять»

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

Тем не наименее, на почти всех веб-сайтах рекомендуется этот метод. Вызвать функцию подмены сочетанием CTRL+F, вбить в поля «Отыскать» и «Поменять на» 2 пробела и 1 пробел соответственно и произвести подмену, нажав клавишу «Поменять все».

Удаление излишних пробелов меж словами при помощи подмены

Принципиально созодать это не один раз — до того времени, пока процедура не выведет сообщение:

Почему так? Поэтому что постоянно есть возможность, что меж словами было 3 и наиболее пробелов.

Функцией СЖПРОБЕЛЫ

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

опосля крайнего слова
перед первым словом
заменяя два и наиболее пробела меж словами на один

При помощи готовой процедуры в !SEMTools

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

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

Удаление излишних пробелов в 1 клик

Удалить переносы строк снутри ячеек («энтеры»)

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

В !SEMTools таковая возможность тоже есть! Т.к. надстройка добавляет постоянные выражения в Excel. В синтаксисе постоянных выражений s (space) обозначает любые виды пробелов, включая переносы строк. Подменой можно конвертировать их в обыденные пробелы, и если останутся излишние, уже повторить шаг выше.

Удалить переносы (разрывы) строк в ячейках столбца в Excel

Удалить пробелы в конце строчки

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

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

Удалить пробелы в начале строчки

Аналогично, если задачка удалить лишь пробелы в начале строк, а остальные излишние пробелы не трогать. Синтаксис будет чуток остальным:

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

Четыре метода, как стремительно удалить пробелы в Excel

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

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

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

Интересно почитать: Как в excel все ячейки сделать одного размера

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

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

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

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

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

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

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

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

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

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

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

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

Метод 4: при помощи специальной функции

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

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

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

Excel как убрать пробел в конце ячейки

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

Способ A: Удалите все излишние пробелы из строк при помощи функции TRIM (2 шага)
Удалите все излишние пробелы, включая избыточное, изначальное и конечное пробелы
Способ B: удаление ведущих пробелов из строк при помощи кода VBA (5 шагов)
Удалите лишь 1-ый пробел, перед пуском кода необходимо иметь копию
Способ C: удалите ведущие / конечные / доп / все пробелы при необходимости при помощи Kutools (3 шага)
Удалите ведущие / конечные / доп / все пробелы при необходимости
Скачать эталон файла
Этот эталон файла содержит все данные, нужные для данной нам статьи.
Остальные операции (статьи), связанные с местами
Считать пробел перед текстовой строкойНайти позицию n-го пробелаСкоро..

Интересно почитать: Excel сцепить ячейки

Способ A: Удалите все излишние пробелы из строк при помощи функции TRIM (2 шага)

Удалите излишние пробелы из строк

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

=TRIM(B2)

B2 — это ячейка, из которой вы желаете удалить пробелы, см. Скриншот:

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

Наконечник:

1. Вы сможете скопировать и вставить результаты формулы как значение, как показано ниже:

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

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

Если в вашей строке есть некие непечатаемые знаки, к примеру, показавшиеся в Excel как CHAR (160), их недозволено удачно удалить при помощи функции TRIM. В этом случае для вас нужно соединить функции TRIM, CLEAN и SUBSTITUTE для обработки этого задания.

Используя эту формулу:

=TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160),» «)))

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

Потом перетащите маркер заливки на ячейки, к которым вы желаете применить эту формулу.

Примечание:

Функция TRIM удалит все излишние пробелы, включая ведущие, конечные пробелы и излишние пробелы меж словами. Если вы желаете удалить лишь исходные пробелы, перейдите к способу Б. Если вы желаете удалить лишь исходные пробелы либо лишь конечные пробелы, либо желаете удалить все излишние пробелы, включая непечатаемые знаки, перейдите к способ C.

13 текстовых инструментов, которые вы обязаны иметь в Excel, которые повысят вашу эффективность на 90%

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

▲ Не считая инструментов, отображаемых на картинке, в Kutools for Excel еще есть 200 расширенных инструментов, которые могут решить ваши 82% головоломки Excel.

▲ Станьте профессионалом по Excel за 5 минут, получите признание и продвижение по службе.

▲ 110000+ высокоэффективных служащих и 300+ всемирно узнаваемых компаний.

60-дневная бесплатная пробная версия, кредитная карта не требуется Читать дальше Загрузить на данный момент

Способ B: удаление ведущих пробелов из строк при помощи кода VBA (5 шагов)

Если вы желаете конвертировать либо импортировать все данные в документе Word в Excel, вы сможете употреблять код VBA.

1. Нажмите Alt + F11 ключ для включения Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модуль для сотворения новейшего Модуль скрипт, скопируйте и вставьте в скрипт приведенный ниже код.

VBA: удаление ведущих пробелов из строчки

3. Нажмите F5 ключ для пуска кода, потом KutoolsforExcel Возникает диалоговое окно для выбора ячеек для удаления ведущих пробелов.

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

Примечание:

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

Способ C: удалите ведущие / конечные / доп / все пробелы при необходимости при помощи Kutools (3 шага)

Этот инструмент является одним из 229 инструментов в Kutools for Excel, он предоставляет 5 вариантов удаления разных типов пространств при необходимости:

Удалить ведущие пробелы
Удалить конечные пробелы
Удалить исходные и конечные пробелы
Удалите все излишние пробелы
Удалить все пробелы

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

1. Изберите данные, из которых вы желаете удалить пробелы, потом щелкните Kutools > Удалить пробелы. Смотрите снимок экрана:

2. Потом отметьте подходящий вариант в Удалить пробел Диалог.

Удалить лишь ведущие пробелы:

Удалить лишь конечные пробелы:

Удалите лишь исходные и конечные пробелы:

Удалите все избыточное место:

Удалить все пробелы:

excel удалить знаки в строке

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

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

Интересно почитать: Excel узнать количество символов в ячейке excel

1. Отыскать и поменять знак в ячейке Excel

Если кратко, то существует особый инструмент во всех продуктах MS Office. Вызывается кнопками Ctrl+F (отыскать) либо Ctrl+H (поменять). В 1-ое поле можно вписать знак который необходимо удалить, во 2-ое ничего не вчеркивайте.

Подробнее о этом инструменте написано в данной нам статье

2. Удалить знак в ячейке Excel формулой

Удалить хоть какой знак можно прелестной функцией =ПОДСТАВИТЬ().

Эта функция имеет последующую систему =ПОДСТАВИТЬ(текст;старый_текст;новый_текст;номер_вхождения)

текст — фактически, указывается ячейка где записан текст для конфигурации

старый_текст — что меняем

новый_текст — на что меняем

номер_вхождения — является необязательным. Он предписывает Excel поменять лишь знаки начиная с определенного номера. Увлекательное дополнение

Представим, что для вас необходимо поменять все точки на запятые, формула:

3. =СЖПРОБЕЛЫ()

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

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

Но в 95% случаев сжпробелы помогает на «непревзойденно».

Удаление пробелов я даже вынес в отдельную статью

4. Удалить 1-ый знак

Нередко нужно удалить 1-ый, несколько крайних знаков и так дальше. Как это создать стремительно и не вручную? Воспользуйтесь функцией ПСТР .

Весьма тщательно о этом написано тут .

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

Формула будет смотреться последующим образом.

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

5. Функция =ПЕЧСИМВ()

Функция с одним аргументом весьма похожа на СЖПРОБЕЛЫ, но она удаляет непечатные знаки в строке, такие как перенос строчки, знаки абзаца, какие-нибудь квадратики и т.п. (1-ые 32 непечатаемых символов в 7-разрядном коде ASCII). В особенности полезна она, если необходимо удалить перенос строчки — мне постоянно помогает.

Внимание! ПЕЧСИМВ способна удалить большая часть, но не все излишние знаки.

Удалить 1-ый знак слева в программке эксель можно при помощи специальной интегрированной функции, разглядим пошаговую аннотацию, как это создать.

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

Поначалу мы должны найти полное количество знаков в каждой ячейки. Для этого воспользуемся функцией ДЛСТР, которая подсчитывает количество знаков. Пишем в ячейки В2 формулу: =ДЛСТР(A2), а потом её копируем на спектр В3 по В6.

Сейчас приступим к удалению первого знака слева, для этого воспользуемся функцией ПРАВСИМВ. Пишем в ячейки В2 формулу: =ПРАВСИМВ(A2;ДЛСТР(A2)-1), где: A2 – адресок ячейки у котором будем убирать 1-ый знак слева, ДЛСТР(A2)-1 – число символов, которые возвращаем с конца строчки с правой стороны, эта цифра для каждой ячейки расчетная, которая выходит методом вычитание 1-го знака из общего их числа.

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

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

Функции, которая отрезает избыточное количество знаков в ячейке, отыскать не удается, но существует система из 2-ух функций, которые можно применить. Это система из функции ЛЕВСИМВ (либо ПРАВСИМВ) и функции ДЛСТР (длина строчки).

Разглядим их применение.

Сначала находим количество знаков в тексте с помощью функции ДЛСТР. Для этого запускаем менеджер функций, избираем ДЛСТР, указываем ячейку с текстом. Функция ДЛСТР посчитает количество знаков.

Зная длину излишнего текста — 6 символов в индексе, вычитаем их из общего числа знаков посчитанных функцией ДЛСТР и получаем то количество знаков, которое обязано остаться в тексте, когда удалим индекс.

Приобретенное значение – количество оставляемых в тексте символов, вписываем в функцию ЛЕВСИМВ либо ПРАВСИМВ. Если необходимо удалить знаки справа, то используем ЛЕВСИМВ, если слева, то ПРАВСИМВ. Жмем «Enter». Функция ЛЕВСИМВ либо ПРАВСИМВ присваивает ячейке необходимое нам количество знаков из текста в новейшую ячейку, исключая ненадобный нам индекс.

Для уменьшения количества ячеек с расчетами эти функции можно записать в систему последующего вида: