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

Когда вы вставляете данные из внешнего источника в электронную таблицу Excel (текстовые отчеты, числа с веб-страниц и т.д.), то с большой вероятностью получите лишние пробелы вместе с важными данными. Могут появиться начальные и конечные пробелы, несколько интервалов между словами или же разделители разрядов для чисел.

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

Ранее мы уже подробно рассмотрели, как удалить лишние пробелы и прочие ненужные символы из текста (ссылки смотрите в конце). В этой статье вы узнаете, как очистить свои цифровые данные.

Способ 1. Используем инструмент «Найти и заменить».

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

Наша задача – преобразовать цифры в числа, убрав из них всё ненужное. Разберём пошагово.

  • Выделяем диапазон с нужными данными. Если данных много, нажмите Ctrl + Пробел для выбора всех ячеек в столбце.
  • Затем вызываем инструмент «Найти и заменить» (можно при помощи комбинации Ctrl + H). В поле «Найти» вставляем пробел, поле «Заменить» оставляем пустым.
  • Жмём «Заменить всё». Повторяйте этот шаг, пока не увидите сообщение «Не удалось найти замену». :)
  • Интервалы между разрядами исчезли, но цифры все равно остаются выровненными по левому краю ячейки. Что означает, что это по-прежнему текст.
  • Используем инструмент вставки значений, чтобы получить числа из текста. В любую клетку рабочего листа вписываем цифру 1 и срезу копируем ее в буфер обмена. Можно через контекстное меню, но мне больше нравится комбинация клавиш CTRL+C.
  • Вновь выделяем наш диапазон с цифрами и вызываем «Специальная вставка» (кроме меню можно использовать CTRL+ALT+V.
  • Выбираем Вставить — Значения, Операция — Умножить. Теперь в нашем распоряжении – не текст, а числа.
  • Но чаще всего на этом не заканчивается. К нашим данным скорее всего при импорте был автоматически применен текстовый формат. Вновь выделите их и смените формат с «Текст» на «Общий» либо на любой из цифровых форматов (денежный, с разделителями, процентный и т.п.). Еще раз повторю, что эта операция не всегда необходима, но вполне вероятна.

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

2. Формула ПОДСТАВИТЬ.

Рассмотрим те же исходные данные, что и в предыдущем примере. Но в этот раз попробуем очистить их при помощи формул.

Используем функцию ПОДСТАВИТЬ, чтобы удалить все пробелы перед, после, а также между цифрами.

=ПОДСТАВИТЬ(A2;" ";"")

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

Для этого существует 2 способа.

  1. При помощи функции ЗНАЧЕН

=ЗНАЧЕН(ПОДСТАВИТЬ(A2;" ";""))

  1. При помощи математической операции с текстом.

Если мы попытаемся произвести с текстом какое-то действие как с числом (например, умножение или вычитание), то Excel в первую очередь попытается конвертировать этот текст в число, чтобы выполнить наше задание. Этим и воспользуемся. Если дважды умножить число на минус 1, то его значение не изменится. То же самое можно сделать, дважды поставив знак минус перед формулой. Назовем это «двойное отрицание».

=--ПОДСТАВИТЬ(A2;" ";"")

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

3. Функция СЖПРОБЕЛЫ.

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

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

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

=--СЖПРОБЕЛЫ(A2)

Вот как это выглядит на скриншоте:

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

В этом случае рекомендуем немного усложнить выражение:

=СЖПРОБЕЛЫ(ПЕЧСИМВ(A2))

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

=СЖПРОБЕЛЫ(ПЕЧСИМВ((ПОДСТАВИТЬ(A2;СИМВОЛ(160);" "))))

Более подробные пояснения по этим формулам смотрите в этом руководстве.

4. Форматирование ячеек.

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

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

Как удалить пробелы в ячейках Excel - Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов. В чем самая большая проблема с…
Функция СЖПРОБЕЛЫ — как пользоваться и примеры - Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии)  не работает и как…
Функция ПРАВСИМВ в Excel — примеры и советы. - В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части…
Функция ЛЕВСИМВ в Excel. Примеры использования и советы. - В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди…
Как использовать функцию ПСТР в Excel? - ПСТР - одна из текстовых функций, которые Microsoft Excel предоставляет для управления текстовыми строками. На самом базовом уровне она используется для извлечения подстроки из середины текста.  В этом руководстве мы обсудим…
5 примеров с функцией ДЛСТР в Excel. - Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)…
Как быстро сосчитать количество знаков в ячейке Excel - В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…
Как в Excel разделить текст из одной ячейки в несколько - В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на…