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

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

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

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

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

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

Проблема с удалением пробелов возникает и при работе с числами — в числах есть пробелы (например, вместо разделителей позиций), поэтому они воспринимаются в Excel как текст.

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

Аналогичный результат можно получить по формуле:

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

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи, поделитесь ими в комментариях.

Способ 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. Нажмите ОК».

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

Форматирование ячеек

Иногда причиной ненужных пробелов является форматирование. Возможно, он уже был повторно введен или перемещен с данными из внешнего источника. Чтобы все исправить, нужно щелкнуть правой кнопкой мыши вверху столбца и выбрать там вкладку «Формат ячеек». Появится список настроек, нужно выбрать вкладку «Число», где уже и настроить наличие битового разрыва.

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

Найти и заменить

Найти и заменить — это самый простой способ удалить пробелы в ячейке Excel. Для того, чтобы все заработало, необходимо выполнить следующий порядок действий:

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

   

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

Удаление апострофов в начале ячеек

Апостроф (‘) в начале ячейки на листе Microsoft Excel — это специальный символ, официально называемый текстовым префиксом. Необходимо дать понять Excel, что все последующее содержимое ячейки должно восприниматься как текст, а не как число. Апостроф виден только в строке формул, его не видно на листе.

Фактически, это удобная альтернатива заданию текстового формата для ячейки (Main → Number → Text) и для ввода длинных последовательностей чисел (номера банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим.

Но иногда апостроф попадает в ячейки против нашей воли (например, после загрузки из корпоративных баз данных) и начинает мешать вычислениям. Чтобы удалить его, вам понадобится небольшой макрос. Откройте редактор Visual Basic, нажав Alt + F11, вставьте новый модуль (меню «Вставка» → «Модуль») и введите его текст.

Теперь, если вы выберете диапазон на листе и запустите наш макрос (Alt + F8 или вкладка «Разработчик» → кнопка «Макрос»), апострофы перед содержимым выбранных ячеек исчезнут.

Способ 1: Формула СЖПРОБЕЛЫ

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

1. Выберите пустую ячейку, в которую будут вставлены данные после удаления пробелов.



2. Начните писать формулу со знака =, который в синтаксисе Excel представляет ее.



3. Введите имя функции ОБРЕЗАТЬ, при необходимости прочитав описание.



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



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

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

Удалить переносы строк внутри ячеек («энтеры»)

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

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

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

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

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

   

Таким образом, ненужные пробелы между цифрами или буквами будут удалены. Второй столбец (или строку) можно удалить.

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

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

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

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

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

Помимо первых двух методов, существует еще один метод, использующий специальную формулу «SUBSTITUTE», если используется английская версия, то «SUBSTITUTE».

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

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

довольно легко получить слово до первого пробела: = MID (A1; 1; FIND («»; A1) -1)

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

= MID (A1; НАЙТИ («»; A1) +1, НАЙТИ («», A1, НАЙТИ («», A1) +1) -НАЙТИ («», A1) -1)

На мой взгляд, это выглядит немного запутанно, хотя все предельно просто:

• НАЙТИ («»; A1) +1 — поиск позиции первого пробела;
• НАЙТИ («»; A1; НАЙТИ («»; A1) +1) — ищет позицию второго пробела, а затем вычитает позицию первого пробела из этой позиции (-НАЙТИ («»; A1)).

Но возникает проблема: если второго пробела нет, формула выдаст ошибку # ЗНАЧ !. Таким образом, вам также необходимо проверить наличие ошибки, что, очевидно, не добавляет элегантности формуле.

Поэтому я предпочитаю использовать эту формулу: = ПОДСТАВИТЬ (ПРАВО (MID («» & ПОДСТАВИТЬ (A1; «»; ПОВТОР («»; 999)); 1; 999 * 2); 999);» «;»»)

На первый взгляд, это намного больше кошмар, чем первый.

Но у него есть ряд преимуществ: его не нужно проверять на пробелы; поменяв номер, можно получить не второе, а 3-е, 4-е и так далее.

Разберем самое главное: чтобы получить первое слово из начала строки, нужно в блоке 999 * 2 заменить 2 на 1:

= REPLACE (RIGHT (MIDDLE («» & REPLACE (A1; «»; REPEAT («»; 999))); 1; 999 * 1); 999); «»; «») Чтобы получить пятый — умножить на 5:

= REPLACE (RIGHT (MIDDLE («» & REPLACE (A1; «»; REPEAT («»; 999))); 1; 999 * 5); 999);» «;»»)

И ЧТО БЫЛО СДЕЛАТЬ — СЛОВО ПОСЛЕ ПОСЛЕДНЕГО ПРОСТРАНСТВА

Если вдруг окажется меньше пробелов, чем указанное число, мы получим слово после последнего пробела (т.е первое слово с конца строки). Это означает, что если вы укажете * 999, в большинстве случаев мы получим только последнее слово.

Как это работает: с помощью функции REPLACE мы заменяем все пробелы в тексте 999 пробелами (число может быть меньше 999, но не должно быть меньше длины исходной строки). Затем с помощью функции PSTR извлекаем первые 999 символов, умноженные на число, обозначающее нужное нам слово (999 * 1 — первое). Фактически только указанное количество слов (в данном случае одно — 999 * 1). Таким образом, функция RIGHT возвращает нам только последнее слово, то есть то, что нам нужно. А затем сама ЗАМЕНА теперь удаляет лишние пробелы, заменяя их все пустой строкой — «».

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

= REPLACE (MIDDLE (RIGHT («» & REPLACE (A1; «»; REPEAT («»; 999))); 999 * 1); 1; 999);» «;»»)

Принцип тот же: если в блоке 999 * 1 заменить 1 на 5, мы получим пятое слово с конца строки.

Если вам нужно извлекать слова из пробелов, то лучше добавить еще одну функцию — TRIMS:

= REPLACE (RIGHT (MIDDLE («» & REPLACE (TRIM (A1); «»; REPEAT («»; 999))); 1; 999 * 1); 999);» «;»»)

= REPLACE (MIDDLE (RIGHT («» & REPLACE (TRIM (A1); «»; REPEAT («»; 999))); 999 * 1); 1; 999);» «;»»)

Вы также можете «извлечь» несколько слов: = TRIMS (MID (RIGHT («» & SUBSTITUTE (A1; «»; REPEAT («»; 999)); 999 * 3); 1; 999 * 2))

3 — третье слово с конца строки. 2 — количество слов.

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

Затем, чтобы получить вторую строку (ТК Таганка и ТК Опус), можно применить следующую формулу: = ПОДСТАВИТЬ (ПРАВО (PSTR (СИМВОЛ (10) & ПОДСТАВИТЬ (C2; СИМВОЛ (10); ПОВТОР (СИМВОЛ (10)))); 999)); 1; 999 * 2); 999); СИМВОЛ (10); «») CHAR (10) здесь означает разрыв строки. Обычно эти переводы выполняются с клавиатуры. Входим в режим редактирования ячейки, ставим курсор в нужную точку в строке и нажимаем Alt + Enter.

у меня есть месяцы (август 2020 г и сентябрь 2020 г.) — вот так:

= REPLACE (MIDDLE (RIGHT («/» & REPLACE (C2; «/»; REPEAT («/»; 999))); 999 * 1); 1; 999); «/»; «»)) Если вы также вам лень писать один символ в формуле несколько раз, вы можете записать его в ячейку (скажем, G1) и указать ссылку на эту ячейку в формуле: = REPLACE (RIGHT (G1 & REPLACE (C2; G1; ПОВТОР (G1; 999)); 999 * 1); 1; 999); G1; «») Теперь, чтобы изменить символ, вам нужно будет изменить его один раз в ячейке, и формула «вытащит» нужное слово / строку на основе этого конкретного символа.

Символ перед каждым словом в ячейке — Excel формула

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

1. Если в ячейке нет слов, в ячейку ничего нельзя добавить
2. Если в ячейке есть слово, вам просто нужно поставить перед ним символ, называя его ячейкой
3. Если есть 2 или более слов, пробел между ними можно заменить пробелом и требуемым символом, это формально и добавит символ перед каждым словом, кроме первого
4. На всякий случай лучше убрать лишние пробелы между словами, иначе предыдущий шаг создаст лишние символы в ячейке.

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

1. IF поможет нам создать тестовое условие и даст Excel дальнейшие инструкции на основе результата.
2. СЦЕПИТЬ (или просто «&») добавит символ перед значением ячейки
3. REPLACE поможет вам заменить пробелы пробелом и символом, необходимым для вставки перед словами
4. TRIMS поможет вам избавиться от лишних пробелов перед этим

Посмотрите пример того, как создать формулу, состоящую из этих функций:

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

Отсюда окончательная универсальная формула:

= ЕСЛИ (A1 = «»; «»; D1 & ПОДСТАВИТЬ (ОБРЕЗАТЬ (A1); «»; «» & D1))

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

= ЕСЛИ (LA1 = «»; «»; «!» & ПОДСТАВИТЬ (ОБРЕЗАТЬ (A1);» «;» !»))

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

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

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

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

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

Если вам нужно удалить пробелы только слева или справа (иначе они называются ведущими и замыкающими), вам необходимо сделать следующее:

1. Уберите пробел слева в начале строки. Чтобы все работало, нужно в соседнюю ячейку ввести следующую формулу: = MID (A1; FIND (LEFT (TRIM (A1))). Если английская версия, то: = MID (A1; FIND (LEFT (TRIM (A1))); A1); LEN (A1)) Где A1 — целевое значение для обработки.

   

2. Уберите пробел справа в конце строки. В соседней ячейке введите следующую формулу: = LEFT (F1; MAX ((MID (A1 & REPEAT (««; 99); LINE (A1: A99); 1); »«) * LINE (A1: A99))). В английской версии: = LEFT (A1; MAX ((MID (A1 & REPT («»; 99); ROW (A1: A99), 1); «») * ROW (A1: A99))). Где A1 — целевое значение для обработки.

   

Также статьи о том, как работать с ячейками в Excel:

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

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

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

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

Для корректного поиска нам нужно скопировать пробел точно из имеющегося числа с пробелом. Затем во вкладке «Главная» находим меню «Найти и выбрать» и выбираем пункт «Заменить». Вы также можете просто нажать комбинацию клавиш «Ctrl + H».

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

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

Но для текстовых данных есть еще один вариант поиска и удаления лишних пробелов. Для этих целей вы можете использовать функцию «ОБРЕЗАТЬ». Эта функция удалит все пробелы до и после текста, а также между словами, всегда оставляя только один пробел.

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

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

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

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

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

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

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 не удаляет (и не принимает в условном форматировании) неразрывный пробел.

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

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

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

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

Технология удаления пробелов

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

Способ 1: использование инструмента «Заменить»

Заменяя двойные пробелы между словами на одиночные в Excel, инструмент «Заменить» отлично справляется».

Находясь на вкладке «Главная», нажмите кнопку «Найти и выбрать», которая находится в панели инструментов «Редактировать» на ленте. В раскрывающемся списке выберите пункт «Заменить». В качестве альтернативы вместо предыдущих действий вы можете просто ввести сочетание клавиш Ctrl + H.

В любом из вариантов на вкладке «Заменить» открывается окно «Найти и заменить». В поле «Найти» установите курсор и дважды щелкните кнопку «Пробел» на клавиатуре. В поле «Заменить на» введите пробел. Затем нажмите кнопку «Заменить все».

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

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

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

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

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

Окно «Найти и заменить» снова откроется на вкладке «Заменить». Но на этот раз мы введем в поля несколько другие значения. Установите пробел в поле «Найти» и оставьте поле «Заменить на» пустым. Чтобы убедиться, что в этом поле нет пробелов, поместите в него курсор и удерживайте кнопку возврата (в виде стрелки) на клавиатуре. Нажмите и удерживайте кнопку, пока курсор не коснется левого края поля. Затем нажмите кнопку «Заменить все».

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

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

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

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

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

Выберите диапазон чисел с разделителями. Щелкните выделение правой кнопкой мыши. В появившемся меню выберите пункт «Форматировать ячейки…».

Откроется окно форматирования. Перейдите во вкладку «Номер», если открытие произошло где-то еще. Если деление было задано с использованием форматирования, в блоке параметров «Числовые форматы» должна быть установлена ​​опция «Числовой». В правой части окна есть точные настройки для этого формата. Вам просто нужно снять флажок рядом с разделителем группы строк (). Затем, чтобы изменения вступили в силу, нажмите кнопку «ОК».

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

Замена символов

Старый, но не устаревший трюк. Выделите диапазон ячеек, которые нужно очистить, и используйте инструмент «Заменить» на вкладке «Главная» → «Найти и выделить» → «Заменить» или нажмите сочетание клавиш Ctrl + H.

Изначально это окно задумывалось для массовой замены одного текста другим по принципу «Машу найди — замени на Петю», но и в этом случае мы можем использовать его для удаления лишнего текста. Например, в первой строке мы вводим «g». (без кавычек!), а во втором ничего не вводите и нажмите кнопку «Заменить все». Excel удалит все символы «y» перед названиями городов.

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

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

Используем функцию «СЖПРОБЕЛЫ» для удаления лишних пробелов

В Excel есть функция ОБРЕЗАТЬ, которая используется для удаления лишних пробелов из текста. Ниже вы найдете подробные инструкции по работе с этим инструментом:

1. Добавьте столбец поддержки рядом с вашими данными. Вы можете назвать это Trim.
2. В первой ячейке вспомогательного столбца (C2) введите формулу для удаления лишних пробелов:

• Скопируйте эту формулу в остальные ячейки столбца. Вы можете воспользоваться советами из статьи Как ввести одну и ту же формулу сразу во все выделенные ячейки .
• Заменить исходный столбец полученными данными. Для этого выделите все ячейки вспомогательного столбца и нажмите Ctrl + C, чтобы скопировать данные в буфер обмена, затем выберите первую ячейку исходного столбца (в нашем случае A2), нажмите Shift + F10 или клавишу контекстного меню, а затем клавишу V (З).

  

• Удалите вспомогательную колонку.

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

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

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

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

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

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

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

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

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

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

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

= КОД (ЛЕВЫЙ (A2; 1))

• Завершающий пробел или непечатаемый символ в конце строки:

= КОД (ПРАВО (A2; 1))

• Пробел или непечатаемый символ в центре строки, где n — позиция проблемного символа:

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

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

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

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

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

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

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

= ОБРЕЗАТЬ (ПЕЧАТЬ ((ПОДСТАВИТЬ (ПОДСТАВИТЬ (A2; CAR (127); «»); CAR (160);» «))))

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

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