В процессе работы с любыми документами пользователь незаметно для себя может оставлять нежелательные пробелы между словами или числами. Существует множество способов исправления подобной ситуации. Рассмотрим их в статье.
Как удалить пробелы в Excel
Перед началом отметим, что сами пробелы бывают разные: между словами, между числовыми выражениями или же в начале или конце какого-либо значения. Соответственно, для устранения каждого типа существует свой отдельный метод. Именно о них сейчас и пойдет речь.
Способ 1: с помощью инструмента «Заменить»
Если вы в процессе работы в программе случайно устанавливали два пробела между словами вместо одного, тогда без проблем удалить пробелы в Excel можно с помощью инструмента «Заменить». Пользоваться им довольно просто:
- Перейдите на вкладку «Главная».
- На панели инструментов, в правой ее части, нажмите по кнопке «Найти и выделить». Нажмите по ней.
- В появившемся выпадающем списке выберите пункт «Заменить».
- Появится окно, в котором в поле «Найти» введите двойной пробел. Для этого просто дважды нажмите по соответствующей клавише.
- В поле «Заменить на» пропишите один пробел.
- В окне с отчетом нажмите «ОК».
После этого все двойные пробелы в Excel будут удалены. Как можно отметить, способ этот довольно простой в использовании. Все последующие методы не будут отличаться особой сложностью, и пользователь легко поймет, как удалить лишние пробелы в Excel.
Способ 2: удаление пробелов между разрядами
Только что мы разобрались, как удалить пробелы в ячейках Excel с текстовыми выражениями, но что, если появилась потребность убрать лишние одинарные пробелы в числовых выражениях, которые зачастую устанавливаются автоматически? В этом случае нам также поможет инструмент «Заменить».
- В таблице выделите область, в которой находятся ненужные пробелы.
- Находясь на главной странице на панели инструментов, нажмите по кнопке «Найти и заменить» и выберите инструмент «Заменить».
- В появившемся окне на этот раз нужно ввести немного другие значения.
- В поле «Найти» поставьте один пробел.
- Поле «Заменить на» оставьте нетронутым.
- Нажмите кнопку «Заменить все».
- Подтвердите свои действия, ознакомьтесь с отчетом и примите его.
После этого вы обнаружите, что пробелы между числами исчезли, соответственно, поставленная задача выполнена.
Способ 3: удаление путем форматирования текста
Вполне возможно, что использование вышеописанной инструкции по удалению пробелов в Excel не дало никаких результатов. При этом программа вам сказала, что пробелов попросту нет. Происходит это лишь в одном случае - если пробелы в числах были поставлены путем форматирования. По сути, это никак не повлияет на работу формул и всего прочего, однако некоторым пользователям очень важно, чтобы все числа были записаны в ряд без пробелов. Если вы из их числа, тогда выполните следующую инструкцию:
- Выделите область таблицы, в ячейках которой вы хотите избавиться от пробелов между числовыми выражениями.
- Кликните по выделению правой кнопкой мыши.
- Выберите пункт «Формат ячеек».
- Появится соответствующее окно, в котором перейдите в раздел «Числовой», расположенный во вкладке «Число».
- В правой части окна снимите отметку с пункта «Разделитель групп разрядов».
- Нажмите «ОК».
После этого окно будет закрыто, а между числовыми значениями пропадут пробелы. Так что, если вся суть заключалось в форматировании, то этот метод обязательно поможет вам.
Способ 4: с помощью специальной функции
Все вышеописанные методы неэффективны в том случае, если нужно в Excel удалить пробелы, находящиеся в начале или в конце значения. Однако с этим безукоризненно справляется функция СЖПРОБЕЛЫ.
- Установите курсор в той ячейке, которая находится параллельно столбцу или строке, пробелы из которых нужно убрать.
- Откройте окно Мастера функций, нажав по соответствующей кнопке, находящейся рядом со строкой функций.
- В списке «Категория» выберите "Текстовый", а в списке с перечнем функций выделите «СЖПРОБЕЛЫ» и нажмите «ОК».
- Появится окно ввода функции. В нем вам нужно указать ячейку, в которой хотите убрать пробелы. Для этого просто кликните по ней левой кнопкой мыши.
- Нажмите «ОК».
Сразу после этого лишние пробелы будут убраны. Однако исправленный вариант будет находиться в той ячейке, где была прописана формула. Ничего страшного, просто скопируйте ячейку и вставьте ее в нужную область.
Лишние пробелы в тексте замечают не все. И не для всех это, в общем-то, важно. Но в Excel это может обернуться серьезной проблемой, потому что при выполнении команд программа просто не будет учитывать ячейки с дополнительными пробелами.
Как удалить лишние пробелы в Excel в автоматическом режиме? Чтобы не испортить зрение, вглядываясь в каждую ячейку и убирая лишние доступы клавишей Backspace, можно воспользоваться специальной функцией – СЖПРОБЕЛЫ.
Синтаксис функции СЖПРОБЕЛЫ в Excel
Да, проговорить название команды может оказаться непросто. Но вот понять ее синтаксис и принцип работы очень легко. Если начать вводить команду, то подсветится следующее: =СЖПРОБЕЛЫ(текст). Т.е. в скобках нужно всего лишь задать ячейку (ячейки), в которых необходимо удалить пробелы.
Примечание: функция убирает все лишние пробелы, за исключением того, который ставится по умолчанию между словами.
Те, кто хорошо владеет техникой печатания, наверняка недоумевают, как вообще могут появиться лишние пробелы. Но опытные пользователи знают, что информация для Excel часто копируется из разных источников, которые могли заполняться дилетантами. Поэтому в тексте и встречаются опечатки и ненужные пробелы.
Пример использования функции СЖПРОБЕЛЫ
На небольшом примере рассмотрим, как пользоваться функцией СЖПРОБЕЛЫ. Имеем таблицу, в которой указаны наименования детских игрушек и их количество. Не очень грамотный оператор вел примитивный учет: при появлении дополнительных единиц игрушек он просто впечатывал новые позиции, несмотря на то, что аналогичные наименования уже есть. Наша задача: подсчитать общую сумму каждого вида.
Невооруженным глазом видно, что некоторые позиции написаны с явными лишними пробелами. Рассмотрим, к чему это может привести.
Подсчитывать общее количество позиций по каждому виду игрушек будем через функцию СУММЕСЛИ. Вводим ее, протягиваем на остальные ячейки и смотрим результат. Программа выдала ответ по плюшевым зайцам: 3. Хотя мы видим, что их должно быть 3+2=5. В чем проблема? В лишних пробелах.
Запишем функцию в ячейке D3. В качестве аргумента введем ячейку B3, в которой значится наименование игрушки.
Теперь протянем функцию до 14 строки и увидим, что тексты действительно выровнялись, потому что пробелы удалились. Проверим это наверняка, изменив диапазон в команде СУММЕСЛИ. Вместо B3:B14 пропишем D3:D14 и посмотрим результат. Теперь плюшевых зайцев действительно 5, медведей – 6 и т.п. Получается, символы табуляции играют важную роль, и их нужно подчищать.
СЖПРОБЕЛЫ с другими функциями
Использование функции СЖПРОБЕЛЫ вместе с другими функциями расширяет возможности пользователя. Логично, что она будет использоваться вместе с теми функциями, которые исследуют массивы и возвращают данные. В частности, это функции НАЙТИ, ЛЕВСИМВ, ПРАВСИМВ и др. На практике оказывается, что чаще всего СЖПРОБЕЛЫ используется вместе с ВПР.
Рассмотрим на примере той же таблицы. Задача: проставить цену напротив каждой позиции. Справа дана вспомогательная таблица, в которой прописана цена каждой игрушки. Теоретически мы можем вручную перепечатать нужные нам цифры, но раз мы в Excel, будем использовать ВПР.
Если мы протянем формулу до конца таблицы, увидим следующее:
Розовым цветом мы специально подсветили те позиции, которые написаны с лишними пробелами. Поэтому команда ВПР не выдала по ним результат. Чтобы исправить это, добавим к ВПР функцию СЖПРОБЕЛЫ и посмотрим, что получится.
Т.к. пробелы нам нужно удалить в искомом значении, команду СЖПРОБЕЛЫ поставим на первое место в синтаксисе ВПР. Формула немного видоизменилась, и если теперь мы протянем ее до низа таблицы, функция проставит нам все значения. Теперь все правильно.
Как еще можно удалить лишние пробелы в Excel?
Избавиться от лишних пробелов можно и без использования функции СЖПРОБЕЛЫ. Воспользуемся старым проверенным способом, который нам знаком еще из WORD – команда НАЙТИ-ЗАМЕНИТЬ.
Пример останется тот же самый. Выделяем столбец, в котором прописаны наименования игрушек и нажимаем CTRL+H. В появившемся окне напротив НАЙТИ проставляем пробел, а напротив ЗАМЕНИТЬ НА не пишем ничего.
Проблема такого способа в том, что он убирает все, в том числе между словами. Поэтому этот путь используют, преимущественно, для удаления лишних пробелов в составе чисел.
Многие ошибки возникают из-за того, что вы даже не можете увидеть: из-за пробела. Рассмотрим пример, показанный на рис. 198.1. Ячейка В2 содержит формулу, которая ищет название цвета в ячейке В1 и возвращает соответствующий код из таблицы: =ВПР(B1;D2:E9;2;ЛОЖЬ) .
На рис. 198.2 формула в ячейке В2 возвращает ошибку указывающую на то, что красный не был найден в таблице. Тысячи пользователей Excel потратили бы уйму времени, пытаясь выяснить, почему так произошло. В данном случае ответ прост: ячейка D7 не содержит слово Красный . Точнее, она содержит слово Красный , за которым идет пробел. Для Excel эти строки совершенно разные.
Если лист содержит тысячи текстовых записей и вам необходимо выполнить сравнение, то сначала нужно найти ячейки, которые содержат избыточные пробелы, а затем исправить их. Термин «избыточный пробел» означает текстовую запись, которая содержит что-нибудь из следующего набора: один или несколько пробелов в начале, один или несколько пробелов в конце либо от двух идущих вместе пробелов.
Один из способов идентификации этого типа ячеек заключается в применении условного форматирования. Чтобы задать условное форматирование, выполните следующие действия.
- Выделите все текстовые ячейки, к которым нужно применить условное форматирование.
- Выберите Главная Стили Условное форматирование Создать правило для открытия диалогового окна .
- В верхней части окна выберите пункт Использовать формулу для определения форматирующих ячеек.
- Введите формулу, аналогичную следующей, в нижней части диалогового окна: =А1СЖПРОБЕЛЫ(А1) . Заметьте: здесь предполагается, что ячейка А1 является верхней левой ячейкой в выделенном фрагменте. Если это не так, замените адрес верхней левой ячейки для набора ячеек, которые вы выбрали в шаге 1.
- Нажмите кнопку Формат для открытия окна Формат ячеек и выберите тип форматирования для ячеек, содержащих избыточные пробелы. Например, задайте выделение желтым цветом.
- Нажмите кнопку ОК , чтобы закрыть окно Формат ячеек , и снова нажмите ОК , чтобы закрыть окно Создание правила форматирования .
После выполнения этих шагов каждая ячейка, которая содержит избыточные пробелы и находится в пределах выбранного в шаге 1 диапазона, будет отформатирована по вашему выбору. Вы можете легко определить эти ячейки и удалить пробелы.
Из-за особенностей работы функции СЖПРОБЕЛЫ формула в шаге 4 также применяет условное форматирование ко всем числовым ячейкам. Вот немного более сложная формула, которая не применяет форматирование к числовым ячейкам: =ЕСЛИ(НЕ(ЕНЕТЕКСТ(А1));А1СЖПРОБЕЛЫ(А1))
Из этой статьи Вы узнаете 2 быстрых способа удалить лишние пробелы между словами или все пробелы из ячеек Excel. Вы можете использовать функцию TRIM (СЖПРОБЕЛЫ) или инструмент Find & Replace (Найти и заменить), чтобы вычистить содержимое ячеек в Excel.
Когда Вы вставляете данные из внешнего источника на лист Excel (простой текст, числа и т.д.), то можете получить лишние пробелы вместе с важными данными. Это могут быть начальные и конечные пробелы, несколько пробелов между словами или разделители разрядов в числах.
Следовательно, таблица выглядит малость неопрятно и становится трудной для использования. Казалось бы, простая задача может стать непростой. Например, найти покупателя с именем John Doe (нет лишних пробелов между частями имени), в то время как в таблице он сохранён как “John Doe “. Или числа, которые не могут быть просуммированы, а виноваты в этом опять лишние пробелы.
Из этой статьи Вы узнаете, как очистить данные от лишних пробелов:
Удаляем все лишние пробелы между словами, отсекаем начальные и конечные пробелы
Предположим, есть таблица с двумя столбцами. В столбце Name в первой ячейке содержится имя John Doe , записанное корректно, т.е. без лишних пробелов. Все остальные ячейки содержат вариант записи с лишними пробелами между именем и фамилией, а также в начале и в конце (начальные и конечные пробелы). Во втором столбце, с названием Length , показано количество символов в каждом имени.
Используем функцию «СЖПРОБЕЛЫ» для удаления лишних пробелов
В Excel существует функция TRIM (СЖПРОБЕЛЫ), которая используется для удаления лишних пробелов из текста. Далее Вы найдёте пошаговую инструкцию по работе с этим инструментом:
Готово! Мы удалили все лишние пробелы при помощи функции TRIM (СЖПРОБЕЛЫ). К сожалению, этот способ занимает много времени, особенно, когда таблица достаточно велика.
Замечание: Если после применения формулы Вы по-прежнему видите лишние пробелы, скорее всего в тексте содержатся неразрывные пробелы. Как их удалить, Вы можете узнать из этого примера .
Применяем инструмент «Найти и заменить», чтобы удалить лишние пробелы между словами
Этот вариант требует меньше действий, но позволяет удалить только лишние пробелы между словами. Начальные и конечные пробелы будут также отсечены до 1, но не удалены полностью.
Удаляем все пробелы между числами
Предположим, у Вас есть таблица с числами, в которых группы разрядов (тысячи, миллионы, миллиарды) разделены пробелами. В этом случае Excel рассматривает числа как текст и никакая математическая операция выполнена быть не может.
Простейший способ избавиться от лишних пробелов – это воспользоваться стандартным инструментом Excel – Find & Replace (Найти и заменить).
Удаляем все пробелы при помощи формулы
Возможно, Вам пригодится способ удалить все пробелы при помощи формулы. Для этого Вы можете создать вспомогательный столбец и ввести такую формулу:
SUBSTITUTE(A1," ","")
=ПОДСТАВИТЬ(A1;" ";"")
Здесь A1 – это первая ячейка столбца с числами или словами, в которой должны быть удалены все пробелы.
Лишние пробелы в тексте не красят любой документ. Особенно их не нужно допускать в таблицах, которые предоставляются руководству или общественности. Но даже, если вы собираетесь использовать данные только в личных целях, лишние пробелы способствуют увеличению объема документа, что является негативным фактором. Кроме того, наличие подобных лишних элементов затрудняет поиск по файлу, применение фильтров, использование сортировки и некоторых других инструментов. Давайте выясним, какими способами их можно быстро найти и убрать.
Сразу нужно сказать, что пробелы в Excel могут быть разного типа. Это могут быть пробелы между словами, пробел в начале значения и в конце, разделители между разрядами числовых выражений и т.д. Соответственно и алгоритм их устранения в этих случаях различный.
Способ 1: использование инструмента «Заменить»
С заменой двойных пробелов между словами на одинарные в Экселе отлично справляется инструмент «Заменить» .
Таким образом, мы избавились от лишних двойных пробелов между словами в документе.
Способ 2: удаление пробелов между разрядами
В некоторых случаях между разрядами в числах устанавливаются пробелы. Это не ошибка, просто для визуального восприятия больших чисел именно такой вид написания более удобен. Но, все-таки далеко не всегда это приемлемо. Например, если ячейка не отформатирована под числовой формат, добавление разделителя может негативно отразиться на корректности вычислений в формулах. Поэтому актуальным становится вопрос об удалении таких разделителей. Эту задачу можно выполнить при помощи все того же инструмента «Найти и заменить» .
Разделения между разрядами будут удалены, а формулы начнут вычисляться корректно.
Способ 3: удаление разделителей между разрядами путем форматирования
Но бывают ситуации, когда вы четко видите, что на листе разряды разделены в числах пробелами, а поиск не дает результатов. Это говорит о том, что в данном случае разделение было выполнено путем форматирования. Такой вариант пробела не повлияет на корректность отображения формул, но в то же время некоторые пользователи считают, что без него таблица будет выглядеть лучше. Давайте рассмотрим, как убрать такой вариант разделения.
Так как пробелы были сделаны с помощью инструментов форматирования, то только при помощи этих же инструментов их можно убрать.
Способ 4: удаление пробелов с помощью функции
Инструмент «Найти и заменить» отлично подходит для удаления лишних пробелов между символами. Но что делать, если их нужно убрать в начале или в конце выражения? В этом случае на помощь придет функция из текстовой группы операторов СЖПРОБЕЛЫ .
Данная функция удаляет все пробелы из текста выделенного диапазона, кроме одиночных пробелов между словами. То есть, она способна решить проблему с пробелами в начале слова в ячейке, в конце слова, а также удалить двойные пробелы.
Синтаксис этого оператора довольно прост и имеет всего один аргумент:
СЖПРОБЕЛЫ(текст)
В качестве аргумента «Текст» может выступать, как непосредственно текстовое выражение, так и ссылка на ячейку в котором оно содержится. Для нашего случая как раз последний вариант и будет рассматриваться.
- Выделяем ячейку, расположенную параллельно столбцу или строке, где следует убрать пробелы. Кликаем по кнопке «Вставить функцию» , расположенную слева от строки формул.
- Запускается окно Мастера функций. В категории «Полный алфавитный перечень» или «Текстовые» ищем элемент «СЖПРОБЕЛЫ» . Выделяем его и жмем на кнопку «OK» .
- Открывается окошко аргументов функции. К сожалению, данная функция не предусматривает использования в качестве аргумента всего нужного нам диапазона. Поэтому устанавливаем курсор в поле аргумента, а затем выделяем самую первую ячейку диапазона, с которым мы работаем. После того, как адрес ячейки отобразился в поле, жмем на кнопку «OK» .
- Как видим, содержимое ячейки отобразилось в области, в которой находится функция, но уже без лишних пробелов. Мы выполнили удаление пробелов только для одного элемента диапазона. Чтобы удалить их и в других ячейках, нужно провести аналогичные действия и с другими ячейками. Конечно, можно проводить с каждой ячейкой отдельную операцию, но это может отнять очень много времени, особенно, если диапазон большой. Существует способ значительно ускорить процесс. Устанавливаем курсор в нижний правый угол ячейки, в которой уже содержится формула. Курсор трансформируется в небольшой крестик. Он носит название маркера заполнения. Зажимаем левую кнопку мыши и тянем маркер заполнения параллельно тому диапазону, в котором нужно убрать пробелы.
- Как видим, после этих действий образуется новый заполненный диапазон, в котором находится все содержимое исходной области, но без лишних пробелов. Теперь перед нами стоит задача заменить преобразованными данными значения исходного диапазона. Если мы выполним простое копирование, то скопирована будет формула, а значит, вставка произойдет некорректно. Поэтому нам нужно произвести только копирование значений.
Выделяем диапазон с преобразованными значениями. Жмем на кнопку «Копировать» , расположенную на ленте во вкладке «Главная» в группе инструментов «Буфер обмена» . Как альтернативный вариант можно после выделения набрать сочетание клавиш Ctrl+C .
- Выделяем исходный диапазон данных. Кликаем по выделению правой кнопкой мыши. В контекстном меню в блоке «Параметры вставки» выбираем пункт «Значения» . Он изображен в виде квадратной пиктограммы с цифрами внутри.
- Как видим, после вышеописанных действий значения с лишними пробелами были заменены идентичными данными без них. То есть, поставленная задача выполнена. Теперь можно удалить транзитную область, которая была использована для трансформации. Выделяем диапазон ячеек, в которых содержится формула СЖПРОБЕЛЫ . Кликаем по нему правой кнопкой мыши. В активировавшемся меню выбираем пункт «Очистить содержимое» .
- После этого лишние данные будут убраны с листа. Если в таблице существуют другие диапазоны, которые содержат лишние пробелы, то с ними нужно поступить по точно такому же алгоритму, какой был описан выше.
Как видим, существует целый ряд способов быстрого удаления лишних пробелов в Экселе. Но все эти варианты реализуются при помощи всего двух инструментов – окна «Найти и заменить» и оператора СЖПРОБЕЛЫ . В отдельном случае можно использовать также форматирование. Не существует универсального способа, который было бы максимально удобно использовать во всех ситуациях. В одном случае будет оптимальным использовать один вариант, а во втором – другой и т.д. Например, с удалением двойного пробела между словами быстрее всего справится инструмент «Найти и заменить» , а вот убрать пробелы в начале и в конце ячейки корректно сможет только функция СЖПРОБЕЛЫ . Поэтому о применении конкретного способа пользователь должен принимать решение самостоятельно с учетом ситуации.
