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

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

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

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

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

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

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

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

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

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

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

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

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

Где:

  • Текст - исходная строка для поиска.
  • Шаблон - регулярное выражение для поиска.
  • Замена - текст, на который нужно заменить. Чтобы удалить пробелы, вы должны установить для этого аргумента одно из следующих значений:
    • пустая строка "", чтобы удалить абсолютно все пробелы
    • несколько символов “  “ для замены нескольких пробелов на один пробел
  • Instance_num (необязательно) – порядковый номер найденного пробела. В большинстве случаев вы опускаете его, чтобы заменить все (по умолчанию).
  • Match_case (необязательно) - логическое значение, указывающее, следует ли учитывать (ИСТИНА) или игнорировать (ЛОЖЬ) регистр символов. Для пробелов это не имеет никакого значения и поэтому опускается.

Для получения дополнительной информации см. Функцию RegExpReplace.

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

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

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

Шаблон: \s+

Замена: ""

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

=RegExpReplace(A5; "\s+"; "")

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

=RegExpReplace(A5; $A$2; "")

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

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

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

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

Шаблон: \s+

Замена: " "

=RegExpReplace(A5;"\s+";" ")

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

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

=RegExpReplace(RegExpReplace(A5; "\s+"; " "); "^[\s]+|[\s]+$"; "")

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

=СЖПРОБЕЛЫ(RegExpReplace(A5;"\s+";" "))

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

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

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

^[\s]+

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

 [\s]+$

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

^[\s]+|[\s]+$

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

Замена: ""

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

=RegExpReplace(A5; “^[\s]+|[\s]+$”; "")

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

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

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

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

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

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

=RegExpReplace(A5; " +"; " ")

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

=RegExpReplace(A5; " ^ +| +$"; "")

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

=RegExpReplace(RegExpReplace(A5; " +"; " "); "^ +| +$"; "")

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

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

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

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

=RegExpReplace(A8, "^[\s]+|[\s]+$", "")

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

Шаблон: \s+

Замена : -

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

=RegExpReplace(RegExpReplace(A8; “^[\t ]+|[\t ]+$”;””); "\s+"; "-")

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

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

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

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

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

^\n

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

^[\t ]*\n

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

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

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

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

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

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

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

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

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

(?<=\d)\s+(?=\d)

Чтобы создать формулу на его основе, выполните два простых шага:

  1. На вкладке «Ablebits Data» в группе «Text» щелкните «Regex Tools».
  • На появившейся слева от рабочего листа панели выберите исходные данные, введите свое регулярное выражение, выберите параметр «Remove» и нажмите кнопку «Remove».
Удаление пробелов с помощью инструментов RegEx tool

Чтобы получить результаты в виде формул, а не значений, не забудьте поставить галочку в поле «Insert as formula (Вставить как формулу)».

Вы увидите, что функция AblebitsRegexRemove вставлена ​​в новый столбец справа от исходных данных.

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

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

(?<=\d)\s+(?=\d)

Вот формула:

=AblebitsRegexRemove(A5;$A$2)

Вот как можно убрать пробелы в Excel с помощью регулярных выражений. 

Благодарю вас за чтение!