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

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



21.04.2017 15:14:21
На эту тему могу порекомендовать еще функцию ПЕЧСИМВ – она удаляет из текста всякие непечатные символы (символы которых нет на клавиатуре), а главное символ переноса строки, который вызывает много проблем при обработке данных в Excel.
Как правило, непечатные символы попадают в текст при ручном копировании данных из различных учетных систем, например 1С..
04.11.2018 15:46:29
Классная функция! СПС.
01.08.2018 11:33:58
Для удаления пробелов в начале и в конце строки можно использовать условный оператор ЕСЛИ.
До этого желательно несколько раз с помощью поиска и замены убрать двойные пробелы.

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


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