Как убрать все пробелы в excel
Как убрать пробелы в числах в Excel
Представляем 4 быстрых способа удалить лишние пробелы между цифрами в ячейках Excel. Вы можете использовать формулы, инструмент «Найти и заменить» или попробовать изменить формат ячейки.
Когда вы вставляете данные из внешнего источника в электронную таблицу Excel (текстовые отчеты, числа с веб-страниц и т.д.), то с большой вероятностью получите лишние пробелы вместе с важными данными. Могут появиться начальные и конечные пробелы, несколько интервалов между словами или же разделители разрядов для чисел.
Как следствие, ваша таблица выглядит неаккуратно и становится неудобна в использовании. Найти клиента в столбце «Имя» может быть непросто, так как вы ищете «Иван Петров», в котором нет лишних интервалов между именами, а в таблице он выглядит как « Иван Петров ». Или же числа не суммируются. И здесь снова скорее всего виноваты лишние пробелы.
Ранее мы уже подробно рассмотрели, как удалить лишние пробелы и прочие ненужные символы из текста (ссылки смотрите в конце). В этой статье вы узнаете, как очистить свои цифровые данные.
Способ 1. Используем инструмент «Найти и заменить».
Рассмотрим пример, когда мы импортировали цифровые данные из отчета, подготовленного другой программой. Там в числах были разделители тысяч, чтобы их было удобнее читать. Естественно, эти разделители попали в нашу таблицу, а числа оказались импортированы как текст. В результате никакие математические операции с ними оказались невозможны.
Наша задача – преобразовать цифры в числа, убрав из них всё ненужное. Разберём пошагово.
Вот теперь вы совершенно точно не только удалили пробелы из чисел, но и превратили ваши цифры в полноценные числа, пригодные для математических вычислений. К сожалению, если будет импортирована новая партия данных, то все операции придется повторять снова.
2. Формула ПОДСТАВИТЬ.
Рассмотрим те же исходные данные, что и в предыдущем примере. Но в этот раз попробуем очистить их при помощи формул.
Используем функцию ПОДСТАВИТЬ, чтобы удалить все пробелы перед, после, а также между цифрами.
В результате мы имеем цифры, записанные в виде текста. Чтобы иметь возможность производить с ними различные математические вычисления, нужно преобразовать их в числа.
Для этого существует 2 способа.
Если мы попытаемся произвести с текстом какое-то действие как с числом (например, умножение или вычитание), то Excel в первую очередь попытается конвертировать этот текст в число, чтобы выполнить наше задание. Этим и воспользуемся. Если дважды умножить число на минус 1, то его значение не изменится. То же самое можно сделать, дважды поставив знак минус перед формулой. Назовем это «двойное отрицание».
В результате мы имеем настоящие числа. Ну а если нужны пробелы-разделители между разрядами, используйте для этого соответствующий числовой формат.
3. Функция СЖПРОБЕЛЫ.
Если у нас нет разделителей разрядов между цифрами, но есть начальные пробелы перед цифрами, то можно использовать функцию СЖПРОБЕЛЫ.
А затем, используя тот же метод, что был описан выше, мы превращаем цифры в числа.
Вот как это выглядит на скриншоте:
В этом случае рекомендуем немного усложнить выражение:
Если же имеются неразрывные пробелы между цифрами, то вы их также не сможете убрать способами, описанными выше. Вот что можно предложить:
Более подробные пояснения по этим формулам смотрите в этом руководстве.
4. Форматирование ячеек.
Порой причиной ненужных пробелов в числах иногда является форматирование. Оно может быть уже установлено ранее или же перенесено вместе с данными из внешнего источника. Чтобы все исправить, нужно по правой кнопке мыши выбрать вкладку «Формат ячеек». Появится список настроек, нужно выбрать вкладку «Число», где и настроить наличие разделителей групп разрядов.
Как видите, способ несложный. Но работает он только с числами. Если цифры записаны как текст, то никакого эффекта эти манипуляции не принесут.
Как удалить пробелы в ячейках Excel
Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов.
В чем самая большая проблема с пробелами? Часто они невидимы для человеческого глаза. Внимательный пользователь может иногда заметить пустой интервал, скрывающийся перед текстом, или несколько ненужных пустот между словами. Но нет никакого способа визуально обнаружить конечные пробелы, те, которые находятся вне поля зрения в конце ячеек.
Не было бы большой проблемы, если бы эти лишние интервалы просто существовали, но они могут повлиять на результаты ваших формул. Дело в том, что две ячейки, содержащие один и тот же текст с пробелами и без них, даже если это всего лишь один знак, считаются разными значениями. В результате вы можете ломать голову, пытаясь понять, почему очевидно правильная формула не может соответствовать двум, казалось бы, идентичным записям.
Теперь, когда вы полностью осознаете проблему, пришло время найти решение. Есть несколько способов удалить пробелы из строки, и это руководство поможет вам выбрать метод, наиболее подходящий для вашей конкретной задачи и типа данных, с которым вы работаете.
Как убрать пробелы в Excel при помощи «Найти и заменить»
Это более быстрый метод, который может быть полезен в следующих ситуациях:
Обратите внимание, что этот метод не рекомендуется использовать для удаления начальных или конечных пробелов, так как хотя бы один их них там все равно останется.
Итак, найдем и заменим двойные интервалы независимо от их расположения.
Вот как это можно сделать:
Нажмите «Заменить все».
Обратите внимание, что если у вас есть три интервала между двумя словами, то теперь вы получите два (один будет удален). В таких случаях вы можете повторить эту операцию снова, чтобы удалить любые двойные пробелы, которые ещё могли остаться.
Недостатки этого метода вы видите сами: перед некоторыми из слов остался начальный пробел. Аналогично остались и конечные пробелы после текста, просто они не видны явно. Поэтому для текстовых выражений я не рекомендовал бы применение этого способа.
Найти: одинарный пробел.
Заменить на: оставьте это поле пустым.
Это удалит все пробелы в выбранном диапазоне.
Функция СЖПРОБЕЛЫ.
Стандартный синтаксис очень простой:
Как показано на следующем скриншоте, СЖПРОБЕЛЫ успешно удалила всё до и после текста, а также лишние интервалы в середине строки.
Формулы для удаления начальных и концевых пробелов.
В некоторых ситуациях вы можете вводить двойные или даже тройные интервалы между словами, чтобы ваши данные были более удобочитаемыми. Однако вам нужно избавиться от ведущих пробелов (находящихся в начале), например:
Как вы уже знаете, функция СЖПРОБЕЛЫ удаляет лишние интервалы в середине текстовых строк, чего мы в данном случае не хотим. Чтобы сохранить их нетронутыми, мы будем использовать немного более сложную конструкцию:
Это выражение в начале вычисляет позицию первого знака в строке. Затем вы передаете это число другой функции ПСТР, чтобы она возвращала всю текстовую строку (длина строки рассчитывается с помощью ДЛСТР), начиная с позиции первого знака.
Вы видите, что все ведущие пробелы исчезли, хотя несколько интервалов между словами всё же остались.
В качестве последнего штриха замените исходный текст полученными значениями, как это было описано выше.
Если же нужно удалить только пробелы в конце каждой ячейки, то формула будет немного сложнее:
И обратите снимание, что ее нужно вводить как формулу массива (с Ctrl+Shift+Enter ). В столбце A выровнять по правому краю получилось плохо из-за разного количества концевых пробелов в каждой ячейке. В столбце B эта проблема решена, и можно красиво расположить текст.
Как удалить разрывы строк и непечатаемые символы
При импорте данных из внешних источников появляются не только лишние пробелы, но и различные непечатаемые символы, такие как возврат каретки, перевод строки, вертикальная или горизонтальная табуляция и т.д.
Чтобы удалить ещё и непечатаемые символы в строке, используйте её в сочетании с функцией ПЕЧСИМВ (CLEAN в английской версии). Как следует из названия, ПЕЧСИМВ предназначена для очистки данных от ненужного «мусора» и умеет удалять любой из первых 32 непечатаемых символов в 7-битном наборе ASCII (значения от 0 до 31), включая разрыв строки (значение 10).
Предполагая, что очищаемые данные находятся в ячейке A2, формула будет следующей:
Практически всегда, когда при помощи приведенного выше выражения вы удаляете разрывы строки, то отдельные слова оказываются «склеенными» друг с другом. Вы можете исправить это, используя один из следующих способов:
=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2; СИМВОЛ(13);» «); СИМВОЛ(10); » «))
Как видите, почтовый адрес в колонке С выглядит вполне читаемо.
Как убрать неразрывные пробелы в Excel?
Если после использования формулы СЖПРОБЕЛЫ и ПЕЧСИМВ некоторые упрямые знаки все еще остаются, то скорее всего, вы скопировали / вставили данные из интернета или из другой программы, и несколько неразрывных пробелов все же прокрались в вашу таблицу.
Чтобы избавиться от неразрывных пробелов (html-код ), замените их обычными, а затем попросите функцию СЖПРОБЕЛЫ удалить их:
Чтобы лучше понять логику, давайте разберем формулу:
Если ваш рабочий лист также содержит непечатаемые символы, дополнительно к описанному выше используйте ещё функцию ПЕЧСИМВ, чтобы избавиться от пробелов и других ненужных знаков одним махом:
Следующий скриншот демонстрирует разницу в результатах:
Как удалить определенный непечатаемый символ
Если взаимодействие трех функций, описанных в приведенном выше примере, не смогло устранить весь мусор из текста, то это означает, что оставшиеся знаки имеют значения ASCII, отличные от 0 до 32 (непечатаемые символы) или 160 (неразрывный пробел).
В этом случае используйте функцию КОДСИМВ, чтобы сначала идентифицировать код мешающего вам знака, а затем используйте ПОДСТАВИТЬ, чтобы заменить его обычным пробелом. А затем при помощи СЖПРОБЕЛЫ удалите его.
Предполагая, что нежелательные символы, от которых вы хотите избавиться, находятся в ячейке A2, вы пишете два выражения:
В этом примере у нас есть неизвестный знак в середине текста, в 11-й позиции, и мы определим его код:
Получаем значение 127 (см. скриншот ниже).
Если ваши данные содержат несколько разных непечатаемых символов, а также неразрывные пробелы, вы можете вложить две или более функции ПОДСТАВИТЬ друг в друга для одновременного удаления всех нежелательных знаков:
Результат должен выглядеть примерно так:
Эту универсальную формулу мы и использовали. Как видите, успешно.
Как удалить все пробелы
В некоторых ситуациях может потребоваться удалить абсолютно все пробелы в ячейке, включая те, что находятся между словами или числами. Например, если вы импортировали в вашу таблицу числовой столбец, в котором пробелы используются в качестве разделителей тысяч. Конечно, разделители разрядов упрощают чтение больших чисел, но они же препятствуют вычислению ваших формул. Такие разделители нужно создавать при помощи форматирования, а не вручную.
Чтобы удалить все пробелы одним махом, используйте ПОДСТАВИТЬ, как описано в предыдущем примере, с той лишь разницей, что вы заменяете знак пробела, возвращаемый при помощи СИМВОЛ(32), ничем («»):
Или вы можете просто ввести его (» «) в формуле, например:
Результатом этого будет текст, состоящий из цифр. Если же в качестве результата вам нужны именно числа, добавьте перед формулой два знака “-“ (минус). Любая математическая операция автоматически превращает цифры в число. А дважды применив минус, то есть дважды умножив на минус 1, мы не изменим величину числа и его знак.
Как посчитать пробелы в Excel
Чтобы получить общее количество пробелов в ячейке, выполните следующие действия:
Предполагая, что исходная текстовая строка находится в ячейке A3, полная формула выглядит следующим образом:
Чтобы узнать, сколько лишних пробелов в ячейке, получите длину текста без них, а затем вычтите ее из первоначальной длины:
На рисунке показаны обе формулы в действии:
Теперь, когда вы знаете, сколько пробелов содержит каждая ячейка, вы можете безопасно удалить лишние, используя функцию СЖПРОБЕЛЫ.
Простой способ удаления пробелов без формул.
Как вы уже знаете, лишние пробелы и другие нежелательные символы могут незаметно скрываться на ваших листах, особенно если вы импортируете данные из внешних источников. Вы также знаете, как удалять пробелы в Excel с помощью формул. Конечно, изучение нескольких формул – хорошее упражнение для оттачивания ваших навыков, но это может занять много времени.
Всякий раз, когда вы хотите удалить лишние пробелы в таблицах Excel, выполните следующие 4 быстрых шага:
Готово! Все лишние пробелы удалены одним щелчком мыши:
Здесь мы вместе с лишними пробелами удалили еще и переводы строки, чтобы значения располагались в привычном виде, в одну строку.
Вот как можно быстро удалить пробелы в ячейках Excel. Если вам интересно изучить другие возможности работы с текстовыми значениями (и не только), вы можете загрузить
ознакомительную версию Ultimate Suite. Благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!
Как в excel сделать разделитель разрядов пробелом?
Рассмотрим способы удаления пробелов в Excel (например, между словами, в начале или конце предложений, в числах).
Приветствую всех, уважаемые читатели блога TutorExcel.Ru.
Зачастую при копировании текста или переносе таблиц возникают проблемы с наличием лишних символов, которые могут появиться по самым разным причинам: неправильный ввод данных, импортирование данных из внешних источников и т.д.
Достаточно часто лишние пробелы в Excel встречаются в тексте в следующих случаях:
Помимо того, что данные могут стать трудночитаемыми, так и использование обычных инструментов Excel (к примеру, сортировка, фильтрация, поиск, применение функций и т.д.) становится затруднительным и некорректным.
Поэтому, чтобы при работе избежать подобных проблем, для корректной работы необходимо удалять лишние символы.
Пробелы в числах
Пробелы в числах могут возникнуть в любой момент, а если увлеченно работать, то можно даже не заметить их появление. Среди основных причин стоит отметить такие:
Обычный «Пробел» можно удалить достаточно просто, но вышеописанные причины просто так не устраняются. Для них нужно использовать специальные формулы, скрипты и пользоваться инструментами форматирования.
Форматирование ячеек
Порой причиной ненужных пробелов является форматирование. Оно может быть уже установлено с отступами или переместится вместе с данными из внешнего источника. Чтобы все исправить нужно нажать правой кнопкой мыши на верх столбца и выбрать там вкладку «Формат ячеек». Появится список настроек, нужно выбрать закладку «Число», где уже и настроить наличие разрядных промежутков.
Если же нужно убрать разделительные пропуски в числах, то заходим в настройки формата ячейки и выбираем «Числовой». При этом, снимаем галочку «Разделитель групп разрядов».
Найти и заменить
«Найти и заменить» — это самый простой из всех возможных методов убрать пробелы в ячейке Excel. Чтобы все получилось, нужно соблюдать следующий порядок действий:
На экране высветится новое окно, которое сообщит о совершенных заменах и их количестве. Для надежности можно проделать последний шаг еще раз, чтобы убедиться в том, что все пропуски в файле устранены.
Удаление апострофов в начале ячеек
Апостроф (‘) в начале ячейки на листе Microsoft Excel — это специальный символ, официально называемый текстовым префиксом. Он нужен для того, чтобы дать понять Excel, что все последующее содержимое ячейки нужно воспринимать как текст, а не как число. Апостроф виден только в строке формул, на листе его не видно.
По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная → Число → Текстовый), и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим.
Но иногда апостроф оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert → Module) и введите туда его текст.
Sub Apostrophe_Remove() For Each cell In Selection If Not cell.HasFormula Then v = cell.Value cell.Clear cell.Formula = v End If Next End Sub
Теперь если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик → кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.
Функция СЖПРОБЕЛЫ
Для удаления пробелов в ячейках Excel часто используют специальную функцию, которая называется СЖПРОБЕЛЫ или TRIM (в англоязычной версии). Она несколько сложнее в исполнении, чем предыдущий метод, но гораздо действеннее и способна справиться с более сложным типом неразрывных отступов. Чтобы все получилось, нужно придерживаться инструкции:
И таким образом произойдет удаление ненужных пробелов между числами или буквами. Второй столбик (или строчку) можно удалить.
Способ 3: удаление путем форматирования
Если вы набираете большие числа в ячейках таблицы и видите, что после нажатия Enter между разделителями появляется пробел, то значит, у вас стоит соответствующая опция форматирования. Сейчас будет рассказано, как убрать пробелы в числах в Excel, отключив ее.
После этого окно будет закрыто, а вы можете наблюдать, что все лишние пробелы были удалены из документа. Как видите, это довольно простой способ, как можно убрать пробелы в числах в Excel, но стоит учитывать, что он работает лишь в случаях форматирования текста.
Формула ПОДСТАВИТЬ
Кроме первых двух способов есть еще один метод, в котором используется специальная формула «ПОДСТАВИТЬ», если используется англоязычная версия, то «SUBSTITUTE».
Чтобы формула заработала нужно в соседнем столбике или строчке от того, где находятся числа с пробелами, ввести следующую формулу: =ПОДСТАВИТЬ(A1;» «;»»). На место A1 вписывается нужный адрес. После этого, при необходимости, формула копируется на все остальные ячейки и заменяется таким же способом, как в предыдущем методе. Таким образом можно удалить пробелы во всем столбце.
Способ 4: с помощью специальной функции
Все вышеописанные методы неэффективны в том случае, если нужно в Excel удалить пробелы, находящиеся в начале или в конце значения. Однако с этим безукоризненно справляется функция СЖПРОБЕЛЫ.
Сразу после этого лишние пробелы будут убраны. Однако исправленный вариант будет находиться в той ячейке, где была прописана формула. Ничего страшного, просто скопируйте ячейку и вставьте ее в нужную область.
Удаление пробелов только слева или справа
Если нужно произвести удаление пробелов только слева или справа (иначе называются начальные и конечные), нужно выполнить следующие действия:
Также статьи о работе с ячейками в Экселе:
Для правильной работы формул и фильтров в Экселе важно соблюдать правильность написания данных в ячейках. Так лишние пробелы и другие знаки вызывают появление ошибок при использовании формул, и неправильную работу фильтров.
Убрать пробелы в ячейках Excel можно как с применением специальных функций, так и с помощью простой функции поиска и замены данных через меню, все зависит от конкретной ситуации. Так убрать пробелы между цифрами в ячейке Excel вполне можно с помощью поиска и замены.
Как правило, пробелы между цифрами в ячейке Эксель появляются при вставке данных из других источников. Причем, как видно на примере ниже, все цифры с пробелами воспринимаются Экселем как текстовые данные, так как они выравниваются по левому краю.
Единственный способ перевести эти данные в числовые значения, это убрать пробелы между цифрами в ячейке Excel. Проще всего это сделать с помощью поиска пробелов и замены их на пустое место. Но если при поиске просто ввести пробел с клавиатуры, это может не всегда сработать, и Эксель выдаст, что ничего не найдено.
Для правильного поиска нам необходимо скопировать пробел именно из имеющегося числа с пробелом. Затем на вкладке «Главная» находим меню «Найти и выделить», и выбираем пункт «Заменить». Также можно просто нажать комбинацию клавиш «Ctrl + H».
Для корректной замены можно предварительно выделить диапазон всех ячеек с числами, или выбрать любую пустую ячейку, чтобы поиск производился по всему листу. В появившемся окошке «Найти и заменить» на вкладке «Заменить» вставляем скопированный ранее пробел в графу «Найти:», а графу «Заменить на:» оставляем пустой. Затем нажимаем кнопку «Заменить все», и получаем сообщение о количестве замен.
Когда речь идет о текстовых данных, где между словами может встречаться по два и более лишних пробела, функция поиска и замены также может помочь. При этом нужно искать сразу два пробела, и заменять их на один. Проделывать данную процедуру необходимо до тех пор, пока не будет найдено ни одного совпадения.
Но для текстовых данный есть и другой вариант поиска и удаления лишних пробелов. Для этих целей можно воспользоваться функцией «СЖПРОБЕЛЫ». Данная функция будет удалять все пробелы перед и после текста, а также между словами, всегда оставляя только один пробел.
Для использования функции необходимо записать формулу в другой ячейке, в которой затем и будет отображен результат ее работы.
Лишние пробелы в тексте не красят любой документ. Особенно их не нужно допускать в таблицах, которые предоставляются руководству или общественности. Но даже, если вы собираетесь использовать данные только в личных целях, лишние пробелы способствуют увеличению объема документа, что является негативным фактором. Кроме того, наличие подобных лишних элементов затрудняет поиск по файлу, применение фильтров, использование сортировки и некоторых других инструментов. Давайте выясним, какими способами их можно быстро найти и убрать.
Замена символов
Старый, но не устаревший трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная → Найти и выделить (Home → Find & Select → Replace) или жмем сочетание клавиш Ctrl+H.
Изначально это окно было задумано для оптовой замены одного текста на другой по принципу «найди Маша — замени на Петя», но мы в данном случае можем использовать его и для удаления лишнего текста. Например, в первую строку вводим «г.» (без кавычек!), а во вторую не вводим ничего и жмем кнопку Заменить все (Replace All). Excel удалит все символы «г.» перед названиями городов.
Удаляем все лишние пробелы между словами, отсекаем начальные и конечные пробелы
Предположим, есть таблица с двумя столбцами. В столбце Name в первой ячейке содержится имя John Doe, записанное корректно, т.е. без лишних пробелов. Все остальные ячейки содержат вариант записи с лишними пробелами между именем и фамилией, а также в начале и в конце (начальные и конечные пробелы). Во втором столбце, с названием Length, показано количество символов в каждом имени.
Используем функцию «СЖПРОБЕЛЫ» для удаления лишних пробелов
В Excel существует функция TRIM (СЖПРОБЕЛЫ), которая используется для удаления лишних пробелов из текста. Далее Вы найдёте пошаговую инструкцию по работе с этим инструментом:
Готово! Мы удалили все лишние пробелы при помощи функции TRIM (СЖПРОБЕЛЫ). К сожалению, этот способ занимает много времени, особенно, когда таблица достаточно велика.
Замечание: Если после применения формулы Вы по-прежнему видите лишние пробелы, скорее всего в тексте содержатся неразрывные пробелы. Как их удалить, Вы можете узнать из этого примера.
Применяем инструмент «Найти и заменить», чтобы удалить лишние пробелы между словами
Этот вариант требует меньше действий, но позволяет удалить только лишние пробелы между словами. Начальные и конечные пробелы будут также отсечены до 1, но не удалены полностью.