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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 появилась новая (наша)
logo

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

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

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

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

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

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

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

В меню Debug есть пункты Сделать Шаг (Step Into) – т.е. нажимая F8 вы будете выполнять программу строчка за строчкой. И в каждый момент можно посмотреть чему равны нужные нам переменные
logo
Если нам больше не надо выполнять по шагам или надо остановить выполнение – достаточно нажать на кнопку Стоп, что бы остановить. Или убрать красную строчку и нажать на зелёный треугольник, что бы всё выполнялось дальше.

logo

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

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

И вуаля!
logo

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

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

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

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 – выводим сообщение об ошибке. Понятно, что в реальном приложении лучше проверять по другому :) Для целей обучения – будет работать и так :)

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

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

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

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

  • Pingback: Год 2011, лучшее | vdasus blog()

  • Evilduck

    Есть вопрос.

    Мне нужно заменить номер вида 80123456789 на 012 345 67 89. Первым символом всегда будет 8.
    Отредактировала макрос так:

    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 = “((8)+(d{3})+(d{3})+(d{2})+(d{2})” RgxPhone = re.Replace(tempString, “$2 $3 $4 $5”)End Function

    Что не так? Я восьмерку неправильно записываю?

    • 8+(d{3})+(d{3})+(d{2})+(d{2})+ заменить на $1 $2 $3 $4
      или (8)+(d{3})+(d{3})+(d{2})+(d{2})+ заменить на $2 $3 $4 $5

    • ошибка была в нумерации – больше скобок и $2 это уже не то, что задумывалось :) 
      и скобок не хватает :) первая лишняя

    • Со скобками так:
      ((8)+(d{3}))+(d{3})+(d{2})+(d{2})+

      это 80123456789   |  8012  |  8  |  012  |  345  |  67  |  89

      • Evilduck

        А, так я лишнюю скобку поставила :)
        А плюс в конце обязательно? Там же больше ничего не будет.

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

  • Pingback: регулярные выражения для самых начинающих | vdasus blog()

  • Olga

    У меня тоже вопрос. Если у меня БД не телефонов, а дресов, которые также в расзной форме записаны необходимо присети к единому виду, плюс удалить дубли. Можно ли на основе программы с номерами, написать программу для адресов? Заранее спасибо!

    • почему-то не пришло сообщение на почту – только что заметил. Если проблема всё ещё есть – можно посмотреть. Сделать безусловно можно :) Если проблема хоть как-то формализуется (вы можете описать шаги, которые надо предпринять, чтобы получить то, что вам надо), то пусть этим занимается железяка, а не человек :) Жизнь коротка :)

      • Антон

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

        • А что значит не могу формализовать? Формализовать это как раз описать что надо сделать чтобы из того что есть получить то что надо. Например: есть адреса вот в таких видах в таких местах, надо их привести вот к такому виду.
          Любая задача которую можно представить алгоритмом “возьми и вот так поменяй” легко автоматизируется

          • Антон

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

            • В “обо мне” есть мой почтовый адрес – опиши всё толком, посмотрим

              • Антон

                Описал/отправил

                Заранее благодарю

                • tos

                  А подскажите, как подправить скрипт, чтобы он прерывался как только совпал шаблон по регулярному выражению? А то, например номер 84951118233 не меняет на 84996128233 т.к. идет дальше и доходя до последнего выполняет его.
                  Public Function RgxPhone(astring As Range) As String
                  Dim re As RegExp
                  Dim tempString
                  Set re = New RegExp
                  re.Pattern = “(D)”
                  re.Global = True
                  re.IgnoreCase = True
                  tempString = re.Replace(astring, “”)
                  re.Pattern = “((8)|(495))+(11182)+(d*)”
                  RgxPhone = re.Replace(tempString, “849961282$5”)
                  re.Pattern = “((8)|(495))+(2689002)+(d*)”
                  RgxPhone = re.Replace(tempString, “84997480402$5”)
                  End Function

                  • например вот так:

                    http://msdn.microsoft.com/en-us/library/3y21t6y4.aspx

                    If (re.IsMatch(tempstring)) Then re.Replace… else …

                    • tos

                      Что-то не очень получается. Похоже что не выполняются условия и он идет к последнему. СТранно, хотя регулярное выражение проверяю – оно точно соответствует.

                    • tos

                      Может я конечно не то делаю, но мне надо сделать проверку списка телефонов по данным условиям:

                      http://wiki.z9.ru/%D0%98%D0%B7%D0%BC%D0%B5%D0%BD%D0%B5%D0%BD%D0%B8%D1%8F_%D1%82%D0%B5%D0%BB%D0%B5%D1%84%D0%BE%D0%BD%D0%BD%D1%8B%D1%85_%D0%BD%D0%BE%D0%BC%D0%B5%D1%80%D0%BE%D0%B2_%D0%B2_%D0%9C%D0%BE%D1%81%D0%BA%D0%B2%D0%B5

                    • Позже вечером сделаю

                    • например что-то в этом роде (немного обманул – функция именно в экселе называется test – http://msdn.microsoft.com/en-us/library/ms974570.aspx):

                      Public Function RgxPhone(astring As Range) As String

                      Dim re As RegExp

                      Dim tempString

                      Set re = New RegExp

                      re.Pattern = “(D)”

                      re.Global = True

                      re.IgnoreCase = True

                      tempString = re.Replace(astring, “”)

                      re.Pattern = “((8)|(495))+(11182)+(d*)”

                      RgxPhone = “undefined”

                      If (re.Test(tempString)) Then

                      RgxPhone = re.Replace(tempString, “849961282$5”)

                      Else

                      re.Pattern = “((8)|(495))+(2689002)+(d*)”

                      If (re.Test(tempString)) Then

                      RgxPhone = re.Replace(tempString, “84997480402$5”)

                      End If

                      End If

                      End Function

                    • Test (string) – The Test method takes a string as its argument and returns True if the regular expression can successfully be matched against the string, otherwise False is returned.
                      возвращает true если regexp найден в строке и false если нет

                    • tos

                      Работает! Спасибо. А через case никак не переделать, а то с if-ми всегда громоздко получается?

                    • Конечно можно :) Только не через кейс. Я бы посоветовал записать телефоны (вернее маски) в массив, а потом просто пробегаться по массиву. Что-то вроде:

                      ‘убираем пробелы, минусы и т.п. – приводим к виду маски
                      re.Pattern = “[s -.]+”
                      tempString = re.Replace(astring, “”)
                      Dim av As Variant
                      av = [{ “495-101-(d+)”,”495-921-$1″;”495-102-(d+)”,”495-222-$1″;”495-103-(d+)”,”495-923-$1″}]
                      ‘т.е. av(1,1) = маска регекспа 495-101-(d+)
                      ‘av(1,2) = шаблон на что менять 495-921-$1

                      ‘приводим всё к виду ddd-ddd-xxxxx
                      ‘((8)|(495))+(11182)+(d*)…
                      re.Pattern = “(8)*(49d{1})+(d+)+(d*)”
                      RgxPhone = re.Replace(tempString, “$2-$3-$4”)

                      ‘а дальше достаточно просто пробежаться в цикле от i=1 до конца массива
                      if (re.Test(a(i,1)) then re.Replace(tempstring(av(i,2))

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

                    • а выход из цикла если проверка удалась – кажется “Exit For”

  • Эля

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

    • Как разбить? :) Попрограммировать немного :) Как можно помочь, если не представляешь о чем идёт речь?

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

  • Анна

    Мне нужно заменить номер вида 8442526325 на +7 (8442) 52-63-25. Первым символом всегда будет +7.
    Отредактировала макрос так:

    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{4})+(d{2})+(d{2})+(d{2})+”
    re.Pattern = “7+(d{4})+(d{2})+(d{2})+(d{2})+”
    RgxPhone = re.Replace(tempString, “$2 ($3) $4-$5-$6”)
    If (Left(RgxPhone, 1) “7”) Then RgxPhone = “+7” + RgxPhone _
    Else: RgxPhone = Replace(RgxPhone, “+7”, “8442”, 1, 1)
    End Function
    Что не так??

    • Например вот так:

      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, “”)

      ‘+7 (8442) 52-63-25

      ‘8442526325

      re.Pattern = “(d{4})+(d{2})+(d{2})+(d{2})+”

      RgxPhone = re.Replace(tempString, “($1) $2-$3-$4”)

      ‘Esli pervyj simvol ne 7, to dobavim vperedi +7

      If (Left(RgxPhone, 1) “7”) Then RgxPhone = “+7 ” + RgxPhone _

      ‘Inache drugaja obrabotka – zdes tochno chto-to ne tak :)

      ‘Else: RgxPhone = Replace(RgxPhone, “+7”, “8442”, 1, 1)

      End Function

      Если напишешь подробнее какие данные могут быть – подправлю :)

  • Денис

    Спасибо за статью Виктор!!! Благодаря таким как ты – просторы сети ещё не стали окончательной помойкой)))

    • Спасибо за добрые слова – где бы времени взять, чтобы вести его регулярно – ничего не успеваю…

  • Anna Us

    Спасибо Вам огромное! Очень доходчиво и понятно.

    • На здоровье, спрашивайте если что :)