В этой статье поговорим о том, как удалить пробелы в ячейках excel. С одной стороны кажется, что ничего проблемного здесь нет, но все становится намного хуже, когда речь идет об обработке больших объемов данных. Неожиданно появившиеся в начале, конце или даже внутри текстовых строк, они способны нарушить сортировку, фильтрацию, формулы и, в конечном итоге, привести к ошибочным результатам.
К счастью, в Excel есть эффективные способы, как решить эту проблему. Мы обсудим, как простые ручные методы, так и более автоматизированные функции. Благодаря понимаю этих инструментов и их правильного применения получиться значительно повысить точность и надежность ваших данных!
Содержание
Способ 1 замена
Первый вариант, как можно избавиться от всех лишних пробелов – это простая замена символов. Вот инструкция, как это сделать:
- Переходим в раздел «Главная». После чего в правой верхней части экрана будет находиться раздел «Редактирование», где расположен значок лупы. Кликаем туда. Потом откроется выпадающее меню, где требуется выбрать пункт «Заменить».
- Откроется окно «Найти и заменить». В первой строчке нужно указать тот символ (или символы), которые нужно найти. В нижней строке указываем на что нужно заменить найденные результаты. В нашем случае, вверху указываем «__» (два пробела), внизу – «_» (один пробел). После чего кликаем на «Заменить все».
- После этих действий программа начнет искать все элементы, совпадающие с запросом, и заменит их на обычные пробелы. Однако, если результатов не будет найдено, то Excel оповестит об этом.
Таким методом можно заменять не только пробелы, но и остальные символы. Допустим, пользователю нужно заменить все «;» в документе на «:». Повторяем все шаги, описанные выше, и получаем готовый результат. Это очень важная функция, которой очень часто пользуются даже опытные пользователи, чтобы в excel удалить пробелы.
Способ 2 удаление пробела между цифрами
Данным способом можно воспользоваться в другой ситуации, которая возникает очень часто у многих пользователей. А если точнее, то мы говорим о случае, когда данные копируются или переносятся с другого документа. Допустим, у нас есть много ячеек с большими числами, запись которых ведется удобным для восприятия образом.
Например, 1 миллион записан так – 1 000 000. Между цифрами есть пробел, который помогает быстрее понять, какое это число. Однако в некоторых версиях Excel не может обработать подобный вариант записи и поэтому в итоге будет выдавать неверные результаты подсчетов. Именно в этом случае и поможет этот метод.
ВАЖНО! Многие современные версии программы Excel легко могут работать с такими цифрами и удаление пробелов в excel не потребуется. Поэтому прежде всего стоит перепроверить – будут ли функции работать корректно или нет.
Вот подробная инструкция, как убрать пробелы между цифрами:
- Вначале выделяем ячейки, где расположены необходимые нам цифры;
- Снова переходим в раздел редактирование и нажимаем на пункт «Найти и выделить». Выбираем «Заменить».
- Теперь на этот раз в первой строке нужно ввести «_» (один пробел), а нижнюю оставить пустой, стерев все с помощью кнопки на клавиатуре «Backspace». Снова нажимаем на «Заменить все». Проверяем результат.
ИНТЕРЕСНО! Программа в этом случае может оповестить пользователя о том, что она «не нашла ничего, что нужно было заменить». Это означает то, что она не воспринимает пробелы, которые мы видим за действительные. Что говорит о том, что в настройках программы заранее был отмечен параметр для визуального выделения числа.
Если вам это нужно убрать, то сделать это можно следующим образом:
- Выделяем необходимые ячейки;
- Кликаем правой кнопкой мышки и находим пункт «Формат ячейки»;
- Откроется новое окно, в котором ищем раздел «Числовой». Убираем галочку в пункте «Разделитель групп разрядов ()»;
- Нажимаем на «ОК».
Смотрим на готовый результат.
Способ 3 использование функции
Хоть прошлые способы и работают, но они никак не смогут помочь в случае, когда пробел расположен в начале либо в конце строки. Возможно, визуально этот пробел никак не будет мешать пользователю.
Однако вся проблем кроется в том, что во время автоматических подсчетов могут возникнуть проблемы и ошибки. Кроме того, программа может неправильно определить формат ячейки. А значит, что использовании функций будет уместным и рабочим вариантом. Подробнее рассмотрим, как это сделать.
Для работы с пробелами понадобиться функция «СЖПРОБЕЛЫ». Она легко сможет убрать пробел. На месте звездочек нужно будет указать текст или ссылку на какую-то ячейку. Вот как это сделать:
- Выделяем ячейку, расположенную напротив той, что нужно исправить;
- Нажимаем на кнопку «Функции», которая находиться слева от строки значений;
- Выбираем категорию «Текстовые» и устанавливаем необходимую функцию;
- Выбираем «СЖПРОБЕЛЫ» и кликаем на «ОК»;
- После этого потребуется указать текст или адрес на ячейку. В нашем примере выберем ячейку А2;
- Остается выделить исправленное значение, скопировать его при помощи сочетания клавиш «Ctrl + C» и заменить старые значения, нажав «Ctrl + Alt + V» для специальной вставки. Специальная вставка отличается от обычной тем, что она повторит все формулы и вычисления, которые были проведены до этого. Обычная же вставка просто скопирует получившийся результат. И если пользователь что-то изменит в таблице, то это никак не повлияет на скопированные ячейки…
ВАЖНО! Данные, которые мы получили после использования функции можно удалить, больше они не пригодятся.
Конечно, этот способ не так удобен, как было до этого. Но зато благодаря таким манипуляциям получиться убрать лишние пробелы, которые могут находиться в начале либо конце строки.
Как убрать лишние пробелы во всем столбце
А что делать, когда исправить нужно большой объем ячеек, в которых пробелы находятся вначале и в конце строки? Как в excel убрать пробелы такого типа? Возвращаемся все к той же функции
=СЖПРОБЕЛЫ(***)
Только на этот раз сделаем немного по-другому:
- Как и в прошлый раз выделяем ячейку и находим в разделе «Функции» нужную нам «=СЖПРОБЕЛЫ(***)».
- Указываем ячейку, которую хотим исправить. Нажимаем «ОК». Только на это раз выделяем получившийся результат и протягиваем его вниз. Таким образом программа повторит функцию во всех ячейках.
- Теперь выделяем получившийся результат, копируем его «Ctrl + C» и заменяем нужный столбец, нажав на «Ctrl + Alt + V».
Готово!
Как удалить ведущие пробелы в столбце с числами
В примерах выше мы работали с ячейками, в которых располагались текстовые значения. Но как убрать пробелы в excel, если в ячейке находятся числа? К удивлению, если воспользоваться уже знакомой нам функцией
=СЖПРОБЕЛЫ(***)
, то она не сможет помочь. Пробелы будут удалены, но итоговые значения не будут являться числами. Как понять, что это не число, а текст? Вот несколько способов:
- В Excel по стандартам текст всегда выравнивается по левому краю, а числа по правому. Если «числа» притянулись к левому краю, значит это текст.
- Если выделить 2 или больше ячеек с числами, то программа всегда показывает их сумму и среднее значение. Если этого не произошло, то это не числа.
- И последний способ определить проблему – это воспользоваться функцией СУММ. Если в ячейках текст, то пользователю вернется значение 0.
Итак, мы поняли, что формула не будет работать с числами. Как же тогда решить проблему? Есть 2 варианта. Рассмотрим каждый из них:
- В первом случае достаточно будет функцию СЖПРОБЕЛЫ поместить в другую функцию ЗНАЧЕН. Выглядеть это будет следующим образом:
=ЗНАЧЕН(СЖПРОБЕЛЫ(A2))»
Таким образом будут удалены пробелы с начала и конца строки, если они имеются.
- А есть второй более простой вариант. Если пользователю нужно, чтобы в ячейке осталось просто число. То для этого вообще не нужно удалять пробелы. Достаточно просто прописать функцию
=ЗНАЧЕН(А2)
На выходе будет только число, которое будет поддаваться любым математическим вычислениям.
Оба этих метода работают и оба дадут одинаковый результат. Поэтому здесь уже стоит выбирать на собственное усмотрение.
Как посчитать лишние пробелы в ячейке
Мы уже обсудили, как убрать пробелы в ячейке excel. Но делать, если нужно понять, сколько ненужных пробелов находиться в ячейке? Для этого сделаем несколько простых действий.
Достаточно просто вычислить длину текста, воспользовавшись функцией ДЛСТР, после чего вычислить длину текста, не считая дополнительные интервалы. Последнее значение вычитаем из первого. Вот как будет выглядеть итоговая формула:
=ДЛСТР(A2)-ДЛСТР(СЖПРОБЕЛЫ(A2)).
ВАЖНО! Эта формула будет искать только ДОПОЛНИТЕЛЬНЫЕ пробелы, не беря в те, которые находятся в середине текста. То есть засчитаются только пробелы перед текстом, после него и двойные пропуски.
Как выделить ячейки с лишними пробелами
Допустим пользователю необходимо отметить любые ячейки, которые хранят в себе лишние пробелы для того, чтобы потом удалить пробелы в excel. В таком случае легче всего будет воспользоваться следующей формулой:
=ДЛСТР($A2)>ДЛСТР(СЖПРОБЕЛЫ($A2))
Разберем подробно, как она работает. В данном случае А2 – это ссылка на верхнюю ячейку, в которой располагаются данные для выделения. Формула говорит программе о том, чтобы она выделяла любую ячейку, общая длина которой больше, чем длина имеющегося в ней текста.
Теперь нам нужно создать правило условного форматирования, которое визуально выделит нужные нам ячейки. Для этого делаем следующие шаги:
- Во вкладке «Главная» находим раздел «Условное форматирование». Далее выбираем «Создать правило»;
- В открывшемся окне находим тип правила «Использовать формулу для определения форматируемых ячеек». Немного ниже есть раздел «Измените описание правила». Вставляем формулу в пустую строку и нажимаем «ОК».
Готово! Формула примениться к нашему документу и все ячейки с лишними пробелами будут выделены!
Если не работает удаление пробелов
Может произойти и такая ситуация, что после применения всех советов, описанных выше, у пользователя так и не получиться удалить пробелы в ячейке excel. Но почему? Все дело в том, что функция «СЖПРОБЕЛЫ» может удалить только те пробелы, которые предоставлены в виде кода 32 в 7-битном наборе символов ASCII.
Но существует другой пробел, который получил название неразрывный. Он часто применяется на интернет-страничках в виде символа « » Символ имет значение 160 и функция просто не способна его удалить. Помочь в такой ситуации может функция «ПОДСТАВИТЬ», которая вначале заменить неразрывной пробел на стандартный, а потом уберет его. Вот как будет выглядеть итоговая формула:
=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2; СИМВОЛ(160); » «))
Чаще всего такая формула поможет в решении проблемы.
Вот такие простые и легкие советы помогут вам удалить любые лишние пробелы из вашего документа для того, чтобы избежать проблем в расчетах и вычислениях.
