Как найти пробелы в 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)))

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

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

Удаление лишних пробелов функцией СЖПРОБЕЛЫ (TRIM) и формулами

Неразрывные пробелы не удаляются

Неразрывный пробел

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

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

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

Зачистка текста

Давайте рассмотрим способы избавления от такого «мусора».

Замена

«Старый, но не устаревший» трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная – Найти и выделить (Home – Find & Select – Replace) или жмем сочетание клавиш Ctrl+H.

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

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

Удаление пробелов

Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).

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

Удаление непечатаемых символов

В некоторых случаях, однако, функция СЖПРОБЕЛЫ (TRIM) может не помочь. Иногда то, что выглядит как пробел – на самом деле пробелом не является, а представляет собой невидимый спецсимвол (неразрывный пробел, перенос строки, табуляцию и т.д.). У таких символов внутренний символьный код отличается от кода пробела (32), поэтому функция СЖПРОБЕЛЫ не может их «зачистить».

Вариантов решения два:

Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
Использовать функцию ПЕЧСИМВ (CLEAN) . Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справится не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.

Функция ПОДСТАВИТЬ

Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE) . У нее три обязательных аргумента:

Текст в котором производим замену
Старый текст – тот, который заменяем
Новый текст – тот, на который заменяем

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

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

Апостроф (‘) в начале ячейки на листе Microsoft Excel – это специальный символ, официально называемый текстовым префиксом. Он нужен для того, чтобы дать понять Excel, что все последующее содержимое ячейки нужно воспринимать как текст, а не как число. По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная – Число – Текстовый) и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим. Но иногда он оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert — Module) и введите туда его текст:

Sub Apostrophe_Remove() For Each cell In Selection If Not cell.HasFormula Then v = cell.Value cell.Clear cell.Formula = v End If Next End Sub

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

Английские буквы вместо русских

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

Можно, конечно, вручную заменять символы латинцы на соответствующую им кириллицу, но гораздо быстрее будет сделать это с помощью макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert — Module) и введите туда его текст:

Sub Replace_Latin_to_Russian() Rus = "асекорхуАСЕНКМОРТХ" Eng = "acekopxyACEHKMOPTX" For Each cell In Selection For i = 1 To Len(cell) c1 = Mid(cell, i, 1) If c1 Like "[" & Eng & "]" Then c2 = Mid(Rus, InStr(1, Eng, c1), 1) cell.Value = Replace(cell, c1, c2) End If Next i Next cell End Sub

Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу 🙂

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

Поиск символов латиницы в русском тексте
Проверка текста на соответствие заданному шаблону (маске)
Деление «слипшегося» текста из одного столбца на несколько

Как удалить пробелы и пустые строки в Excel с помощью Regex

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

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

Зачем использовать регулярное выражение для удаления пробелов в Excel?

Прежде чем мы углубимся в подробности использования регулярных выражений для удаления пробелов в листах Excel, я хотел бы прежде всего ответить на вопрос, который приходит в голову — зачем нам регулярные выражения, если в Excel уже есть функция СЖПРОБЕЛЫ (TRIM)?

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

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

Точно зная, что происходит за кулисами, гораздо проще найти решение, не так ли?

Как включить регулярные выражения в Excel

Хорошо известно, что стандартная версия Excel не поддерживает регулярные выражения. Чтобы включить их, вам нужно создать настраиваемую функцию VBA. К счастью, у нас уже есть такая, названная RegExpReplace. Вы спросите, а зачем «заменять», когда речь идет об удалении? На языке Excel «удалить» – это просто еще один синоним для «заменить пустой строкой» 🙂

Чтобы добавить функцию в Excel, просто скопируйте ее код с этой страницы, вставьте его в редактор VBA и сохраните файл как книгу с поддержкой макросов (.xlsm).

Вот синтаксис функции для справки:

RegExpReplace (текст; шаблон; замена; [instance_num]; [match_case])

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

Текст — исходная строка для поиска.
Шаблон — регулярное выражение для поиска.
Замена — текст, на который нужно заменить. Чтобы удалить пробелы, вы должны установить для этого аргумента одно из следующих значений:
- пустая строка «» , чтобы удалить абсолютно все пробелы
- несколько символов “ “ для замены нескольких пробелов на один пробел
Для получения дополнительной информации см. Функцию RegExpReplace.

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

Удалить любые пробелы с помощью регулярного выражения

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

Предполагая, что исходная строка находится в A5, формула в B5:

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

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

Удалить только более одного пробела подряд

Чтобы удалить лишние пробелы (т.е. более одного пробела подряд), используйте то же регулярное выражение \s+ , но замените найденные совпадения одним символом пробела.

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

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

Регулярное выражение для удаления начальных и конечных пробелов

Для поиска пробелов в начале или конце строки используйте якоря начала ^ и конца $ .

Регулярное выражение для удаления ведущих пробелов:

Для конечных пробелов:

Регулярное выражение для удаления начальных и конечных пробелов:

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

Например, чтобы удалить все пробелы в начале и в конце строки в A5, формула имеет следующий вид:

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

Удалите лишние пробелы, но сохраните переносы строк

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

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

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

Другое удаляет пробелы в начале и в конце строки:

Просто вложите две функции одна в другую:

И вы получите отличный результат:

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

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

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

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

Предполагая, что исходная строка находится в A8, формула принимает следующий вид:

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

Регулярное выражение для удаления пустых строк в ячейке

Вот вопрос, который часто задают пользователи, у которых в одной ячейке есть несколько строк: «В моих ячейках много пустых строк. Есть ли какой-нибудь способ избавиться от них, кроме как просматривать каждую ячейку и удалять каждую строку вручную?» Ответ: это просто!

Чтобы определить пустые строки, в которых нет ни одного символа от начала ^ текущей строки до следующей строки \n , можно использовать регулярное выражение:

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

Просто замените регулярное выражение пустой строкой, используя эту формулу, и все лишние пустые строки исчезнут сразу!

=RegExpReplace(A5; “^[\t ]*\n”; «»)

Удаление пробелов с помощью инструментов RegEx

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

К счастью, инструменты RegEx, включенные в программу Ultimate Suite , свободны от этих ограничений, поскольку они обрабатываются механизмом Microsoft .NET RegEx. Это позволяет создавать более сложные шаблоны, которые не поддерживаются VBA RegExp. Ниже вы видите пример такого регулярного выражения.

Регулярное выражение для удаления пробелов между числами

В буквенно-цифровой строке предположим, что вы хотите удалить пробелы только между числами, чтобы такая строка, как «A 1 2 B» превратилась в «A 12 B».

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

Чтобы создать формулу на его основе, выполните два простых шага:
1. На вкладке «AblebitsData» в группе «Text» щелкните «Regex Tools».
- На появившейся слева от рабочего листа панели выберите исходные данные, введите свое регулярное выражение, выберите параметр «Remove» и нажмите кнопку «Remove».
Чтобы получить результаты в виде формул, а не значений, не забудьте поставить галочку в поле «Insert as formula (Вставить как формулу)».

В качестве альтернативы вы можете ввести регулярное выражение в какую-либо ячейку, скажем, A5, и вставить формулу непосредственно в ячейку с помощью диалогового окна «Вставить функцию», где AblebitsRegexRemove относится к категории AblebitsUDFs.

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

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