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

регулярные выражения в Excel: 46 комментариев

1. Уведомление: Год 2011, лучшее | vdasus blog

2. Есть вопрос.

   Мне нужно заменить номер вида 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

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

   1. 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. ошибка была в нумерации – больше скобок и $2 это уже не то, что задумывалось :) 
      и скобок не хватает :) первая лишняя

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

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

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

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

3. Уведомление: регулярные выражения для самых начинающих | vdasus blog

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

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

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

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

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

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

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

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

                     1. А подскажите, как подправить скрипт, чтобы он прерывался как только совпал шаблон по регулярному выражению? А то, например номер 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

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

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

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

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

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

                              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 (http://wiki NULL.z9 NULL.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)

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

                           4. например что-то в этом роде (немного обманул – функция именно в экселе называется test – http://msdn.microsoft.com/en-us/library/ms974570.aspx (http://msdn NULL.microsoft NULL.com/en-us/library/ms974570 NULL.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

                           5. 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 если нет

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

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

                              ‘убираем пробелы, минусы и т.п. – приводим к виду маски
                              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 синтакис, поэтому не готовый кусок. Если не получится сходу – напиши я присяду, вспомню и сделаю работающий вариант.

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

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

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

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

6. Мне нужно заменить номер вида 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
   Что не так??

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

      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

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

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

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

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

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

9. А как переписать первую функцию, чтоб обрабатывала весь текст в ячейке? В исходном виде заменяет только первое вхождение. (RegExp понимаю, VBA – нет).

   Спасибо!

   1. Не совсем понял что надо? Пришлите пример содержимого ячейки, regexp который вы пробуете и что должно получиться в результате. Тогда посмотрю

10. Здравствуйте! Очень благодарен Вам за Вашу статью. Стал понятен механизм и Ваш макрос даже заработал! Баловался три вечера
    Но когда попытался написать сам под свои нужды, не осилил написать…

    Мне регулярно присылают базу с тысячами номеров телефонов, в которой “телефоны” написаны абсолютно в каком угодно виде. Например

    нету тилифона
    900.16.34
    ….
    нибирёт нихто
    0000000
    нет
    716-116-22-33

    Я делаю правки руками в текстовом редакторе с поддержкой регулярных выражений.
    Честно говоря уже запарило одно и то же делать и хочется автоматизировать процесс и делегировать его какой-нибудь ответственной женщине :)

    Мне нужно
    1. чтобы номера состояли сплошь из 11 цифр подряд, и начинались с 8ки
    2. если номер состоит из 7ми цифр, то впереди дописать 4 цифры
    а) к тем, которые начинаются на 900 впереди дописать 8499
    б) ко всем остальным в начале дописать 8495
    3. если номер состоит из 10 цифр, то в начале дописать 8
    4. если у номера из 11 цифр в начале стоит не 8, то поставить 8
    5. если в номере содержится 7 и более нулей подряд а так же номера в которых по итогу не 11 цифр, были заменены на ничего

    Вот как я делаю это руками
    1. Запихиваю грязную базу в текстовый редактор и удаляю всё, что не является цифрой или знаком перехода на другую строку (энтером)

    [^0-9,r,n]
    заменить на
    ничего

    2. Если номер состоит из 7 цифр и начинается на 900, то дописываем в начале 8499

    ^(900d{4})$
    заменить на
    8499$1

    3. Если номер из 7ми цифр, то подписываем в начале 8495

    ^(d{7})$
    заменить на
    8495$1

    4. Если номер без 8 в начале, то дописываем

    ^(d{10})$
    заменить на
    8$1

    5. Если в номере 11 цифр и первая цифра не 8, то заменяем на 8

    ^([^8])(d{10})$
    заменить на
    8$2

    6. Если в номере 7 и более нулей подряд или в нем не 11 цифр, то удаляем его беспощадно

    ^((.*[0]{7,}.*)|(d{1,10})|(d{12,}))$
    заменить на
    ничего

    Помогите пожалуйста оформить это в макрос

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

       1. Подскажите пожалуйста, написанное ниже похоже на правду? Excel выполняет после строчки re.IgnoreCase = True первую операцию замены (в данном случае замену семи нулей) и далее выполняет последнюю строчку if then

          !Ахтунг! Написанный ниже код работает только частично
          Public Function RgxPhone(astring As Range) As String
          Dim re As RegExp
          Dim tempString
          Set re = New RegExp
          re.Pattern = “[^0-9]”
          re.Global = True
          re.IgnoreCase = True
          tempString = re.Replace(astring, “”)
          re.Pattern = “(.*[0]{7,}.*)”
          RgxPhone = re.Replace(tempString, “”)
          re.Pattern = “(900d{4})”
          RgxPhone = re.Replace(tempString, “8499$1”)
          re.Pattern = “(d{7})”
          RgxPhone = re.Replace(tempString, “8495$1”)
          re.Pattern = “(d{10})”
          RgxPhone = re.Replace(tempString, “8$1”)
          re.Pattern = “([^8])(d{10})”
          RgxPhone = re.Replace(tempString, “8$2”)
          If Len(RgxPhone) 11 Then RgxPhone = “”
          End Function

          1. на первый взгляд проблема проста-
             tempString

             tempString = re.Replace(astring, “”)
             присваивается только в одном месте

             а дальше идут присвоения переменной RgxPhone
             RgxPhone = re.Replace(tempString, “”)

             т.е. операции выполняем, но не сохраняем их результат…

             скорре всего копай в сторону чего-то типа
             RgxPhone = re.Replace(RgxPhone , “8499$1”)

             1. Оно заработало!!! :) Не верил если честно, что осилю. Огромное спасибо! Я скоро стану свободен :)

                Суть проблемы действительно была в том, что результат операции не сохранялся вобще. Твой способ не помог, или я просто не соображаю как его применять. Я дописал после каждой операции строку
                tempString = RgxPhone

                Public Function RgxPhone(astring As Range) As String
                Dim re As RegExp
                Dim tempString
                Set re = New RegExp
                re.Pattern = “[^0-9]”
                re.Global = True
                re.IgnoreCase = True
                tempString = re.Replace(astring, “”)
                re.Pattern = “(.*[0]{7,}.*)”
                RgxPhone = re.Replace(tempString, “”)
                tempString = RgxPhone
                re.Pattern = “^(900d{4})$”
                RgxPhone = re.Replace(tempString, “8499$1”)
                tempString = RgxPhone
                re.Pattern = “^(d{7})$”
                RgxPhone = re.Replace(tempString, “8495$1”)
                tempString = RgxPhone
                re.Pattern = “^(d{10})$”
                RgxPhone = re.Replace(tempString, “8$1”)
                tempString = RgxPhone
                re.Pattern = “^([^8])(d{10})$”
                RgxPhone = re.Replace(tempString, “8$2”)
                tempString = RgxPhone
                If Len(RgxPhone) 11 Then RgxPhone = “”
                End Function

                1. лучше вот так:
                   Public Function RgxPhone(astring As Range) As String
                   Dim re As RegExp
                   Dim tempString
                   Set re = New RegExp
                   re.Global = True
                   re.IgnoreCase = True

                   re.Pattern = “[^0-9]”
                   tempString = re.Replace(astring, “”)

                   re.Pattern = “(.*[0]{7,}.*)”
                   RgxPhone = re.Replace(tempString, “”)

                   re.Pattern = “^(900d{4})$”
                   RgxPhone = re.Replace(RgxPhone, “8499$1”)

                   re.Pattern = “^(d{7})$”
                   RgxPhone = re.Replace(RgxPhone, “8495$1”)

                   re.Pattern = “^(d{10})$”
                   RgxPhone = re.Replace(RgxPhone, “8$1”)

                   re.Pattern = “^([^8])(d{10})$”
                   RgxPhone = re.Replace(RgxPhone, “8$2”)

                   If Len(RgxPhone) 11 Then RgxPhone = “”
                   End Function

                2. поясню “магию”
                   RgxPhone = re.Replace(RgxPhone, “8499$1”)

                   взять значение RgxPhone, применить к нему реюреплейс и записать результат обратно в RgxPhone.

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

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

                3. Ну и да, поздравляю, конечно! Сделав самостоятельно вы приобрели навык, знания. Если бы это сделал я – через неделю все бы забылось :)

                   1. Наученый горьким опытом, даже если сам в чём-то разобрался, могу обнулиться через неделю. Поэтому строчу в эвернот подробные заметки как будто для другого человека :)

                      1. Совершенно верно, я тоже так делаю. Веду конспекты видеолекций в onenote, и заметки в evernote. Вы молодец

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

Обсуждение закрыто.