E hoʻokaʻawale i nā kikokikona me nā ʻōlelo maʻamau (RegExp) ma Excel

ʻO kekahi o nā hana hoʻopau manawa a hoʻonāukiuki i ka hana ʻana me ka kikokikona ma Excel pāʻani - ka hoʻokaʻawale ʻana i ka "porridge" alphanumeric i loko o nā ʻāpana a unuhi i nā ʻāpana e pono ai mākou mai ia mea. ʻo kahi laʻana:

• ka unuhi ʻana i ke code zip mai ka helu wahi (maikaʻi inā ʻo ke code zip i ka hoʻomaka, akā pehea inā ʻaʻole?)
• ka loaʻa ʻana o ka helu a me ka lā o ka invoice mai ka wehewehe ʻana o ka uku ma ka ʻōlelo panakō
• ka unuhi ʻana o TIN mai nā wehewehe motley o nā ʻoihana i ka papa inoa o nā hoa kūʻē
• e ʻimi i ka helu kaʻa a i ʻole ka helu ʻatikala ma ka wehewehe, etc.
  

ʻO ka maʻamau i ia mau hihia, ma hope o ka hapalua hola o ka ʻohi ʻana i ka kikokikona me ka lima, e hoʻomaka ana nā manaʻo i ka noʻonoʻo e hoʻokaʻawale i kēia kaʻina hana (ʻoi aku ka nui o ka ʻikepili). Nui nā hāʻina a me nā pae like ʻole o ka paʻakikī-efficiency:

• hoʻohana nā hana kikokikona Excel i kūkulu ʻia e ʻimi-ʻoki-glue kikokikona: LEVIMV (HEMA), AKAU (AKAU), PSTR (waena), STsEPIT (CONCATENATE) a me kona mau analogues, KAHIKEA (JOINTEXT), KA MANAʻI (POLOKOLO) etc. He mea maikaʻi kēia ʻano inā loaʻa kahi loiloi maʻemaʻe i loko o ka kikokikona (no ka laʻana, aia mau ka index i ka hoʻomaka o ka helu). A i ʻole, ʻoi aku ka paʻakikī o nā formulas a, i kekahi manawa, hiki mai i ka array formulas, ka mea e lohi nui ai i nā papa nui.
• E ho ohana like me ka mea hoʻohana like kikokikona mai Visual Basic i kāʻei ʻia i kahi hana macro maʻamau. ʻAe kēia iā ʻoe e hoʻokō i kahi hulina maʻalahi me ka hoʻohana ʻana i nā huapalapala wildcard (*, #,?, etc.) ʻO ka mea pōʻino, ʻaʻole hiki i kēia hāmeʻa ke unuhi i ka substring i makemake ʻia mai ka kikokikona - e nānā wale inā aia i loko.

Ma waho aʻe o ka mea i luna, aia kekahi ala ʻē aʻe i ʻike nui ʻia i nā pōʻai liʻiliʻi o nā mea papahana ʻoihana, nā mea hoʻomohala pūnaewele a me nā ʻenehana ʻē aʻe - ʻo ia kēia. nā hōʻike maʻamau (Nā Manaʻo Maʻamau = RegExp = "regexps" = "maʻamau"). ʻO ka ʻōlelo maʻalahi, He ʻōlelo ʻo RegExp kahi e hoʻohana ʻia ai nā huaʻōlelo kūikawā a me nā lula e ʻimi i nā substrings pono i loko o ka kikokikona, unuhi iā lākou a i ʻole e hoʻololi iā lākou me nā kikokikona ʻē aʻe.. ʻO nā ʻōlelo maʻamau he mea hana ikaika loa a nani i ʻoi aku ma mua o nā ʻano hana ʻē aʻe a pau o ka hana me ka kikokikona ma ke ʻano o ka nui. Nui nā ʻōlelo papahana (C#, PHP, Perl, JavaScript…) a me nā mea hoʻoponopono kikokikona (Word, Notepad++…) kākoʻo i nā ʻōlelo maʻamau.

ʻAʻole i loaʻa iā Microsoft Excel ke kākoʻo RegExp ma waho o ka pahu, akā hiki ke maʻalahi kēia me VBA. E wehe i ka Visual Basic Editor mai ka pā mea haku polokalamu (Ka mea hoʻomohala) a i ʻole ka pōkole papa keyboard Alt+F11. A laila e hoʻokomo i ka module hou ma o ka papa kuhikuhi Hoʻokomo - Module a kope i ka kikokikona o ka hana macro ma laila:

RegExpExtract Public Function(Text As String, Pattern As String, Mea Koho E like me Integer = 1) As String On Error GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = ʻOiaʻiʻo inā regex.Test (Text) A laila E hoʻonohonoho i nā pāʻani = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

Hiki iā mākou ke pani i ka Visual Basic Editor a hoʻi i Excel e hoʻāʻo i kā mākou hiʻohiʻona hou. ʻO kāna syntax penei:

=RegExpExtract( Txt ; Pattern ; 'ikamu )

kahi

• txt - he kelepona me ka kikokikona a mākou e nānā nei a mai kahi a mākou e makemake ai e unuhi i ka substring e pono ai mākou
• kumu – pale (ka hoʻohālike) no ka huli substring
• 'ikamu - ka helu kaʻina o ka substring e unuhi ʻia, inā he nui o lākou (inā ʻaʻole i kuhikuhi ʻia, a laila hōʻike ʻia ka hanana mua)

ʻO ka mea hoihoi loa ma ʻaneʻi, ʻoiaʻiʻo, ʻo ia ka Pattern - kahi kaula kikoʻī o nā huaʻōlelo kūikawā "ma ka ʻōlelo" o RegExp, e kuhikuhi ana i ka mea a me kahi a mākou e makemake ai e loaʻa. Eia nā mea maʻamau e hoʻomaka ai ʻoe:

Inā mākou e ʻimi nei i kekahi helu o nā huaʻōlelo, no ka laʻana, kahi helu leka uila ʻeono a i ʻole nā ​​code huahana ʻekolu mau leka, a laila hele mai mākou e hoʻopakele. mea hoʻohelu helu or mea hoʻohelu helu he mau huaʻōlelo kūikawā e hōʻike ana i ka helu o nā huaʻōlelo e ʻimi ʻia. Hoʻohana ʻia nā mea helu i ke ʻano i hele mai ma mua o ia:

ʻĀnō e neʻe kākou i ka ʻaoʻao hoihoi loa – ka nānā ʻana i ka hoʻohana ʻana i ka hana i hana ʻia a me nā mea a mākou i aʻo ai e pili ana i nā hiʻohiʻona ma nā hiʻohiʻona kūpono mai ke ola.

Ka unuhi ʻana i nā helu mai ka kikokikona

No ka hoʻomaka ʻana, e noʻonoʻo kākou i kahi hihia maʻalahi - pono ʻoe e unuhi i ka helu mua mai ka alphanumeric porridge, no ka laʻana, ka mana o nā lako mana uninterruptible mai ka papa inoa kumukūʻai:

He maʻalahi ka loiloi ma hope o ka ʻōlelo maʻamau: d 'o ia ho'i kekahi huahelu, a me ka mea helu + ʻōlelo ʻia he hoʻokahi a ʻoi aku paha kā lākou helu. Pono ka hoʻemi ʻelua i mua o ka hana e "ma ka lele" e hoʻololi i nā huaʻōlelo i unuhi ʻia i helu piha mai ka helu-like-text.

Kālepona

I ka nānā muaʻana, maʻalahi nā mea a pau maʻaneʻi - keʻimi nei mākou iʻeono mau huahelu i ka lālani. Hoʻohana mākou i kahi ʻano kūikawā d no ka huahelu a me ka helu 6 {} no ka helu o nā huaʻōlelo:

Eia nō naʻe, hiki ke kūlana inā, ma ka ʻaoʻao hema o ka papa kuhikuhi i ka laina, aia kekahi pūʻulu nui o nā helu i ka lālani (helu kelepona, TIN, waihona waihona, a me nā mea ʻē aʻe) A laila e huki kā mākou manawa maʻamau i ka 6 mua. nā huahelu mai ia mea, ʻo ia hoʻi, ʻaʻole e hana pololei:

No ka pale ʻana i kēia, pono mākou e hoʻohui i kahi mea hoʻololi a puni nā kihi o kā mākou ʻōlelo maʻamau b e hoike ana i ka pau ana o ka olelo. E hoʻomaopopo kēia iā Excel ʻo ka ʻāpana (index) pono mākou he huaʻōlelo ʻokoʻa, ʻaʻole ʻāpana o kahi ʻāpana ʻē aʻe (helu kelepona):

Phone

ʻO ka pilikia o ka loaʻa ʻana o kahi helu kelepona ma ka kikokikona, ʻo ia ka nui o nā koho no ke kākau ʻana i nā helu - me ka ʻole a me ka ʻole o ka hyphens, ma o nā hakahaka, me ka ʻole a i ʻole ke code ʻāina ma nā brackets, etc. No laila, i koʻu manaʻo, ʻoi aku ka maʻalahi. E hoʻomaʻemaʻe mua i kēia mau huaʻōlelo mai ke kikokikona kumu me ka hoʻohana ʻana i kekahi mau hana pūnana PALAPALA (KA HOPE)no laila e pili pū ai i hoʻokahi holoʻokoʻa, a laila me kahi maʻamau primitive d{11} unuhi i 11 huahelu ma ka lālani:

ITN

ʻOi aku ka paʻakikī ma ʻaneʻi, no ka mea, hiki i ka TIN (ma Ko mākou ʻāina) he 10-helu (no nā hui kānāwai) a i ʻole 12-helu (no kēlā me kēia kanaka). Inā ʻaʻole ʻoe e ʻike i ka hewa, a laila hiki ke māʻona me ka mea maʻamau d{10,12}, akā, ʻo ka ʻōlelo ʻoiaʻiʻo, e huki ia i nā helu āpau mai 10 a 12 mau huaʻōlelo, ʻo ia hoʻi a komo hewa i nā huahelu 11. ʻOi aku ka pololei o ka hoʻohana ʻana i ʻelua mau hiʻohiʻona i hoʻopili ʻia e kahi mea hoʻohana OR logical | (pa kū pololei):

E ʻoluʻolu, ma ka nīnau e ʻimi mua mākou i nā helu 12-bit, a laila no nā helu 10-bit wale nō. Inā kākau mākou i kā mākou ʻōlelo maʻamau ma ke ʻano ʻē aʻe, a laila e huki ʻia no kēlā me kēia, ʻoiai nā TIN 12-bit lōʻihi, ʻo nā huaʻōlelo 10 wale nō. ʻO ia hoʻi, ma hope o ka hoʻomaka ʻana o ke kūlana mua, ʻaʻole hana hou ʻia ka hōʻoia hou ʻana:

ʻO kēia ka ʻokoʻa ma waena o ka mea hoʻohana | mai kahi hana loiloi excel maʻamau OR (MAU), kahi e hoʻololi ai i ka hopena o ka hoʻonohonoho hou ʻana i nā hoʻopaʻapaʻa.

Nā SKU Huahana

I loko o nā hui he nui, ua hāʻawi ʻia nā mea ʻike kūʻokoʻa i nā waiwai a me nā lawelawe - nā ʻatikala, nā code SAP, nā SKU, a me nā mea ʻē aʻe. No ka laʻana, inā ʻike mākou ʻekolu mau huaʻōlelo Pelekane nui i kā mākou ʻatikala, kahi hyphen a me kahi helu helu ʻekolu ma hope, a laila:

He maʻalahi ka manaʻo ma hope o ka template. [AZ] – 'o ia ho'i nā hua'ōlelo nui o ka pī'āpā Latina. ʻO ka mea helu hou 3 {} ʻōlelo ʻo ia he mea nui iā mākou he ʻekolu mau leka like. Ma hope o ka hyphen, ke kali nei mākou i ʻekolu huahelu, no laila hoʻohui mākou i ka hopena d{3}

Nā huina kālā

Ma ke ʻano like me ka paukū mua, hiki iā ʻoe ke huki i nā kumukūʻai (koki, VAT ...) mai ka wehewehe ʻana i nā huahana. Inā hōʻike ʻia nā huina kālā, no ka laʻana, me kahi hyphen, a laila:

kumu d me ka mea helu + huli i kekahi helu a hiki i kahi hyphen, a d{2} e nānā i nā peni (ʻelua huahelu) ma hope.

Inā pono ʻoe e unuhi i nā kumukūʻai, akā ʻo VAT, a laila hiki iā ʻoe ke hoʻohana i ke kolu o ka hoʻopaʻapaʻa koho o kā mākou hana RegExpExtract, e kuhikuhi ana i ka helu ordinal o ka mea e unuhi ʻia. A, ʻoiaʻiʻo, hiki iā ʻoe ke pani i ka hana PALAPALA (KA HOPE) i nā hualoaʻa, hyphen i ka mea hoʻokaʻawale decimal maʻamau a hoʻohui i kahi pālua pālua i ka hoʻomaka ʻana i wehewehe ʻo Excel i ka VAT i loaʻa ma ke ʻano he helu maʻamau:

Helu kaa

Inā ʻaʻole ʻoe e lawe i nā kaʻa kūikawā, nā kaʻa kaʻa a me nā kaʻa kaʻa ʻē aʻe, a laila hoʻokaʻawale ʻia ka helu kaʻa maʻamau e like me ke kumu "leka - ʻekolu helu - ʻelua leka - code ʻāina". Eia kekahi, hiki i ka helu ʻāina ke 2- a i ʻole 3-helu, a ʻo nā mea i like ke ʻano me ka alphabet Latin i hoʻohana ʻia e like me nā leka. No laila, e kōkua ka ʻōlelo maʻamau iā mākou e unuhi i nā helu mai ka kikokikona:

manawa

No ka unuhi ʻana i ka manawa ma ke ʻano HH:MM, kūpono kēia ʻōlelo maʻamau:

Ma hope o ka ʻāpana colon [0-5]d, e like me ka maʻalahi o ka hoʻomaopopo ʻana, hoʻonohonoho i kekahi helu ma ka laulā 00-59. Ma mua o ke kolonā i loko o nā pale, hana ʻelua kumu, hoʻokaʻawale ʻia e ka OR (paipu):

• [0-1]d – kekahi helu ma ka laulā 00-19
• 2[0-3] – kekahi helu ma ka laulā 20-23

I ka hopena i loaʻa, hiki iā ʻoe ke hoʻopili i ka hana Excel maʻamau TIME (KIEM)e hoʻololi iā ia i kahi ʻano manawa i hiki ke hoʻomaopopo ʻia i ka papahana a kūpono no nā helu hou aʻe.

Nānā ʻōlelo huna

Inā pono mākou e nānā i ka papa inoa o nā ʻōlelo huna i hana ʻia e nā mea hoʻohana no ka pololei. E like me kā mākou mau lula, hiki i nā ʻōlelo huna ke loaʻa i nā leka Pelekane wale nō (ka liʻiliʻi a i ʻole ka nui) a me nā helu. ʻAʻole ʻae ʻia nā hakahaka, nā kaha lalo a me nā kaha kaha ʻē aʻe.

Hiki ke hoʻonohonoho ʻia ka nānā ʻana me ka hoʻohana ʻana i ka ʻōlelo maʻamau maʻamau:

ʻO ka ʻoiaʻiʻo, me ke ʻano o ia ʻano koi mākou ma waena o ka hoʻomaka (^) a me ka hopena ($) i loko o kā mākou kikokikona he mau huaʻōlelo wale nō mai ka hoʻonohonoho i hāʻawi ʻia i nā bracket square. Inā pono ʻoe e nānā i ka lōʻihi o ka ʻōlelo huna (no ka laʻana, ma ka liʻiliʻi o 6 mau huaʻōlelo), a laila ka quantifier + hiki ke hoʻololi i ka wā "ʻeono aʻoi aʻe" ma keʻano {6,}:

Kulanakauhale mai ka helu wahi

E ʻōlelo kākou pono kākou e huki i ke kūlanakauhale mai ka pahu helu wahi. E kōkua ka papahana maʻamau, e unuhi i ka kikokikona mai "g." i ke koma aʻe:

E nānā pono kākou i kēia hiʻohiʻona.

Inā ua heluhelu ʻoe i ka kikokikona ma luna, a laila ua hoʻomaopopo mua ʻoe he manaʻo koʻikoʻi kekahi mau huaʻōlelo ma nā ʻōlelo maʻamau (nā wā, nā asterisk, nā hōʻailona kālā, a me nā mea ʻē aʻe. Inā pono ʻoe e ʻimi i kēia mau huaʻōlelo iā lākou iho, a laila ma mua o ka backslash (i kapa ʻia kekahi manawa pale pale). No laila, i ka ʻimi ʻana i ka ʻāpana "g." pono mākou e kākau ma ka ʻōlelo maʻamau Mr. inā mākou e ʻimi nei i kahi hoʻohui, a laila + etc.

ʻO nā huaʻōlelo ʻelua e hiki mai ana i kā mākou template, ke kiko a me ka asterisk quantifier, e kū no kekahi helu o nā huaʻōlelo, ʻo ia hoʻi kekahi inoa kūlanakauhale.

Aia kahi koma ma ka hope o ka template, no ka mea ke ʻimi nei mākou i ka kikokikona mai "g." i kahi koma. Akā, hiki ke nui nā koma ma ka kikokikona, ʻeā? ʻAʻole wale ma hope o ke kūlanakauhale, akā ma hope o ke alanui, nā hale, a me nā mea ʻē aʻe. Ma kahi o lākou e kū ai kā mākou noi? ʻO ia ke kumu o ka hōʻailona nīnau. Inā ʻaʻole ia, e unuhi kā mākou ʻōlelo maʻamau i ke kaula lōʻihi loa.

Ma keʻano o nā'ōlelo maʻamau,ʻo iaʻano hiʻohiʻona he "makemake". No ka hoʻoponopono ʻana i ke kūlana, pono ʻia kahi māka nīnau - hana ia i ka quantifier ma hope o ke kū ʻana he "ʻino" - a lawe kā mākou nīnau i ke kikokikona a hiki i ke koma counter mua ma hope o "g.":

inoa waihona mai ke ala piha

ʻO kekahi kūlana maʻamau ka unuhi ʻana i ka inoa faila mai ke ala piha. ʻO kahi ʻōlelo maʻamau maʻamau o ke ʻano e kōkua ma aneʻi:

ʻO ka hoʻopunipuni ma ʻaneʻi ʻo ka huli ʻana, ʻoiaʻiʻo, aia ma ka ʻaoʻao ʻē aʻe - mai ka hopena a i ka hoʻomaka, no ka mea ma ka hope o kā mākou template. $, a ke ʻimi nei mākou i nā mea a pau ma mua o ia a hiki i ke kua hope mua mai ka ʻākau. Pakele ka backslash, e like me ke kiko ma ka laʻana mua.

PS

"I ka hopena" makemake wau e wehewehe i nā mea a pau i luna nei he wahi liʻiliʻi o nā mea hiki ke hāʻawi ʻia e nā ʻōlelo maʻamau. Nui nā ʻano kikoʻī a me nā lula no kā lākou hoʻohana ʻana, a ua kākau ʻia nā puke holoʻokoʻa ma kēia kumuhana (Ke paipai nei au i kēia no ka hoʻomaka). ʻO ke ʻano, ʻo ke kākau ʻana i nā ʻōlelo maʻamau he mea hana noʻeau. Ma kahi kokoke i nā manawa a pau, hiki ke hoʻomaikaʻi ʻia a hoʻohui ʻia paha kahi ʻōlelo maʻamau i hana ʻia, e hoʻonui ai i ka nani a i ʻole hiki ke hana me kahi ākea o ka ʻikepili komo.

No ka nānā ʻana a hoʻopaʻa ʻana i nā ʻōlelo maʻamau a nā poʻe ʻē aʻe a i ʻole e hoʻopau i kāu ponoʻī, aia kekahi mau lawelawe pūnaewele kūpono: RegEx101, RegExr a oi

ʻO ka mea pōʻino, ʻaʻole kākoʻo ʻia nā hiʻohiʻona āpau o nā ʻōlelo maʻamau ma VBA (no ka laʻana, huli huli a i ʻole nā ​​papa POSIX) a hiki ke hana pū me Cyrillic, akā manaʻo wau ua lawa ka mea i laila no ka manawa mua e ʻoluʻolu iā ʻoe.

Inā ʻaʻole ʻoe he mea hou i ke kumuhana, a loaʻa iā ʻoe kahi mea e kaʻana like, e waiho i nā ʻōlelo maʻamau i ka wā e hana ai ma Excel ma nā ʻōlelo ma lalo nei. Hoʻokahi noʻonoʻo maikaʻi, akā ʻelua mau kāmaʻa he pālua!

• Hoʻololi a hoʻomaʻemaʻe i ka kikokikona me ka hana SUBSTITUTE
• Huli a hōʻailona i nā huaʻōlelo Latin ma ka kikokikona
• Huli i ka kikokikona like kokoke loa (Ivanov = Ivonov = Ivanof, etc.)