Как убрать пробелы в Excel (эксель): СЖПРОБЕЛЫ и ПЕЧСИМВ

=СЖПРОБЕЛЫ(A2) удаляет все пробелы до и после текста в A2 и сводит каждую группу пробелов между словами к одному пробелу. " Ana Silva " превращается в Ana Silva. Это функция СЖПРОБЕЛЫ (по-английски TRIM). В таблицах формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой: =ПОДСТАВИТЬ(A2;" ";"").

ФормулыСбросить

B2fx

=TRIM(A2)

	A	B	C	D
1	Name	TRIM	Length before	Length after
2	Ana Silva	Ana Silva	14	9
3	Ben Okafor	Ben Okafor	13	10
4	Chen Wu	Chen Wu	10	7

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СЖПРОБЕЛЫ(A2)

В столбце A пробелы не видны, поэтому здесь есть столбцы с длиной: ДЛСТР (LEN) их считает. Лишние пробелы обычно приходят вместе с импортированными или вставленными данными и ломают поиск и сравнения без всяких видимых признаков.

Почему лишние пробелы ломают формулы

Для Excel Ana с пробелом в конце и Ana это разные тексты. Сравнение возвращает ЛОЖЬ, СЧЁТЕСЛИ не считает ячейку, а поиск возвращает #Н/Д (по-английски #N/A); таблицы на этой странице показывают ошибки под английскими именами:

ФормулыСбросить

D3fx

=XLOOKUP("Ana",TRIM(A2:A4),B2:B4,"not found")

	A	B	C	D
1	Name	Score	Is it Ana?	Ana's score
2	Ana	90	FALSE	not found
3	Ben	85	TRUE	90
4	Chen	78

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ПРОСМОТРX("Ana";СЖПРОБЕЛЫ(A2:A4);B2:B4;"not found")

C2 даёт ЛОЖЬ, а D2 ничего не находит (без четвёртого аргумента ПРОСМОТРX показала бы #Н/Д), потому что в A2 стоит Ana с пробелом. D3 обрезает пробелы во всём столбце поиска прямо внутри формулы и находит 90. Это работает в Microsoft 365 и Excel 2021; в старых версиях сначала очистите столбец. Пробелы одна из самых частых причин ошибки #Н/Д при поиске.

Удалить все пробелы

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

ФормулыСбросить

B2fx

=TRIM(A2)

	A	B	C
1	Code	TRIM	No spaces
2	AB 12 34	AB 12 34	AB1234
3	555 201 3344	555 201 3344	5552013344

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СЖПРОБЕЛЫ(A2)

ФормулыСбросить

B2fx

	A	B
1	Customer	Clean name
2	Eli Novak
3	Fay Ruiz
4	Gus Lee

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

Ваша очередь: В именах в столбце A есть лишние пробелы. В B2 верните имя из A2 без пробелов по краям и с одним пробелом между словами. Формула протягивается до B4.

ПроверитьПоказать ответ

СЖПРОБЕЛЫ не работает: неразрывные пробелы

Текст, скопированный с веб-страницы или из PDF, часто содержит неразрывные пробелы, символ 160, вместо обычных пробелов, символа 32. Выглядят они одинаково, но СЖПРОБЕЛЫ удаляет только символ 32, поэтому в Excel ничего не меняется:

A2:             Ana Silva   (a non-breaking space between the names and one at the end)
=LEN(A2)        10
=LEN(TRIM(A2))  10, TRIM removed nothing

В русском Excel последняя формула записывается как =ДЛСТР(СЖПРОБЕЛЫ(A2)). Решение такое: сначала превратите каждый символ 160 в обычный пробел функцией ПОДСТАВИТЬ, а остальное сделает СЖПРОБЕЛЫ. Текст в A2 ниже собран через СИМВОЛ(160), чтобы вы видели символ, который вставляет веб-страница:

ФормулыСбросить

D2fx

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

	A	B	C	D	E
1	Pasted text	Length	Code of 4th character	Fixed	Fixed length
2	Ana Silva	10	160	Ana Silva	9

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;СИМВОЛ(160);" "))

C2 показывает, как распознать странный символ: КОДСИМВ(ПСТР(A2;4;1)) возвращает код 4-го символа, здесь 160. В D2 9 символов: 10 из A2 минус пробел в конце.

ПЕЧСИМВ: удалить переносы строк и другие скрытые символы

ПЕЧСИМВ (по-английски CLEAN) удаляет непечатаемые символы с кодами от 0 до 31: переносы строк (10), возвраты каретки (13) и табуляцию (9). Пробелы она не трогает, поэтому обычно их используют в паре: =СЖПРОБЕЛЫ(ПЕЧСИМВ(A2)):

ФормулыСбросить

B2fx

=CLEAN(A2)

	A	B	C
1	Imported	CLEAN	TRIM(CLEAN)
2	Ana Silva	AnaSilva	AnaSilva

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ПЕЧСИМВ(A2)

ПЕЧСИМВ просто удаляет перенос строки, поэтому имя и фамилия сливаются: AnaSilva . Когда перенос строки разделяет слова, замените его пробелом, =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2;СИМВОЛ(10);" ")); этот случай разобран на странице о переносе строки.

Заменить исходный столбец очищенным текстом

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

1. Введите =СЖПРОБЕЛЫ(A2) в пустом столбце рядом с данными и протяните вниз.
2. Скопируйте этот столбец (Ctrl+C, на Mac Cmd+C).
3. Выделите исходный столбец и выберите Главная > Вставить > Вставить значения, чтобы в ячейки попал текст, а не формулы.
4. Удалите вспомогательный столбец.

«Найти и заменить» умеет удалять пробелы без формулы, но не отличает пробелы по краям от пробелов между словами: замена пробела пустотой склеит Ana Silva в AnaSilva. Замена двух пробелов одним, повторённая, пока Excel не перестанет находить что заменять, это ручной вариант правила СЖПРОБЕЛЫ для пробелов внутри текста. Одиночный пробел в начале или в конце при этом остаётся.