Как удалить пробел в конце ячейки в экселе

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

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

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

Чайной ложкой дегтя тут представляется только невозможность удалять таким способом неразрывные пробелы. Это особый вариант пробела (внешне неотличимый от обычного), на котором не бывает переноса строки. Обычно его используют в случаях типа «г. Москва» или «Иванов И.И.», чтобы эти фразы не разбивались между строчками. В 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)))

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

• Подсветка ячеек с лишними пробелами условным форматированием
• Зачистка текста от лишних пробелов, непечатаемых символов, английских букв и т.д.
• Как проверить текст на соответствие заданному шаблону (маске)

Удалить лишние пробелы и пробелы в начале и конце строки

× The language used in menus and dialogs (Язык, используемый в меню и диалоговых окнах) in ASAP Utilities can be set to «Русский (Russian)».

Текст › 11. Удалить лишние пробелы и пробелы в начале и конце строки

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

Например, значение ‘ Lennox Street 141’ будет преобразовано в ‘Lennox Street 141’.
Вы можете использовать эту утилиту вместо функции Excel =TRIM ().

Снимки экрана

Пример снимка экрана: 1 Before (Вот версия на английском языке.)

Пример снимка экрана: 2 Strip the leading, trailing and duplicated spaces (Вот версия на английском языке.)

Пример снимка экрана: 3 After (Вот версия на английском языке.)

Practical tricks on how this can help you

• Tip: Quickly remove spaces from the beginning and end of your data
• Tip: Get rid of unwanted spaces in your data

Запуск этой утилиты

• Нажмите кнопку ASAP Utilities › Текст › 11. Удалить лишние пробелы и пробелы в начале и конце строки
• Укажите сочетание клавиш: ASAP Utilities › Избранное и сочетания клавиш › Редактировать избранное и сочетания клавиш.

Additional keywords for this tool:
удалить, двойной пробел, дополнительные пробелы, убрать чрезмерное количество пробелов, лишние пробелы, последовательный пробел, уменьшить количество пробелов до одного, пустое пространство, удаляет лишние пробелы между двумя словами в текстовой строке, =TRIM(), лишние пробелы, очистить ненужные ячейки, исправить пустые ячейки, которые Excel не распознает как пустые, исправить пустые ячейки, пустые, удалить нежелательный пробел из ячейки

Убрать пробелы в ячейке Excel: двойные пробелы, в числах, в начале и конце

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

Именно поэтому нужно сразу же убрать лишние пробелы в ячейках Excel, как только они начали появляться. Нужно понимать, что пробелы могут быть разного типа – между слов, в начале или в конце. Еще более серьезные пробелы можно увидеть между цифрами или разрядами числа. В таком случае вы можете столкнуться с проблемами при арифметических подсчетах. И ладно, если будет вылезать ошибка, а что, если подсчеты будут неверные. В статье мы рассмотрим несколько способов. Поехали!

Способ 1: Замена

1. Перейдите на вкладку «Главная».

1. Теперь в правом верхнем углу окна в разделе «Редактирования» нажмите по значку лупы – далее из выпадающего меню выбираем инструмент «Заменить».

1. В первой строке ставим текстовый курсор, нажав левую кнопку мыши и два раза жмем на «Пробел». Как вы уже догадались в нижней строке нужно поставить только один «Пробел». Теперь жмем по кнопке «Заменить все».

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

Таким же образом можно заменять любые символы, например, звездочку (*) на пробел или тире (-). Очень полезная функция, которая есть во всех программах, работающих с текстом, символами и цифрами. Она также есть и в Word.

Способ 2: Удаление пробела между цифрами

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

ПРИМЕЧАНИЕ! Новые версии Excel спокойно работают с такими цифрами, поэтому перед удалением пробелов проверьте – работают ли формулы вычисления с такими ячейками. Если они работают, то удалять их не нужно.

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

1. Открываем тот же самый инструмент.

1. Теперь в первую строку вводим только один пробел, а вот со второй нужно все стереть с помощью кнопки «Backspace».

1. После замены пробелы должны удалиться.

Если вы видите сообщение – «Мы не нашли ничего, что нужно было заменить» – значит данные пропуски не воспринимаются программой как пробелы и просто визуально разграничивают числа для удобства. С ними можно спокойно работать при любом вычислении.

Если же они вам не нравятся, то их можно убрать с помощью настроек отображения.

1. Выделяем эти ячейки.
2. Жмем правой кнопкой и заходим в «Формат ячейки».

1. В настройках в «Числовом» разделе просто уберите галочку «Разделитель групп разрядов».

1. В конце нажмите «ОК».

Способ 3: Использование функции

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

Дополнительно она удаляет двойные или тройные пробелы из всех ячеек. Вместо звездочек (***) мы будем использовать или просто текст, или адрес ячейки.

1. Выделите ячейку, параллельную той, которую вы хотите исправить.
2. Нажмите по кнопке «Функции» рядом со строкой значения.

1. Выберите «Текстовую» категорию и установите нужную нам функцию.
2. Жмем «ОК».

1. Теперь нам нужно установить адрес – выделите адрес ячейки.

1. Теперь растяните формулу на другие ячейки.

1. Теперь нам нужно скопировать ( Ctrl + C ) все исправленные значения в старые ячейки ( Ctrl + V ). При вставке выберите режим «Значения».

1. Эти данные можно удалить – кликаем правой кнопкой по выделению и выбираем команду «Очистить содержимое».

Способ не такой удобный, но зато помогает избавиться от лишних пропусков в начале и конце строки. На этом все, дорогие друзья портала WiFiGiD.RU. Надеюсь, урок вам понравился и был для вас полезным. Пишите свои дополнения и вопросы в комментариях. Всем добра!

Как удалить пробелы в ячейках Excel

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

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

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

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

Как убрать пробелы в Excel при помощи «Найти и заменить»

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

1. Удаление двойного интервала.

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

Следовательно, мы находим и заменяем двойные интервалы независимо от их положения.

Вот как это сделать:

• Выберите ячейки, из которых вы хотите их удалить.
• Перейдите в главное меню -> Найти и выбрать -> Заменить (также можно использовать сочетание клавиш — CTRL + H).
• В диалоговом окне «Найти и заменить» введите:
  • Находим: двойной пробел.
  • Заменить на: Single.

  Нажмите «Заменить все».

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

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

  1. Чтобы удалить все пробелы в тексте, выполните следующие действия:
  • Выделите нужные ячейки.
  • Перейдите на главную -> Найти и выбрать -> Заменить. (Вы также можете использовать сочетание клавиш — CTRL + H).
  • В диалоговом окне «Найти и заменить» введите:

  Находка: единое пространство.

  Заменить на: оставьте это поле пустым.

  • Нажмите «Заменить все».

  Это удалит все пробелы в выбранном диапазоне.

  Таких манипуляций с текстовыми данными делать не стоит, а вот для чисел вполне подходит. Битовые промежутки часто возникают при импорте данных из других программ через файл .csv. Правда, на рисунке видно, что числа по-прежнему написаны в виде текста, но их внешний вид стал аккуратнее. Как превратить их в действительные числа — поговорим дальше отдельно.

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

  Если ваш набор данных содержит лишние пробелы, TRIM может помочь вам удалить их все одним махом — ведущие, конечные и промежуточные кратные, оставляя только пробел между словами.

  Стандартный синтаксис очень прост:

  Где A2 — ячейка, из которой вы хотите удалить.

  Как показано на скриншоте ниже, TRIM успешно удалил все до и после текста, а также лишний интервал в середине строки.

  И теперь вам просто нужно заменить значения в исходном столбце на новые. Самый простой способ сделать это — использовать Специальная вставка> Значения.

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

  В некоторых ситуациях вы можете ввести двойной или даже тройной интервал, чтобы сделать ваши данные более читабельными. Однако вам нужно удалить ведущие пробелы (которые находятся в начале), например:

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

  = MID (A2; НАЙТИ (MID (ОБРЕЗАТЬ (A2); 1,1); A2); DLSTR (A2))

  Это выражение сначала вычисляет позицию первого символа в строке. Затем вы передаете это число в другую функцию MID, чтобы она возвращала всю текстовую строку (длина строки вычисляется с помощью DLSTR), начиная с позиции первого символа.

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

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

  = LEFT (A2; MAX ((MID (A2 & REPEAT («»; 99); LINE (A2: A100); 1) «») * LINE (A2: A100)))

  И обратите внимание, что его нужно ввести как формулу массива (с помощью Ctrl + Shift + Enter). В столбце A выравнивание по правому краю было недопустимым из-за разного количества конечных пробелов в каждой ячейке. Столбец B решил эту проблему и может хорошо организовать текст.

  Как удалить разрывы строк и непечатаемые символы

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

  TRIM может удалять пробелы, но не может удалять непечатаемые символы. Технически он предназначен для удаления только 7-битного значения 32 ASCII, что в точности соответствует пробелу.

  Чтобы удалить непечатаемые символы в строке, используйте ее вместе с функцией CLEAN. Как следует из названия, PECHSIMV предназначен для очистки ненужных «мусорных» данных и может удалить любой из первых 32 непечатаемых символов в 7-битном наборе ASCII (значения от 0 до 31), включая разрыв строки (значение 10).

  Предполагая, что данные, подлежащие очистке, находятся в ячейке A2, формула будет иметь следующий вид:

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

  • Используйте инструмент Excel «Заменить все»: в поле «Найти» введите возврат каретки, нажав Ctrl + J. В поле «Заменить» введите пробел. При нажатии кнопки «Заменить все» все разрывы строк в выбранном диапазоне заменяются пробелами.
  • Используйте следующую формулу для замены возврата каретки (код 13) и перевода строки (код 10) на пробелы:

  = ОБРЕЗАТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (A2; СИМВОЛ (13); «»); СИМВОЛ (10); » «))

  Как видите, почтовый адрес в столбце C выглядит вполне разборчивым.

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

  Если после использования формулы TRIM и PECHSIMV все еще остались некоторые устойчивые символы, скорее всего, вы скопировали / вставили данные из Интернета или другой программы, и несколько неразбитых пробелов все еще закрались в вашу таблицу.

  Чтобы исключить неразрывные пробелы (html-код), замените их обычными, а затем попросите функцию TRIM удалить их:

  Чтобы лучше понять логику, давайте разберем формулу:

  • Неразрывное пространство имеет 7-битный код ASCII 160, поэтому вы можете определить его с помощью SYMBOL (160).
  • Функция REPLACE используется для преобразования неразрывных пробелов в обычные пробелы.
  • Наконец, введите ЗАМЕНА в ОБРЕЗКУ, чтобы окончательно удалить все ненужные элементы.

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

  На следующем снимке экрана показана разница в результатах:

  Как удалить определенный непечатаемый символ

  Если взаимодействие трех функций, описанных в предыдущем примере, не привело к удалению всего мусора из текста, то оставшиеся символы представляют собой значения ASCII, отличные от 0–32 (непечатаемые символы) или 160 (непечатаемые символы) расставаться).

  В этом случае используйте функцию CODE, чтобы сначала определить код мешающего символа, а затем используйте SUBSTITUTE, чтобы заменить его обычным пробелом. А затем используйте ОТДЕЛКУ, чтобы удалить его.

  Предполагая, что нежелательные символы, от которых вы хотите избавиться, находятся в ячейке A2, напишите два выражения:

  1. В ячейке C2 определите код флага проблемы, используя одну из следующих функций:
     • Ведущий пробел или непечатаемый символ в начале строки:
  • Завершающий пробел или непечатаемый символ в конце строки:
  • Пробел или непечатаемый символ в центре строки, где n — позиция проблемного символа:

  В этом примере у нас есть неизвестный символ в центре текста, в 11-й позиции, и мы определим его код:

  = КОД (MID (A2; 11; 1))

  Получаем значение 127 (см. Скриншот ниже).

  1. В ячейке C3 замените CHAR (127) обычным пробелом («»), а затем удалите его:

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

  = ОБРЕЗАТЬ (ПЕЧАТЬ ((ЗАМЕНИТЬ (ЗАМЕНИТЬ (A2; CH (127); «»); CH (160);» «))))

  Результат должен выглядеть примерно так:

  Мы использовали эту универсальную формулу. Как видите, успех.

  Как удалить все пробелы

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

  Чтобы удалить все пробелы сразу, используйте SUBSTITUTE, как описано в предыдущем примере, за исключением того, что вы заменяете символ пробела, возвращаемый CHAR (32), ничем («»):

  Или вы можете просто ввести его («») в формулу, например:

  В результате появится текст, состоящий из цифр. Если в результате вам нужны числа, добавьте два знака «-» (минус) перед формулой. Любая математическая операция автоматически преобразует числа в числа. А дважды применив минус, то есть дважды умножив на минус 1, мы не изменим значение числа и его знак.

  Как посчитать пробелы в Excel

  Чтобы узнать общее количество пробелов в ячейке, выполните следующие действия:

  • Вычислить всю длину строки с помощью функции DLSTR: DLSTR (A2)
  • Замените все пробелы ничем: SUBSTITUTE (A2; » «; «»)
  • Вычислите длину строки без пробелов: DLSTR (SUBSTITUTE (A2; » «; «»))
  • Вычтите этот результат из исходной длины.

  Предполагая, что исходная текстовая строка находится в ячейке A3, полная формула выглядит следующим образом:

  Чтобы узнать, сколько лишних пробелов в ячейке, получите длину текста без них, а затем вычтите ее из исходной длины:

  На рисунке показаны обе формулы в действии:

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

  Простой способ удаления пробелов без формул.

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

  Пользователи Excel, которые ценят свое время и удобство, могут воспользоваться текстовыми инструментами, включенными в надстройку Ultimate Suite для Excel. Один из этих удобных инструментов позволяет удалять пробелы и непечатаемые символы одним нажатием кнопки.

  После установки Ultimate Suite добавляет на ленту Excel несколько полезных кнопок, таких как «Удалить пробелы», «Удалить символы», «Преобразовать текст» и т.д.

  Если вы хотите удалить лишние пробелы в таблицах Excel, выполните следующие 4 быстрых шага:

  1. Выделите ячейки (диапазон, весь столбец или строку), в которых вы хотите удалить лишние пробелы.
  2. Нажмите кнопку «Вырезать пробелы» на вкладке «Данные Ablebits».
  3. Выберите один или несколько вариантов:
     • Вырежьте начальные и конечные пробелы.
     • Удалите лишние пробелы между словами, кроме одного.
     • Убрать неразрывные пробелы;)
     • Удалите ненужные разрывы строк до и после значений, оставьте только одно из значений.
     • Удалите все разрывы строк в ячейке.
  4. Нажмите кнопку «Вырезать» .

  Готово! Все лишние пробелы удаляются одним щелчком мыши:

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

  Похожие публикации:

  1. Как округлить число в с
  2. Как переделать питание от батареек на питание от сети
  3. Как создать календарь в ноушен
  4. Что значит увеличить в 2 раза