регулярные выражения в Excel

Я уже писал о такой замечательной вещи, как Регулярные Выражения (Regular Expressions). Тем, кто потратит время на их изучение, удастся значительно упростить себе жизнь в самых разнообразных задачах.

Я уже писал как их можно использовать и вот теперь приступим к реальной задаче. Для разнообразия будем использовать обычный Excel.

У многих, для примера, есть списки, которые содержат номера телефонов. Телефонные номера пишутся как попало. Как хотелось бы одним лёгким движением руки привести их к единообразному виду.

Хотите уметь сделать вот так как в верхней картинке? Тогда вам сюда:

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

Итак. Начнём.

Допустим у нас есть вот такой список телефонов:

Для начала надо “научить” Excel понимать регулярные выражения. Для этого переходим в режим Visual Basic for Applications (VBA):

И добавляем “штуку”, которая, собственно, понимает эти самые Regular Expressions

Штука может быть такой

Или, ещё лучше, такой. Главное, что бы была выбрана одна из них, а не обе сразу. Просто ставим птичку.

Теперь добавляем новый модуль

И пишем вот такую функцию:

Public Function RgxReplace(aregexp As String, _
astring As Range, _
areplace As String) As String
Dim re As RegExp
Set re = New RegExp
re.Pattern = aregexp
RgxReplace = re.Replace(astring, areplace)
End Function

Что делает эта функция? Да просто даёт нам возможность использовать регулярные выражения. Например, функцию замены чего-то на что-то.
Обратите внимание, что в списке всех функций Excel появилась новая (наша)

Мы можем написать вот так:

Пока вы не разберётесь с RegExp она пока бесполезна, поэтому сделаем ещё одну. Которая будет специально “заточена” под конкретную задачу. Назовём её RgxPhone:

B вот что мы получаем, если её используем:

Я подробно разберу конечный вариант функции – пока просто листайте. Обратите внимание, что функцию можно отлаживать. Т.е. выполнять по шагам и смотреть в каком месте мы что-то делаем не так. Для того, что бы начать отладку – мы просто щёлкаем мышкой на левом поле. Строка станет красного цвета. Если щёлкнуть там ещё раз – строка станет обычной. Что такое эта красная строчка? Это “точка остановки”.

Т.е. когда начнёт работать программа и исполнение дойдёт до этой строки – Excel приостановит свою работу

Посмотрим как это на практике: Делаем красную строчку (breakpoint), переключаемся в режим Excel, выбираем строку с нашей формулой, ставим на неё курсор и нажимаем Enter.

Функция пытается себя посчитать, доходит до “красной строчки” и останавливается. А теперь поводим мышкой по экрану. Например посмотрим что будет, если навести мышь на переменную tempString? Появится Hint в котором показывается её текущее значение! Удобно? Конечно – мы можем выполнить с этого места программу по шагам, наблюдая как меняются значения переменных. Это очень сильно облегчает поиск ошибок…

В меню Debug ÐµÑÑ‚ÑŒ пункты Сделать Шаг (Step Into) – т.е. нажимая F8 вы будете выполнять программу строчка за строчкой. И в каждый момент можно посмотреть чему равны нужные нам переменные

Если нам больше не надо выполнять по шагам или надо остановить выполнение – достаточно нажать на кнопку Стоп, что бы остановить. Или убрать красную строчку и нажать на зелёный треугольник, что бы всё выполнялось дальше.

Теперь нам надо написать наше регулярное выражение. Для этого можно использовать множество программ. Например TextCrawler (бесплатная) или RegexBuddy (отличная, но платная) или онлайн сервис http://gskinner.com/RegExr/. Попробуем воспользоваться gskinner

Теперь осталось только скопировать полученный regexp в нашу функцию

И вуаля!

Хотелось бы ещё проверять верный ли номер вообще – добавляем нижнюю строку:

И вот! У нас есть функция rgxphone, которая берет номер практически в любом виде и форматирует его так, как нам надо. Если номер неверный (например, не хватает цифр) – выведется сообщение об ошибке.

Давайте смотреть что у нас получилось:

Public Function RgxPhone(astring As Range) As String
 Dim re As RegExp
 Dim tempString
 Set re = New RegExp
 re.Pattern = "(-|\s|\+|\(|\))"
 re.Global = True
 re.IgnoreCase = True
 tempString = re.Replace(astring, "")
 're.Pattern = "\+?(\d{3})+(\d{3})+(\d{2})+(\d{3})+"
 re.Pattern = "((8)|(\d{3}))+(\d{3})+(\d{2})+(\d{3})+"
 RgxPhone = re.Replace(tempString, "$2$3 ($4) $5-$6")
 If (Left(RgxPhone, 1) <> "8") Then RgxPhone = "+" + RgxPhone _
  Else RgxPhone = Replace(RgxPhone, "8", "+370", 1, 1)
 If Len(RgxPhone) <> 17 Then RgxPhone = "Bad number !!!"
End Function

По номерам строк:
Первая строка – объявление функции.

Public Function RgxPhone(astring As Range) As String

Public – значит, что она доступна для использования в “обычном” Excel. Она принимает один параметр и возвращает строку как результат

2: Говорим, что нам нужна “штука”, которая понимает regexp

Dim re As RegExp

3: Создаём строковую переменную для промежуточных результатов. Это Ð¶Ðµ удобно – посчитали что-то – положили в временный ящичек. А потом, когда надо – использовали.

Dim tempString

Читайте дальше :)

4: Создаём “штуку”, которая понимает regexp

Set re = New RegExp

5: Теперь давайте подумаем. Как человек делает то, что мы хотим? Он мысленно выбирает цифры из номера, отбрасывая пробелы. Затем мысленно группирует их в нужном порядке. Так мы и поступим – вначале просто удалим все ненужные знаки – пробелы, минусы и т.д. Для этого надо написать regexp, который все эти знаки найдёт:

re.Pattern = "(-|\s|\+|\(|\))"

“(-|\s|\+|\(|\)) Смотрите:
группа состоит из “знак минус -” ИЛИ “пробел \s” ИЛИ “знак плюса \+” ИЛИ “открытая скобка \(” ИЛИ “закрытая скобка \)”. Лишние знаки слэш нужны, что бы отличить это “команда” или “символ”. Поясню. Например + в regexp означает “один любой символ” (точнее “+” означает, что предыдущий символ может встречаться один и более раз). Как понять это один любой символ или собственно знак плюса? Для того, что бы можно было различать – там, где нам нужен именно значок – мы просто добавляем перед ним обратный слэш.

Выглядит страшно, но ведь всё понятно? :) Не пугайтесь, для начинающих писать regexp намного легче, чем его читать. Поэтому всё не так страшно. Для ознакомления с деталями – побродите по моему первому посту на эту тему (ссылка вверху).

Далее две строки 6 и 7 это просто модификаторы.

re.Global = True
re.IgnoreCase = True

Если интересно – почитайте о них в любом описании regexp. Ð’ данном случае они не важны, поэтому не буду останавливаться.

8: Собственно замена.

tempString = re.Replace(astring, "")

Берём временную переменную и заполняем его при помощи функции replace. Как работает этот replace? Очень просто: он находит то, что попадает под Pattern и меняет его на то, что передано вторым параметром. Т.е. в данном случае найдутся все плюсы, пробелы, скобки и заменятся на пустую строку т.е. на “ничто”. Что фактически превращает любую строку 8+888 (8) 888 в вид 88888888

10: Теперь во временной переменной у нас телефонный номер, из которого убран “мусор”. Теперь давайте найдём там то, что нам надо – код региона и т.п.

re.Pattern = "((8)|(\d{3}))+(\d{3})+(\d{2})+(\d{3})+"

смотрим какой Pattern (т.е. regexp) будет теперь и разберём его по косточкам:
((8)|(\d{3}))+(\d{3})+(\d{2})+(\d{3})+

((8)|(\d{3}))+ – обязательно Ð´Ð¾Ð»Ð¶Ð½Ð° быть ИЛИ восьмёрка ИЛИ группа из 3 цифр

(\d{3})+ – обязательно группа из 3 цифр 
(\d{2})+ – обязательно группа из 2 цифр
(\d{3})+ -обязательно группа из 3 цифр

Т.е. номер 37061025252 мы “разбиваем на группы” 37061025252
Или номер 861025252 мы “разбиваем на группы” 861025252

Номера групп считаются слева направо. Т.е. первые скобки – первая группа, вторые – вторая и т.д.

11 строка:

RgxPhone = re.Replace(tempString, "$2$3 ($4) $5-$6")

Теперь меняем то, что нашли на вот такое $2$3 ($4) $5–$6 Т.е. пишем то, что нашли во второй или третьей Ð³Ñ€ÑƒÐ¿Ð¿Ðµ, потом пробел, открывается скобка, то, что нашли в четвёртой группе, пробел, то, что нашли в пятой группе, дефис, то что нашли в шестой группе.

Напомню, что во второй группе будет восьмёрка, если будет. А в третьей группа кода страны. Либо вторая либо третья группа пуста. Поэтому мы их пишем вместе. Не может же Ð½Ð¾Ð¼ÐµÑ€ одновременно начинаться и с восьмёрки и с кода страны?

Т.е. в случае 861025252 – вторая группа будет равна 8, а третья будет пустой
В случае 37061025252 – вторая группа будет пустой (не нашли восьмёрку), а третья = 370

12: Вот и почти всё.

If (Left(RgxPhone, 1) <> "8") Then RgxPhone = "+" + RgxPhone _

Хотелось бы ещё добавить + к номеру: Если получившееся НЕ начинается с восьмёрки – добавляем плюсик в начало.
Т.е. 370610… превратится в +370610…

13: Заменяем первую восьмёрку на код страны. В моём случае это 370

 Else RgxPhone = Replace(RgxPhone, "8", "+370", 1, 1)

14: Проверка на правильный номер телефона.

If Len(RgxPhone) <> 17 Then RgxPhone = "Bad number !!!"

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

Вот и вся премудрость. И теперь “упорядочить” номера телефонов у нас займёт всего-навсего пара секунд…

Обратите внимание, что это ТЕСТОВЫЙ пример. Вам надо очень внимательно ОДИН РАЗ просмотреть, что бы всё соответствовало вашим стандартам представления номера, количеству цифр в номере и код страны!

Если есть вопросы – спрашивайте. Если что-то непонятно – спрашивайте. Если заметили ошибку – пишите, исправлю. Хотите предложить другие варианты? Пишите, я обязательно добавлю вашу полезную информацию. Этот блог читают многие – кому-то будет полезно.

Спасибо за внимание :)