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

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

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

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

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

Следовательно, таблица выглядит малость неопрятно и становится трудной для использования. Казалось бы, простая задача может стать непростой. Например, найти покупателя с именем John Doe (нет лишних пробелов между частями имени), в то время как в таблице он сохранён как «John Doe«. Или числа, которые не могут быть просуммированы, а виноваты в этом опять лишние пробелы.

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

Меню Правка – Редактор Notepad++

Отмена – Позволяет отменить внесенные изменения в документ, комбинация «Ctrl + Z».

Повтор – Позволяет повторить действия, отмененные с помощью пункта Отмена, комбинация «Ctrl + Y».

Вырезать – Вырезать выделенный фрагмент, вырезанный фрагмент будет храниться в памяти, комбинация «Ctrl + X»

Копировать – Скопировать выделенный фрагмент в буфер обмена, комбинация «Ctrl + C».

Вставить – Вставить данные, которые хранятся в буфере обмена, комбинация «Ctrl + V».

Удалить – Позволяет удалить выделенный фрагмент, клавиша «DEL».

Выделить все – Выделяет весь текст, содержащийся в активном документе, комбинация «Ctrl + A».

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

• Копировать Путь и Имя файла
• Копировать Имя файла
• Копировать Путь к файлу

Отступы(табуляция) – Работа с табуляцией.

• Вставить табуляцию – Клавиша «Tab».
• Удалить табуляцию – Просто перемещает курсор влево на число символов, равных табуляции, комбинация «Shift + Tab».

Преобразовать регистр – Позволяет изменить регистр выделенных символов.

• Прописные – Комбинация «Ctrl + Shift + U».
• Строчные – Комбинация «Ctrl + U».

Операции со строками – Данный пункт редактора Notepad++ и его подпункты позволяют производить различные манипуляции со строками текста.

• Дублировать текущую строку – Вставляет с новой строки содержимое текущей строки (строки, в которой находится курсор), комбинация «Ctrl + D».
• Разбить строки — Комбинация «Ctrl + I».
• Объединить строки – Позволяет объединить выбранные строки в одну строку, комбинация «Ctrl + J».
• Переместить строку вверх – Комбинация «Shift + Ctrl + Up».
• Переместить строку вниз – Комбинация «Shift + Ctrl + Down».
• Удалить пустые строки – Удаляет все пустые строки в редакторе кода Notepad++, однако, если в строке содержится знак табуляции или пробел, то она не будет считаться пустой.
• Удалить пустые строки(содержащие символы Пробел) – В данном случае, будут удаляться все строки, даже те, которые содержат пробелы и символы табуляции.

Комментарии – Данный пункт содержит несколько подпунктов, которые позволяют управлять комментариями в программном коде.

• Вкл./Выкл. Комментарий строки – Комбинация «Ctrl + Q»
• Закомментировать строку — Комбинация «Ctrl + K»
• Раскомментировать строку — Комбинация «Ctrl + Shift + K»
• Закомментировать выделение — Комбинация «Ctrl + Shift + Q»

Автозавершение – Данный пункт меню содержит несколько подпунктов, которые позволяют сократить время при вводе текста:

• Завершение функций – Позволяет включить отображение выпадающего списка, в котором будут отображаться возможные варианты для вводимой функции, или любой другой программной конструкции, комбинация «Ctrl + SpaceBar»
• Завершение слов – Тут также отображается список возможных слов, которые вы хотите ввести, список формируется из слов, которые уже содержаться в документе, комбинация «Ctrl + Enter»
• Подсказка по функциям — Комбинация «Ctrl + Shift + SpaceBar»

Формат конца строки – Меню позволяет задать код-символ, которым будет завершаться строка.

• Преобразовать в Win-формат – Символ CRLF
• Преобразовать в UNIX-формат – Символ LF
• Преобразовать в MAC-формат – Символ CR

Операции с пробелами – Возможность удалить пробелы в нужных местах.

• Убрать замыкающие пробела – Пробелы, которые находятся в конце строки.
• Убрать начальные пробела – Пробелы, которые находятся вначале строки
• Убрать замыкающие пробела – Пробелы, которые находятся вначале и в конце строки.
• Символ Конец строки в Пробел – Фактически, размещает все строки в одну, так как вместо символа перевода строки мы получим пробел.
• Убрать лишние пробелы и концы строк – Тут, фактически объединены два предыдущих пункта.
• Табуляцию в пробел – Символ табуляции преобразуется в пробел
• Пробел в табуляцию (Все) – Преобразует все пробелы в символ табуляции.
• Пробел в табуляцию (Заглавные) – Преобразует только пробелы в начале строк в символ табуляции.

Редактор столбцов…. – Данный пункт меню открывает диалоговое окно Редактор столбцов, в котором можно задать текст или нумерацию, которые будут вставлены в документ в виде столбика. Вставка произойдет с того места, где находится курсор и до конца последней строки с текстом.

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

Только чтение – Включает или отключает режим, в котором можно только просматривать выбранный документ.

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

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

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

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

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

Способ 1: с помощью инструмента «Заменить»

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

1. Перейдите на вкладку «Главная».
2. На панели инструментов, в правой ее части, нажмите по кнопке «Найти и выделить». Нажмите по ней.
3. В появившемся выпадающем списке выберите пункт «Заменить».
4. Появится окно, в котором в поле «Найти» введите двойной пробел. Для этого просто дважды нажмите по соответствующей клавише.
5. В поле «Заменить на» пропишите один пробел.
6. Нажмите кнопку «Заменить все».
7. В окне с отчетом нажмите «ОК».

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

Способ 2: удаление пробелов между разрядами

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

1. В таблице выделите область, в которой находятся ненужные пробелы.
2. Находясь на главной странице на панели инструментов, нажмите по кнопке «Найти и заменить» и выберите инструмент «Заменить».
3. В появившемся окне на этот раз нужно ввести немного другие значения.
4. В поле «Найти» поставьте один пробел.
5. Поле «Заменить на» оставьте нетронутым.
6. Нажмите кнопку «Заменить все».
7. Подтвердите свои действия, ознакомьтесь с отчетом и примите его.

После этого вы обнаружите, что пробелы между числами исчезли, соответственно, поставленная задача выполнена.

Способ 3: удаление путем форматирования текста

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

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

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

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

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

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

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

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

Excel. Обнаружение лишних пробелов

Распространенная ошибка, которая встречается в электронных таблицах — это невидимые символы пробелов. 1 Ячейка B2 (рис. 1) содержит формулу, которая берет имя цвета в ячейке B1 и возвращает соответствующий код из таблицы D1: E6: = VLOOKUP (B1; D1: E6; 2; FALSE).

В следующем примере формула в ячейке B2 возвращает ошибку, указывающую, что значение Red не найдено в таблице. Сотни и тысячи пользователей Excel потратили много времени, выясняя, почему эти операции не работают. Ответ прост: в ячейке D5 написано не «Красный», а «Красный», т.е после слова следует пробел. Для Excel это две разные последовательности символов.

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

• один или несколько ведущих пробелов;
• один или несколько заключительных пробелов;
• два или более последовательных пробела в одном тексте.

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

1. Выделите все ячейки с текстом, к которым вы хотите применить условное форматирование.
2. Выполните команду Home -> Conditional Formatting -> New Rule, чтобы открыть диалоговое окно New Formatting Rule (рисунок 3).
3. В верхней части этого окна выберите параметр «Использовать формулу для определения форматированных ячеек.
4. В области «Изменить описание правила» введите формулу: = D2 TRIM (D2). В этой формуле предполагается, что ячейка D2 является верхней левой ячейкой диапазона. Если нет, замените адрес верхней левой ячейки, указанный на шаге 1.
5. Нажмите кнопку «Формат», чтобы отобразить диалоговое окно «Формат ячеек», и выберите тип форматирования, который вы хотите применить к ячейкам, содержащим дополнительные пробелы, например желтой заливкой.
6. Дважды щелкните ОК.

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

Рис. 3. Параметры условного форматирования для выделения ячеек, содержащих лишние пробелы

Функция ОБРЕЗАТЬ работает так, что формула на шаге 4 также применяет условное форматирование ко всем числовым ячейкам. Если ваш диапазон содержит числа, используйте формулу из шага 4: = ЕСЛИ (НЕ (UNETEXT (D2)); D2SPACE (D2))

Также обратите внимание, что TRIM не удаляет (и не принимает в условном форматировании) неразрывный пробел

Пробелы в числах

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

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

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

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

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

Описанная ранее функция СЖПРОБЕЛЫ прекрасно справляется с тем, чтобы убрать из ячейки пробелы, но она не подходит для очистки ее от непечатаемых символов. Фактически она нужна только для того, чтобы убирать символ с кодом 32 в ASCII таблице 7-битного разряда. Именно так кодируется этот символ. Но есть и значения с другими кодами, для которых нужно использовать функцию ПЕЧСИМВ. В английской локализации программы она называется CLEAN. Как мы можем понять из названия, с помощью этого оператора можно очистить ячейку от всевозможного хлама и удалять первые 32 непечатаемые символы.

Допустим, нам нужно убрать и пробелы, и непечатаемые символы в ячейке A2. В этом случае наша задача – использовать сначала формулу ПЕЧСИМВ для того, чтобы убрать непечатные знаки, после чего передать эту строку функции СЖПРОБЕЛЫ, которая убирает оставшиеся пробелы. Результат использования этой комбинации экселевских операторов мы видим на скриншоте.

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

1. Открываем диалоговое окно «Найти и заменить» методом, описанным выше. В поле «Найти» указываем символ возврата каретки. Для этого вводим комбинацию клавиш Ctrl + J. В свою очередь, в поле «Заменить» используем символ пробела. Когда мы нажимаем кнопку «Заменить все» все разрывы автоматически заменяются на пробелы.
2. Также можно заменять возврат каретки на пробелы с помощью этой формулы: =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2; СИМВОЛ(13);» «); СИМВОЛ(10); » «)) . Естественно, надо адрес ячейки в функции ПОДСТАВИТЬ заменить на тот, который нужен конкретно в вашей ситуации.

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

Функция СЖПРОБЕЛЫ в Excel и примеры ее использования

​или клавишу вызова​ Во втором столбце,​ покупателя с именем​ а напротив ЗАМЕНИТЬ​ вручную перепечатать нужные​ лишних пробелах.​ пробелы. Но опытные​ будет =LEFT(A1;MAX((MID(A1&REPT(» «;99);ROW(A1:A99),1);»​ обычного), на котором​у всех СЖПРОБЕЛЫ​: Может функция СЖПРОБЕЛЫ​ пустую. Нажимаем кнопку​

Синтаксис функции СЖПРОБЕЛЫ в Excel

​Запишем функцию в ячейке​ пользователи знают, что​ «)*ROW(A1:A99)))​ не бывает переноса​ удаляет пробелы согласно​ поможет.​ «Заменить все». Нажимаем​ разрывы строк, перенос​Нажимаем «ОК». Теперь формулу​Как убрать​ окно​Find What​ затем клавишу​

​Length​(нет лишних пробелов​ ничего.​ раз мы в​ D3. В качестве​

​ информация для Excel​Лишние пробелы в тексте​ строки. Обычно его​ описанию, а у​Kanoist​ кнопку «Закрыть». Остался​ текста на другую​ копируем вниз по​лишние пробелы в Excel​Find & Replace​(Найти) и один​V​, показано количество символов​

​ между частями имени),​

Пример использования функции СЖПРОБЕЛЫ

​Проблема такого способа в​ Excel, будем использовать​ аргумента введем ячейку​ часто копируется из​ замечают не все.​ используют в случаях​ Вас стерла ВСЕ. ​: , нашел какой​ один пробел. Получилось​ страницу, конец абзаца,​ столбцу всего списка.​, которые мешают работе​(Найти и заменить).​ раз в поле​(З).​ в каждом имени.​ в то время​

​ том, что он​ ВПР.​ B3, в которой​ разных источников, которые​ И не для​ типа «г. Москва»​

​logistics​ то ее вариант,​ так.​ табуляция, др.​ В столбце В​ фильтра, сортировке, сравнить​В поле​Replace With​Удалите вспомогательный столбец.​В Excel существует функция​ как в таблице​ убирает все, в​Если мы протянем формулу​ значится наименование игрушки.​

​ могли заполняться дилетантами.​ всех это, в​ или «Иванов И.И.»,​: =СЖПРОБЕЛЫ​ несколько дней назад,​

​В Excel можно выделять​Чтобы​ получился такой список.​ списки, мешают посчитать​Find What​(Заменить на).​Готово! Мы удалили все​TRIM​ он сохранён как​ том числе между​ до конца таблицы,​Теперь протянем функцию до​ Поэтому в тексте​ общем-то, важно. Но​ чтобы эти фразы​легко и просто​ она стерла все​

СЖПРОБЕЛЫ с другими функциями

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

​Kanoist​ пробелы.​ пробелами в момент​ пробел в Excel​Как убрать пробелы в​ нам нужно сравнить​ пробел. Убедитесь, что​Replace all​ помощи функции​ для удаления лишних​John Doe​ путь используют, преимущественно,​Розовым цветом мы специально​

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

​ строчками. В Microsoft​: всем спасибо.​Бахтиёр​ заполнения ячейки таблицы,​, пишем в соседнюю​ числах Excel​ два списка, но​ поле​(Заменить все), а​TRIM​ пробелов из текста.​

​«. Или числа, которые​ для удаления лишних​ подсветили те позиции,​ действительно выровнялись, потому​​ проблемой, потому что​ Word такой спецсимвол​=СЖПРОБЕЛЫ помогло​: =ЕСЛИ(ПРАВБ(A1)=» «;ПСТР(A1;1;ДЛСТР(A1)-1);A1)​ списка, бланка. Как​ ячейку такую формулу.​.​

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

​ лишние пробелы не​Replace With​ затем​(СЖПРОБЕЛЫ). К сожалению,​ Далее Вы найдёте​ не могут быть​ пробелов в составе​ которые написаны с​

​ что пробелы удалились.​На небольшом примере рассмотрим,​ при выполнении команд​ вводится сочетанием клавиш​действительно, несколько дней​=ЕСЛИ(ПРАВБ(A1)=» «;ЛЕВБ(A1;ДЛСТР(A1)-1);A1)​ для этого настроить​=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A6;СИМВОЛ(160);СИМВОЛ(32)))​Если формула не​ дадут этого сделать​

​(Заменить на) –​ОК​ этот способ занимает​ пошаговую инструкцию по​ просуммированы, а виноваты​ чисел.​ лишними пробелами. Поэтому​ Проверим это наверняка,​ как пользоваться функцией​

Как вставить пробел в ячейку из цифр и букв

Дана строка, состоящая из символов букв и цифр. Между словами вставить запятую и пробелДана строка, состоящая из символов букв и цифр. Между словами вставить запятую и пробел.

как можна запретить набор запятую, пробел. цифр, букв желательно программно

А3 содержится строка, состоящая из букв и цифр. Найти произведение нечетных цифр. Результат вывести в ячейку СВ ячейке А3 содержится строка, состоящая из букв и цифр. Найти произведение нечетных цифр.

Пробел после 2 букв или цифр

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

т.е. у меня есть столбец сформированных данных:

Маша=1000000 Саша=2000 Наташа=30000

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

Маша=1 000 000 Саша=2 000 Наташа=30 000

Нужное место я вычислю — вопрос в операторе, есть такой .

мыло: mobile_nv собака rbcmail точка ru

А почему бы не так (если позиция пробела вычислена и хранится в переменной N):

Я также и сделал:

Попробуй вот эту функцию. Она находит в тексте последовательности цифр и группирует их по 3 штуки, разделяя пробелами. Проверено!

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

О вреде лишних пробелов

Наличие лишних пробелов может помешать работе в следующих случаях:

• Требуется сравнить ячейки между собой. Это может быть как поиск дубликатов внутри диапазона, так и сравнение двух списков и поиск повторяющихся значений между ними
• При построении сводных таблиц строки «схлопываются», если являются полностью идентичными, и разница в один пробел не позволит им «схлопнуться». Алгоритм построения сводных таблиц также сравнивает ячейки между собой.
• С помощью «ВПР» или сочетания ИНДЕКС/ПОИСКПОЗ можно «подтянуть» данные из одной таблицы в другую. Эти функции тоже сравнивают ячейки при поиске и учитывают разницу в виде пробелов.

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

Excel: разделяем слипшийся текст

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

Проблема с удалением пробелов встречается и при работе с числами — в числах попадаются пробелы (к примеру, вместо разделителей разрядов), из-за чего они воспринимаются в Excel как текст. Предположим, что у нас имеется таблица с числами, где содержатся пробелы. Выделяем диапазон с данными и с помощью инструмента «Найти и Заменить» удаляем пробелы, подставив вместо одиночного пробела пустое поле, как показано в примере выше. Аналогичный результат можно получить и с помощью формулы:

При этом не забудьте умножить результат на 1, чтобы перевести формулу из текста в число.

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

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

Урок: Удаление больших пробелов в Microsoft Word

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

Пример использования функции СЖПРОБЕЛЫ

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

Невооруженным глазом видно, что некоторые позиции написаны с явными лишними пробелами. Рассмотрим, к чему это может привести.

Подсчитывать общее количество позиций по каждому виду игрушек будем через функцию СУММЕСЛИ. Вводим ее, протягиваем на остальные ячейки и смотрим результат. Программа выдала ответ по плюшевым зайцам: 3. Хотя мы видим, что их должно быть 3+2=5. В чем проблема? В лишних пробелах.

Запишем функцию в ячейке D3. В качестве аргумента введем ячейку B3, в которой значится наименование игрушки.

Теперь протянем функцию до 14 строки и увидим, что тексты действительно выровнялись, потому что пробелы удалились. Проверим это наверняка, изменив диапазон в команде СУММЕСЛИ. Вместо B3:B14 пропишем D3:D14 и посмотрим результат. Теперь плюшевых зайцев действительно 5, медведей – 6 и т.п. Получается, символы табуляции играют важную роль, и их нужно подчищать.

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

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

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

Это выражение в начале вычисляет позицию первого знака в строке. Затем вы передаете это число другой функции ПСТР, чтобы она возвращала всю текстовую строку (длина строки рассчитывается с помощью ДЛСТР), начиная с позиции первого знака.

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

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

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

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

Кроме первых двух способов есть еще один метод, в котором используется специальная формула «ПОДСТАВИТЬ», если используется англоязычная версия, то «SUBSTITUTE».

Чтобы формула заработала нужно в соседнем столбике или строчке от того, где находятся числа с пробелами, ввести следующую формулу: =ПОДСТАВИТЬ(A1;» «;»»). На место A1 вписывается нужный адрес. После этого, при необходимости, формула копируется на все остальные ячейки и заменяется таким же способом, как в предыдущем методе. Таким образом можно удалить пробелы во всем столбце.

Способы расчета суммы ячеек, столбцов или строк в Excel

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

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

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

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

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

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

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

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

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

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

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

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

Обрезать пробелы между словами до 1, удалить конечные/начальные пробелы

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

Используйте формулу Trim, чтобы удалить лишние пробелы

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

Добавьте вспомогательный столбец в конец ваших данных. Вы можете назвать его «Обрезка».
В первой ячейке вспомогательного столбца (С2), введите формулу для обрезки лишних пробелов = ОБРЕЗАТЬ (A2)
Скопируйте формулу в другие ячейки столбца. Не стесняйтесь использовать некоторые советы из статьи Введите одну и ту же формулу во все выделенные ячейки одновременно.
Замените исходный столбец столбцом с очищенными данными. Выделите все ячейки во вспомогательном столбце и нажмите Ctrl + C, чтобы скопировать данные в буфер обмена. Теперь выберите первую ячейку в исходном столбце и нажмите Shift + F10 или кнопку меню. . Затем просто нажмите В.

Удалите вспомогательный столбец. Вот и все! Мы удалили все лишние пробелы с помощью формулы trim()

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

Использование «Найти и заменить» для удаления лишних пробелов между словами

Этот вариант требует меньше шагов, но позволяет удалить только лишние пробелы между словами. Начальные и конечные пробелы также будут обрезаны до 1, но не будут удалены.

1. Выберите один или несколько столбцов с данными, чтобы удалить пробелы между словами.
2. Нажмите Ctrl + H, чтобы получить «Найти и заменить» диалоговое окно.
3. Дважды нажмите пробел в Найти то, что поле и один раз в Заменить
4. Нажми на «Заменить все«, а затем нажмите Ok чтобы закрыть диалоговое окно подтверждения Excel.
5. Повторение шаг 4 пока не появится сообщение «Нам не удалось найти замену».

3 клика, чтобы очистить данные с помощью инструмента Trim Spaces

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

Надстройка Trim Spaces очистит данные, импортированные из Интернета или любого другого внешнего источника. Он удаляет начальные и конечные пробелы, лишние пробелы между словами, неразрывные пробелы, разрывы строк, непечатаемые символы и другие нежелательные символы. Кроме того, есть возможность конвертировать слова в ПРОПИСНОЙ, нижний или правильный регистр. А если вам нужно преобразовать текстовые числа обратно в числовой формат и удалить апострофы, это тоже не будет проблемой.

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

1. Загрузите и установите пробную версию Ultimate Suite для Excel.
2. Выберите диапазон в таблице, где вы хотите удалить лишние пробелы. Для новых таблиц я обычно нажимаю Ctrl + A, чтобы сразу обработать все столбцы.
3. Перейти к Данные об аблебитах вкладку и нажмите кнопку Обрезать пробелы значок.
4. Панель надстройки откроется в левой части рабочего листа. Просто отметьте нужные галочки, нажмите кнопку «Обрезать» и наслаждайтесь идеально очищенным столом.

Разве это не быстрее, чем с двумя предыдущими советами? Если вы всегда имеете дело с обработкой данных, этот инструмент сэкономит вам часы драгоценного времени.

Удалить пробелы в ячейках Excel – Офис Ассист

Удаление лишних пробелов функцией СЖПРОБЕЛЫ (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)))

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

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

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

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

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