Repeater-zone.ru

ПК Репитер
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Удаление лишних пробелов функцией СЖПРОБЕЛЫ (TRIM) и формулами

Удаление лишних пробелов функцией СЖПРОБЕЛЫ (TRIM) и формулами

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

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

clean-text2.png

Просто и красиво. Но есть пара нюансов.

Неразрывные пробелы не удаляются

Чайной ложкой дегтя тут представляется только невозможность удалять таким способом неразрывные пробелы. Это особый вариант пробела (внешне неотличимый от обычного), на котором не бывает переноса строки. Обычно его используют в случаях типа «г. Москва» или «Иванов И.И.», чтобы эти фразы не разбивались между строчками. В Microsoft Word такой спецсимвол вводится сочетанием клавиш Ctrl+Shift+Пробел и отображается в виде кружка, а не точки:

Неразрывный пробел

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

Формулы для удаления начальных и концевых пробелов

Если необходимо удалить только начальные пробелы (т.е. не трогать концевые и пробелы между словами), то придется делать это хитрыми формулами, т.к. готовой функции (по аналогии с функцией LTrim в VBA) в Microsoft Excel, к сожалению, нет.

Формула для удаления лишних пробелов только слева будет выглядеть так:

Удаление лишних пробелов слева формулой

В английской версии =MID(A1;FIND(LEFT(TRIM(A1));A1);LEN(A1))

Формула для удаления лишних пробелов только справа будет чуть посложнее и должна вводиться уже как формула массива (с Ctrl+Shift+Enter):

Удаление лишних пробелов справа

В английском варианте это будет =LEFT(A1;MAX((MID(A1&REPT(» «;99);ROW(A1:A99),1);» «)*ROW(A1:A99)))

Ссылки по теме

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

Пробел в начале строки: =ЕСЛИ(ЛЕВСИМВ(А1;1)=» «;ПРАВСИМВ(А1;ДЛСТР(А1)-1))
Пробел в конце строки: =ЕСЛИ(ПРАВСИМВ(А1;1)=» «;ЛЕВСИМВ(А1;ДЛСТР(А1)-1))

А еще так можно .
Для удаления пробелов в начале и в конце строки
можно использовать функцию =СЦЕПИТЬ(). Допустим, ячейка A1 проблемная ,у неё пробел вначале и в конце, нам это не надо.
Вставляем новый столбец и обрабатываем формулой =СЖПРОБЕЛЫ(A1), далее Выделить — Вст а вить зн а чения, теперь повторяющихся пробелов не будет, но в начале и в конце они остались.
Вставим еще один столбец, а в столбец формулу =СЦЕПИТЬ(«&&»;A1;»&&») , тут главное чтобы && не встречался в тексте. Опять Выделить — Вст а вить зн а чения . Теперь у нас в начале и конце строки не пробел, а дважды амперсанд+ пробел.
Далее Ctrl+F ищем &&[пробел] — заменить ничем, опять ищем [пробел]&& — заменить ничем, там где были пробелы они будут удалены вместе с амперсандами, там где пробелов не было остались наши &&
Проводим последний Поиск -замену Ctrl+F На й ти && Заменит ь ничем.

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

© Николай Павлов, Planetaexcel, 2006-2021
info@planetaexcel.ru

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

За изображения спасибо Depositphotos.com

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРН 310633031600071

Как работает функция TRIM

Синтаксис для функции TRIM:

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

  • Фактический текст, заключенный в кавычки
  • Ссылка на ячейку в месте расположения текстовых данных на листе

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

Например, если текст в ячейке A1 имеет вид:

Функция Excel = TRIM (A1) даст следующий результат:

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

  • Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
  • Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
  • Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
  • Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
  • Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Читать еще:  Как исправить ошибку 0x80070005 в Windows 7

стрелка синий правый пузырь Удалите лишние пробелы между словами с помощью TRIM в Excel

Популярные

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

В Excel вы можете удалить лишние пробелы между словами с помощью TRIM. Пожалуйста, сделайте следующее:

Шаг 1. Введите формулу = ОБРЕЗАТЬ (A1) в ячейке B1, которая находится рядом с ячейкой A1, см. снимок экрана:

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

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

стрелка синий правый пузырь Удалите лишние пробелы между словами с помощью VBA

С помощью VBA вы также можете удалить лишние пробелы между словами.

1. Держать ALT кнопку и нажмите F11 на клавиатуре, чтобы открыть Microsoft Visual Basic для приложений окно.

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

VBA: удалить лишние пробелы между словами:

3. Нажмите Запустите или нажмите F5 ключ для запуска VBA. Появится диалоговое окно с напоминанием о выборе диапазона, в котором вы хотите удалить лишние пробелы, см. Снимок экрана:

4. Затем нажмите OK, из ячеек удалены лишние пробелы. Смотрите скриншот:

стрелка синий правый пузырь Удалите лишние пробелы между словами с помощью Kutools for Excel

Есть Kutools for Excel установлен, с Удалить пробелы функция, вы можете быстро и легко удалить лишние пробелы между словами.

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Получить сейчас

Пожалуйста, примените Удалить пробелы функция, нажав Kutools > Текстовые инструменты > Удалить пробелы. Смотрите скриншот:

Шаг 1. Выберите диапазон, из которого вы хотите удалить лишние пробелы в ячейках.

Шаг 2. Нажмите Kutools > Текстовые инструменты > Удалить пробелы и появится диалоговое окно. Смотрите скриншот:

Шаг 3. Проверьте Все лишние места в Тип пространства опцию.

Шаг 4. Нажмите Ok or Применить чтобы убрать все лишние пробелы в ячейках.

Kutools for Excel‘s Удалить пробелы функция также может удалять только начальные пробелы, конечные пробелы, начальные и конечные пробелы или все пробелы. Щелкните здесь, чтобы узнать больше об удалении пробелов.

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

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

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

Различные типы пространств

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

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

  • Наборы данных могут содержать дополнительные промежуточные пробелы, то есть дополнительные пробелы между текстом.

  • Наборы данных также могут содержать разрывы строк с дополнительным пробелом.

Мы можем быстро и легко избавиться от этих типов пробелов с помощью следующих опций:

  • НАЙТИ И ЗАМЕНИТЬ
  • Функция TRIM
  • ЗАМЕНЯЮЩАЯ ФУНКЦИЯ
  • Функция CLEAN и TRIM для удаления лишних пробелов в строке

Функция TRIM

Функция TRIM — это встроенная встроенная функция, разделенная на текстовые функции. Удаляет все лишние пробелы из текста, кроме одного пробела между словами в Excel.

Синтаксис или формула для функции TRIM в Excel:

Функция обрезки в Excel имеет только один обязательный аргумент или параметр, т.е. текст.

  • Текст: это содержимое ячейки, откуда вам нужно удалить лишние пробелы в Excel.

Функция TRIM удаляет только символ пробела ASCII (32) из ​​текста.

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

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

Читать еще:  Как убрать лишние предметы в Фотошопе

Вы можете скачать этот шаблон Excel Spaces здесь — Удалить шаблон Excel Spaces

Пример № 1 — Удалить пробелы Excel с помощью Find и Replace

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

Для справки я выполнил подсчет количества символов для наборов данных столбца D. С помощью функции LEN в столбце E. я перенес эти необработанные данные в другой новый столбец (столбец H), чтобы применить задачу « Найти и заменить» .

Теперь мне нужно выбрать диапазон ячеек, в котором необходимо применить параметр Find and Replace, чтобы удалить лишние пробелы в Excel.

Теперь, чтобы активировать опцию поиска и замены, нажмите сочетание клавиш Ctrl + H, появится окно поиска и замены.

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

Он заменяет все пустые места в ячейке.

Вы можете найти разницу в количестве символов между обоими наборами данных. Где найти и заменить удалить все пустые места. В дополнительном контенте между пробелами он также удалил все пробелы, не сохраняя один пробел между словами (это можно исправить с помощью функции TRIM).

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

Пример №2 — удаление пробелов в Excel с помощью функции TRIM

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

Для справки я выполнил подсчет количества символов для наборов данных столбца D. С помощью функции LEN в столбце E. я перенес эти необработанные данные в другой новый столбец (столбец G), чтобы применить функцию TRIM . Функция Excel TRIM удаляет все пробелы из текста, кроме одного пробела между словами.

Теперь, в ячейке H4, давайте применим функцию TRIM .

На вкладке « Формулы » щелкните раскрывающееся меню « Текст» в разделе « Функции » и выберите « TRIM».

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

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

Точно так же функция TRIM применяется к другому диапазону, выделив ячейку H4, перетащите ее до H9 и нажмите Ctrl + D, чтобы применить ее к остальным ячейкам.

Вы можете найти разницу в количестве символов между обоими наборами данных. Функция TRIM удаляет все пробелы до и после текста в ячейке (т. Е. G4, G5, G6 и G7) и последовательные пробелы в середине строки в ячейке G8 & G9.

Пример № 3 — удаление пробелов в Excel с помощью функции SUBSTITUTE

Это удалит все лишние пробелы Excel, включая одиночные пробелы между словами или числами.

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

Для справки я выполнил подсчет количества символов для наборов данных столбца D. С помощью функции LEN в столбце E. я перенес эти необработанные данные в другой новый столбец (столбец G), чтобы применить функцию SUBSTITUTE .

Теперь, в ячейке H4, давайте применим функцию ЗАМЕНИТЕЛЯ .

На вкладке « Формулы » щелкните раскрывающееся меню « Текст» в разделе «Функции» и выберите « ЗАМЕНИТЬ».

Теперь появляется диалоговое окно аргумента функции:

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

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

ЗАМЕНА Функция применяется к другому диапазону, выбрав ячейку H4, перетащите ее до H9 и нажмите Ctrl + D, чтобы применить также и другие ячейки.

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

Что нужно помнить об Excel Удалить пробелы

  • Поскольку дополнительный пробел также является символом, следовательно, в кодировке ASCII (американский стандартный код для обмена информацией) он имеет кодовый номер 32.
  • Иногда данные отображаются в виде разрывов строк, которые могут содержать лишние пробелы между словами. Чтобы удалить эти лишние пробелы, мы можем объединить функции Excel TRIM и CLEAN для удаления лишних пробелов строки.
Читать еще:  Как убрать упрощенный стиль в Windows 7

Рекомендуемые статьи

Это руководство по удалению пробелов в Excel. Здесь мы обсудим, как удалить лишние пробелы в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

Способ 4: Изменение формата ячейки

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

  1. Выделите требуемые ячейки в таблице с зажатой левой кнопкой мыши.
  2. Нажмите по ним ПКМ и в появившемся контекстном меню отыщите пункт «Формат ячеек».
  3. Через панель слева перейдите на формат «Числовой» или тот, который изначально был задан для выбранных ячеек.
  4. Снимите галочку с пункта «Разделитель групп разрядов» и сохраните изменения.

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

Помогла ли вам эта статья?

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

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

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

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

  • text (текст) – текст из которого нужно удалить лишние пробелы. Он может быть введен вручную, быть ссылкой на ячейку с текстом. Например, если нам нужно удалить лишние пробелы из текста в ячейке “А1”, то нам поможет формула: =TRIM(A1) или =СЖПРОБЕЛЫ(A1) .

Пример 1. Убираем лишние пробелы перед словами, после слов, двойные пробелы

На примере ниже текст наполненный массой лишних пробелов.

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

С помощью функции СЖПРОБЕЛЫ мы легко уберем все лишнее:

=TRIM(A1) – английская версия

=СЖПРОБЕЛЫ(A1) – русская версия

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

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

Пример 2. Корректно подсчитываем количество слов в тексте с учетом лишних пробелов

Пробелы это хороший индикатор при подсчете слов в тексте.

Например, если нам нужно понять, какое количество слов во фразе “Как посчитать количество слов в тексте?” то нам поможет формула:

Суть формулы заключается в том, что она считает количество пробелов во фразе и вычитывает “1”, таким образом получая количество слов в тексте.

Если в тексте есть лишние пробелы, то формула будет считать слова не корректно.

Избежать ошибки и убрать лишние пробелы мы можем с помощью функции СЖПРОБЕЛЫ :

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Способ 4: с помощью специальной функции

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

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

удалить лишние пробелы excel

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector