Удалите пробелы и пустые строки в Excel с помощью Regex
Хотите обрабатывать пробелы наиболее эффективным способом? Используйте регулярные выражения, чтобы удалить все пробелы в ячейке, заменить несколько пробелов одним символом, обрезать пробелы только между числами и т. д.
Какие бы входные данные вы ни использовали, вы вряд ли встретите набор данных без пробелов. В большинстве случаев пробелы хороши — вы используете их для визуального разделения различных фрагментов информации, чтобы их было легче воспринимать. Однако в некоторых ситуациях это может стать злом — лишние пробелы могут испортить ваши формулы и сделать ваши рабочие листы почти неуправляемыми.
Зачем использовать регулярное выражение для обрезки пробелов в Excel?
Прежде чем мы углубимся в тонкости использования регулярных выражений для удаления пробелов на листах Excel, я хотел бы ответить на вопрос, который в первую очередь приходит на ум: зачем нам нужны регулярные выражения, если в Excel уже есть функция TRIM?
Чтобы понять разницу, давайте посмотрим, что считается пробелом в каждом случае:
- Встроенная функция TRIM может удалить только символ пробела, который имеет значение 32 в 7-битной системе ASCII.
- Регулярные выражения могут идентифицировать несколько различных форм пробелов, таких как пробел ( ), табуляция (\t), возврат каретки (\r) и новая строка (\n). Кроме того, есть символ пробела (\s), который соответствует всем этим типам и чрезвычайно полезен для очистки необработанных входных данных.
Зная, что именно происходит за кулисами, намного проще найти решение, не так ли?
Как включить регулярные выражения в Excel
Общеизвестно, что готовый Excel не поддерживает регулярные выражения. Чтобы включить их, вам нужно создать пользовательскую функцию VBA. К счастью, у нас уже есть один, названный RegExpReplace. Подожди, а зачем “заменять”, когда мы говорим об удалении? На языке Excel «удалить» — это просто другое слово для «заменить пустой строкой» 🙂
Чтобы добавить функцию в Excel, просто скопируйте ее код с этой страницы, вставьте в редактор VBA и сохраните файл как книгу с поддержкой макросов (.xlsm).
Вот синтаксис функции для справки:
RegExpReplace(текст, шаблон, замена, [instance_num], [match_case])
Первые три аргумента обязательны, последние два необязательны.
Где:
- Текст – исходная строка для поиска.
- Шаблон – регулярное выражение для поиска.
- Замена – текст для замены. Чтобы удалить пробелы, вы должны установить для этого аргумента значение:
- пустая строка (“”) для обрезки абсолютно всех пробелов
- пробел (“”) для замены нескольких пробелов одним пробелом
- Экземпляр_номер (необязательно) – номер экземпляра. В большинстве случаев вы опускаете его, чтобы заменить все экземпляры (по умолчанию).
- Учитывать регистр (необязательный) – логическое значение, указывающее, следует ли совпадать (ИСТИНА) или игнорировать (ЛОЖЬ) текстовый регистр. Для пробела это не имеет значения и поэтому опускается.
Для получения дополнительной информации см. функцию RegExpReplace.
Как удалить пробелы с помощью регулярного выражения — примеры
С помощью функции RegExpReplace, добавленной в вашу книгу, давайте рассмотрим разные сценарии по одному.
Удалите все пробелы с помощью регулярного выражения
Чтобы удалить все пробелы в строке, вы просто ищете любой символ пробела, включая пробел, табуляцию, возврат каретки и перевод строки, и заменяете их пустой строкой (“”).
Шаблон: \s+
Замена: “”
Предполагая, что исходная строка находится в ячейке A5, формула в ячейке B5 выглядит так:
=RegExpReplace(A5, “\s+”, “”)
Чтобы упростить управление шаблонами, вы можете ввести регулярное выражение в предопределенную ячейку и указать его в формуле, используя абсолютную ссылку, например $A$2, поэтому адрес ячейки останется неизменным при копировании формулы вниз по столбцу.
=RegExpReplace(A5, $A$2, “”)
Удалить более одного пробела
Чтобы удалить лишние пробелы (т. е. несколько последовательных пробелов), используйте то же регулярное выражение \s+, но замените найденные совпадения одним символом пробела.
Шаблон: \s+
Замена: ” “
=RegExpReplace(A5, “\s+”, ” “)
Обратите внимание, что эта формула оставляет один пробел не только между словами, но и в начале и в конце строки, что не очень хорошо. Чтобы избавиться от начальных и конечных пробелов, вложите приведенную выше формулу в другую функцию RegExpReplace, которая убирает пробелы с начала и с конца:
=RegExpReplace(RegExpReplace(A5, “\s+”, ” “), “^[\s]+|[\s]+$”, “”)
Regex для удаления начальных и конечных пробелов
Для поиска пробела в начале или конце строки используйте привязки start ^ и end $.
Ведущий пробел:
Шаблон: ^[\s]+
Завершающие пробелы:
Шаблон: [\s]+$
Начальные и конечные пробелы:
Шаблон: ^[\s]+|[\s]+$
Какое бы регулярное выражение вы ни выбрали, замените совпадения ничем.
Замена: “”
Например, чтобы удалить все пробелы в начале и в конце строки в A5, используйте следующую формулу:
=RegExpReplace(A5, “^[\s]+|[\s]+$”, “”)
Как показано на снимке экрана ниже, удаляются только начальные и конечные пробелы. Промежутки между словами остаются нетронутыми, создавая визуально приятный вид для глаза читателя.
Удалите лишние пробелы, но сохраните разрывы строк
При работе с многострочными строками вы можете избавиться от лишних пробелов, но сохранить разрывы строк. Для этого вместо пробельного символа \s найдите пробелы [ ] или пробелы и вкладки [\t ]. Последний шаблон удобен, когда исходные данные импортируются из другого источника, например, из текстового редактора.
В приведенном ниже наборе данных предположим, что вы хотите обрезать все начальные/конечные пробелы и все промежуточные пробелы, кроме одного, оставив несколько строк нетронутыми. Для выполнения задачи вам понадобятся две разные функции RegExpReplace.
Первая функция заменяет несколько пробелов одним пробелом.
=RegExpReplace(A5, “+”, “”)
Другой разделяет пробелы в начале и в конце строки:
=RegExpReplace(A5, “^ +| +$”, “”)
Просто вложите две функции одну в другую:
=RegExpReplace(RegExpReplace(A5, “+”, ” “), “^ +| +$”, “”)
И вы получите идеальный результат:
Regex для замены нескольких пробелов одним символом
Если вы хотите удалить все пробелы из строки и заменить каждую группу последовательных пробелов определенным символом, вам нужно сделать следующее:
Во-первых, используйте это регулярное выражение для обрезки начальных и конечных пробелов:
=RegExpReplace(A8, “^[\s]+|[\s]+$”, “”)
Затем выполните вышеуказанную функцию для текст аргумент другого RegExpReplace, который заменяет один или несколько последовательных пробелов на указанный вами символ, например дефис:
Шаблон: \s+
Замена: –
Предполагая, что исходная строка находится в формате A8, формула принимает следующий вид:
=RegExpReplace(RegExpReplace(A8, “^[\s]+|[\s]+$”, “”), “\s+”, “-“)
Или вы можете ввести шаблоны и замены в отдельные ячейки, как показано на скриншоте:
Regex для удаления пустых строк
Вот вопрос, который часто задают пользователи, у которых есть несколько строк в одной ячейке: «В моих ячейках много пустых строк. Есть ли способ избавиться от них, кроме как просмотреть каждую ячейку и удалить каждую строку вручную?» Ответ: Это просто!
Чтобы сопоставить пустые строки, не содержащие ни одного символа, от начала ^ текущей строки до следующей строки \n, используется регулярное выражение:
Шаблон: ^\n
Если ваши визуально пустые строки содержат пробелы или символы табуляции, используйте это регулярное выражение:
Шаблон: ^[\t ]*\n
Просто замените регулярное выражение пустой строкой, используя эту формулу, и все пустые строки сразу исчезнут!
=RegExpReplace(A5, $A$2, “”)
Вышеприведенные примеры продемонстрировали лишь небольшую часть замечательных возможностей, предоставляемых регулярными выражениями. К сожалению, не все функции классических регулярных выражений доступны в VBA.
К счастью, инструменты RegEx, включенные в наш Ultimate Suite, свободны от этих ограничений, поскольку они обрабатываются механизмом Microsoft .NET RegEx. Это позволяет создавать более сложные шаблоны, не поддерживаемые VBA RegExp. Ниже вы найдете пример такого регулярного выражения.
Регулярное выражение для удаления пробела между числами
Предположим, что вы хотите удалить пробелы только между числами в буквенно-цифровой строке, чтобы строка, такая как «A 1 2 B», стала «A 12 B».
Чтобы сопоставить пробел между любыми двумя цифрами, вы можете использовать следующие обходы:
Шаблон: (?<=\d)\s+(?=\d)
Чтобы создать формулу на основе приведенных выше регулярных выражений, выполните два простых шага:
- На Данные об аблебитах вкладка, в Текст щелкните Инструменты регулярных выражений.
- На Инструменты регулярных выражений выберите исходные данные, введите регулярное выражение, выберите параметр «Удалить» и нажмите «Удалить».
Чтобы результаты отображались в виде формул, а не значений, не забудьте установить флажок «Вставить как формулу».
Через мгновение вы увидите AblebitsRegexRemove функция вставляется в новый столбец справа от исходных данных.
Кроме того, вы можете ввести регулярное выражение в какую-нибудь ячейку, скажем, A5, и вставить формулу непосредственно в ячейку, используя Вставить функцию диалоговое окно, где AblebitsRegexRemove относится к категории AblebitsUDF.
Поскольку эта функция специально разработана для удаления строк, ей требуются только два аргумента — входная строка и регулярное выражение:
=AblebitsRegexRemove(A5, $A$2)
Вот как удалить пробелы в Excel с помощью регулярных выражений. Я благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!
Доступные загрузки
Удаление пробелов с помощью регулярного выражения — примеры (файл .xlsm)
Ultimate Suite – пробная версия (файл .exe)
