Удаление лишних пробелов функцией СЖПРОБЕЛЫ (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
А почему бы во всех примерах не добавить формулу в виде текста, а не в виде рисунка?
Т.е. как английский вариант, чтоб можно было удобно скопировать.
Наверх