Я Ñже пиÑал о Ñакой замеÑаÑелÑной веÑи, как РегÑлÑÑнÑе ÐÑÑÐ°Ð¶ÐµÐ½Ð¸Ñ (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 Ð²Ñ Ð±ÑдеÑе вÑполнÑÑÑ Ð¿ÑогÑÐ°Ð¼Ð¼Ñ ÑÑÑоÑка за ÑÑÑоÑкой. Рв каждÑй Ð¼Ð¾Ð¼ÐµÐ½Ñ Ð¼Ð¾Ð¶Ð½Ð¾ поÑмоÑÑеÑÑ ÑÐµÐ¼Ñ ÑÐ°Ð²Ð½Ñ Ð½ÑжнÑе нам пеÑеменнÑе
ÐÑли нам болÑÑе не надо вÑполнÑÑÑ Ð¿Ð¾ Ñагам или надо оÑÑановиÑÑ Ð²Ñполнение – доÑÑаÑоÑно нажаÑÑ Ð½Ð° ÐºÐ½Ð¾Ð¿ÐºÑ Ð¡Ñоп, ÑÑо Ð±Ñ Ð¾ÑÑановиÑÑ. Ðли ÑбÑаÑÑ ÐºÑаÑнÑÑ ÑÑÑоÑÐºÑ Ð¸ нажаÑÑ Ð½Ð° зелÑнÑй ÑÑеÑголÑник, ÑÑо Ð±Ñ Ð²ÑÑ Ð²ÑполнÑлоÑÑ Ð´Ð°Ð»ÑÑе.
ТепеÑÑ Ð½Ð°Ð¼ надо напиÑаÑÑ Ð½Ð°Ñе ÑегÑлÑÑное вÑÑажение. ÐÐ»Ñ ÑÑого можно иÑполÑзоваÑÑ Ð¼Ð½Ð¾Ð¶ÐµÑÑво пÑогÑамм. ÐапÑимеÑ
ТепеÑÑ Ð¾ÑÑалоÑÑ ÑолÑко ÑкопиÑоваÑÑ Ð¿Ð¾Ð»ÑÑеннÑй 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 – вÑводим ÑообÑение об оÑибке. ÐонÑÑно, ÑÑо в ÑеалÑном пÑиложении лÑÑÑе пÑовеÑÑÑÑ Ð¿Ð¾ дÑÑÐ³Ð¾Ð¼Ñ :) ÐÐ»Ñ Ñелей обÑÑÐµÐ½Ð¸Ñ – бÑÐ´ÐµÑ ÑабоÑаÑÑ Ð¸ Ñак :)
ÐÐ¾Ñ Ð¸ вÑÑ Ð¿ÑемÑдÑоÑÑÑ. Ð ÑепеÑÑ “ÑпоÑÑдоÑиÑÑ” номеÑа ÑелеÑонов Ñ Ð½Ð°Ñ Ð·Ð°Ð¹Ð¼ÑÑ Ð²Ñего-навÑего паÑа ÑекÑнд…
ÐбÑаÑиÑе внимание, ÑÑо ÑÑо ТÐСТÐÐЫРпÑимеÑ. Ðам надо оÑÐµÐ½Ñ Ð²Ð½Ð¸Ð¼Ð°ÑелÑно ÐÐÐÐ Ð ÐРпÑоÑмоÑÑеÑÑ, ÑÑо Ð±Ñ Ð²ÑÑ ÑооÑвеÑÑÑвовало ваÑим ÑÑандаÑÑам пÑедÑÑÐ°Ð²Ð»ÐµÐ½Ð¸Ñ Ð½Ð¾Ð¼ÐµÑа, колиÑеÑÑÐ²Ñ ÑиÑÑ Ð² номеÑе и код ÑÑÑанÑ!
ÐÑли еÑÑÑ Ð²Ð¾Ð¿ÑоÑÑ – ÑпÑаÑивайÑе. ÐÑли ÑÑо-Ñо непонÑÑно – ÑпÑаÑивайÑе. ÐÑли замеÑили оÑÐ¸Ð±ÐºÑ – пиÑиÑе, иÑпÑавлÑ. ХоÑиÑе пÑедложиÑÑ Ð´ÑÑгие ваÑианÑÑ? ÐиÑиÑе, Ñ Ð¾Ð±ÑзаÑелÑно Ð´Ð¾Ð±Ð°Ð²Ð»Ñ Ð²Ð°ÑÑ Ð¿Ð¾Ð»ÐµÐ·Ð½ÑÑ Ð¸Ð½ÑоÑмаÑиÑ. ÐÑÐ¾Ñ Ð±Ð»Ð¾Ð³ ÑиÑаÑÑ Ð¼Ð½Ð¾Ð³Ð¸Ðµ – комÑ-Ñо бÑÐ´ÐµÑ Ð¿Ð¾Ð»ÐµÐ·Ð½Ð¾.
СпаÑибо за внимание :)
Есть вопрос.
Мне нужно заменить номер вида 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
А, так я лишнюю скобку поставила :)
А плюс в конце обязательно? Там же больше ничего не будет.
плюс означает, что предыдущая группа может появиться один или больше раз. Теоретически можно не ставить. Практически – лучше поставить
У меня тоже вопрос. Если у меня БД не телефонов, а дресов, которые также в расзной форме записаны необходимо присети к единому виду, плюс удалить дубли. Можно ли на основе программы с номерами, написать программу для адресов? Заранее спасибо!
почему-то не пришло сообщение на почту – только что заметил. Если проблема всё ещё есть – можно посмотреть. Сделать безусловно можно :) Если проблема хоть как-то формализуется (вы можете описать шаги, которые надо предпринять, чтобы получить то, что вам надо), то пусть этим занимается железяка, а не человек :) Жизнь коротка :)
Добрый день, у меня такая же проблема как и у Ольги. Формализовать проблему могу, готов ответить на все вопросы и кинуть пример. Хочется верить, что решить это можно автоматизацией, в противном случае это ручная работа на огромное количество часов.
А что значит не могу формализовать? Формализовать это как раз описать что надо сделать чтобы из того что есть получить то что надо. Например: есть адреса вот в таких видах в таких местах, надо их привести вот к такому виду.
Любая задача которую можно представить алгоритмом “возьми и вот так поменяй” легко автоматизируется
Почему не могу? Как раз наоборот. Да, есть входящие данные и мне нужно привести их к “такому-то” виду. Но это лишь часть проблемы. Необходим алгоритм похожий на функцию ВПР, только с более, мммм, интеллектуальным поиском. Опыта работы с макросами у меня, к сожалению, нет.
В “обо мне” есть мой почтовый адрес – опиши всё толком, посмотрим
Описал/отправил
Заранее благодарю
А подскажите, как подправить скрипт, чтобы он прерывался как только совпал шаблон по регулярному выражению? А то, например номер 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 (http://msdn NULL.microsoft NULL.com/en-us/library/3y21t6y4 NULL.aspx)
If (re.IsMatch(tempstring)) Then re.Replace… else …
Что-то не очень получается. Похоже что не выполняются условия и он идет к последнему. СТранно, хотя регулярное выражение проверяю – оно точно соответствует.
Может я конечно не то делаю, но мне надо сделать проверку списка телефонов по данным условиям:
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)
Позже вечером сделаю
например что-то в этом роде (немного обманул – функция именно в экселе называется 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
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 если нет
Работает! Спасибо. А через 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
Если напишешь подробнее какие данные могут быть – подправлю :)
Спасибо за статью Виктор!!! Благодаря таким как ты – просторы сети ещё не стали окончательной помойкой)))
Спасибо за добрые слова – где бы времени взять, чтобы вести его регулярно – ничего не успеваю…
Спасибо Вам огромное! Очень доходчиво и понятно.
На здоровье, спрашивайте если что :)
А как переписать первую функцию, чтоб обрабатывала весь текст в ячейке? В исходном виде заменяет только первое вхождение. (RegExp понимаю, VBA – нет).
Спасибо!
Не совсем понял что надо? Пришлите пример содержимого ячейки, regexp который вы пробуете и что должно получиться в результате. Тогда посмотрю
Здравствуйте! Очень благодарен Вам за Вашу статью. Стал понятен механизм и Ваш макрос даже заработал! Баловался три вечера
Но когда попытался написать сам под свои нужды, не осилил написать…
Мне регулярно присылают базу с тысячами номеров телефонов, в которой “телефоны” написаны абсолютно в каком угодно виде. Например
нету тилифона
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,}))$
заменить на
ничего
Помогите пожалуйста оформить это в макрос
К сожалению совершенно не имею времени этим заняться… Есть проекты, которые надо делать. Пробуйте сами, мне жаль что я не смогу помочь. Там на самом деле ничего сложного. Фактически в том что есть в этой статье есть все чтобы решить проблему
Подскажите пожалуйста, написанное ниже похоже на правду? 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
на первый взгляд проблема проста-
tempString
tempString = re.Replace(astring, “”)
присваивается только в одном месте
а дальше идут присвоения переменной RgxPhone
RgxPhone = re.Replace(tempString, “”)
т.е. операции выполняем, но не сохраняем их результат…
скорре всего копай в сторону чего-то типа
RgxPhone = re.Replace(RgxPhone , “8499$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
лучше вот так:
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
поясню “магию”
RgxPhone = re.Replace(RgxPhone, “8499$1”)
взять значение RgxPhone, применить к нему реюреплейс и записать результат обратно в RgxPhone.
когда строка начинает выполняться – значение переменной старое, потом выполняется действие и результат записывается по тому же адресу. Т.е. после выполнения этой строки RgxPhone будет иметь уже новое значение
в принципе можно и как вы написали, но это плохо ибо код менее читабельный, много ненужного для выполнения задачи визуального мусора. Привыкайте чтобы код был лаконичен (минимум лишнего), разделять логические куски переводами строки (или выделяя в отдельную функцию), избегайте появления множества ненужных переменных, … тогда вам легче будет разбираться в вашем же коде, например, через год :)
Ну и да, поздравляю, конечно! Сделав самостоятельно вы приобрели навык, знания. Если бы это сделал я – через неделю все бы забылось :)
Наученый горьким опытом, даже если сам в чём-то разобрался, могу обнулиться через неделю. Поэтому строчу в эвернот подробные заметки как будто для другого человека :)
Совершенно верно, я тоже так делаю. Веду конспекты видеолекций в onenote, и заметки в evernote. Вы молодец
если ответ выше не помог – напиши, я завтра вечером присяду посмотрю что там может быть… (Кстати – очень помогает выполнение по шагам. я там в статье написал – попробуй выполнить один вызов функции и увидишь где проблема, просто смотри значение переменных)
сейчас занят чтобы углубляться, завтра смогу глянуть