{"id":7446,"date":"2025-01-24T08:59:42","date_gmt":"2025-01-24T08:59:42","guid":{"rendered":"https:\/\/howtogeek.blog\/lt\/?p=7446"},"modified":"2025-01-24T08:59:42","modified_gmt":"2025-01-24T08:59:42","slug":"a-comprehensive-guide-to-using-regex-functions-in-excel","status":"publish","type":"post","link":"https:\/\/howtogeek.blog\/lt\/a-comprehensive-guide-to-using-regex-functions-in-excel\/","title":{"rendered":"I\u0161samus REGEX funkcij\u0173 naudojimo Excel programoje vadovas"},"content":{"rendered":"<p>2024 m. \u201eMicrosoft\u201c i\u0161pl\u0117t\u0117 \u201eExcel\u201c galimybes pristatydama tris galingas funkcijas, orientuotas \u012f reguliari\u0105sias i\u0161rai\u0161kas (REGEX). \u0160ios funkcijos, b\u016btent REGEXTEST, REGEXEXTRACT ir REGEXREPLACE, suteikia vartotojams galimyb\u0119 efektyviai identifikuoti, i\u0161gauti ir pakeisti sud\u0117tingus duomen\u0173 \u0161ablonus. Juos galima pasiekti naudojant \u201eExcel\u201c \u017einiatinklio versij\u0105 ir \u201eMicrosoft 365\u201c, tod\u0117l naudotojai gali \u017eymiai geriau valdyti duomenis.<\/p>\n<h2 id=\"introduction-to-regex\">\u201eExcel\u201c REGEX funkcij\u0173 supratimas<\/h2>\n<p>Reguliarios i\u0161rai\u0161kos yra simboli\u0173 sekos, kurios sudaro paie\u0161kos \u0161ablon\u0105, palengvinan\u010dios i\u0161sami\u0105 duomen\u0173 analiz\u0119. \u0160ios funkcijos pakei\u010dia \u201eExcel\u201c naudotoj\u0173 s\u0105veik\u0105 su savo duomen\u0173 rinkiniais, tod\u0117l tokios u\u017eduotys kaip duomen\u0173 tikrinimas, i\u0161traukimas ir keitimas tampa paprastesn\u0117s ir intuityvesn\u0117s.<\/p>\n<h2 id=\"using-the-regextest-function\">REGEXTEST funkcijos panaudojimas<\/h2>\n<p>Funkcija REGEXTEST nustato, ar teksto dalyje yra nurodytas \u0161ablonas, tod\u0117l gaunama i\u0161vestis TRUE arba FALSE. Funkcijos sintaks\u0117 yra <code>REGEXTEST(value, pattern, [case_sensitive])<\/code>. \u010cia <code>value<\/code>yra tikslinis bandymo tekstas, <code>pattern<\/code>ie\u0161komas REGEX \u0161ablonas, o did\u017ei\u0173j\u0173 ir ma\u017e\u0173j\u0173 raid\u017ei\u0173 parametras yra neprivalomas.<\/p>\n<ul>\n<li>Pavyzd\u017eiui, i\u0161analizuokime \u201eExcel\u201c lap\u0105, kuriame yra produkt\u0173 arba s\u0105skait\u0173 kodai.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"Gamini\u0173 kod\u0173 pavyzdys\" class=\"wp-image\" decoding=\"async\" height=\"456\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-437-1.webp\" title=\"Gamini\u0173 kod\u0173 pavyzdys\" width=\"811\"\/><\/figure>\n<ul>\n<li>Nor\u0117dami patikrinti, ar kode yra skaitmen\u0173, spustel\u0117kite langel\u012f C4 ir \u012fveskite: <code>=REGEXTEST(A4, \"[0-9]\")<\/code>.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"REGEXTEST \u012fvestis langelyje C4\" class=\"wp-image\" decoding=\"async\" height=\"446\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-438-1.webp\" title=\"REGEXTEST \u012fvestis langelyje C4\" width=\"792\"\/><\/figure>\n<ul>\n<li>I\u0161vestyje bus rodoma \u201eTRUE\u201c, jei kode yra skaitmen\u0173.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"REGEXTEST TRUE i\u0161vestis\" class=\"wp-image\" decoding=\"async\" height=\"476\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-439-1.webp\" title=\"REGEXTEST TRUE i\u0161vestis\" width=\"846\"\/><\/figure>\n<ul>\n<li>Nor\u0117dami tai pakartoti kituose langeliuose, paimkite u\u017epildymo ranken\u0117l\u0119 formul\u0117s langelio kampe ir vilkite \u017eemyn, kad automati\u0161kai u\u017epildytum\u0117te gretimus langelius.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"Formul\u0117s vilkimas \u017eemyn, kad u\u017epildytum\u0117te langelius\" class=\"wp-image\" decoding=\"async\" height=\"430\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-440-1.webp\" title=\"Formul\u0117s vilkimas \u017eemyn, kad u\u017epildytum\u0117te langelius\" width=\"764\"\/><\/figure>\n<ul>\n<li>Nor\u0117dami patikrinti telefono numerius, naudokite: <code>=REGEXTEST(A4, \"\\(?\\d{3}\\)?[-.\\s]?\\d{4}\")<\/code>.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"REGEXTEST telefon\u0173 numeriams\" class=\"wp-image\" decoding=\"async\" height=\"478\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-443-1.webp\" title=\"REGEXTEST telefon\u0173 numeriams\" width=\"849\"\/><\/figure>\n<ul>\n<li>Kaip ir anks\u010diau, vilkite ranken\u0117l\u0119, kad pritaikytum\u0117te papildomiems langeliams.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"Telefono numerio tikrinimo vilkimo parinktis\" class=\"wp-image\" decoding=\"async\" height=\"455\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-444-1.webp\" title=\"Telefono numerio tikrinimo vilkimo parinktis\" width=\"809\"\/><\/figure>\n<h2 id=\"using-the-regexextract-function\">Teksto i\u0161traukimas naudojant REGEXEXTRACT<\/h2>\n<p>Funkcija REGEXEXTRACT specializuojasi tam tikr\u0173 teksto segment\u0173 i\u0161traukime i\u0161 nurodytos reik\u0161m\u0117s. Jo sintaks\u0117 yra <code>REGEXEXTRACT(value, pattern, [first_match], [all_matches], [case_sensitive])<\/code>. \u010cia nurodote, <code>value<\/code>i\u0161 ko i\u0161gauti, ir <code>pattern<\/code>apibr\u0117\u017eiate, k\u0105 i\u0161gauti. Pasirenkamas parametras leid\u017eia naudotojams pasirinkti, ar rasti tik pirm\u0105j\u0105 atitikt\u012f, ar visus \u012fvykius, ir ar paie\u0161koje skiriamos did\u017eiosios ir ma\u017eosios raid\u0117s.<\/p>\n<ul>\n<li>T\u0119sdami ankstesn\u012f pavyzd\u012f, galime skland\u017eiai nuskaityti telefono numerius.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"Naudojamo REGEXEXTRACT pavyzdys\" class=\"wp-image\" decoding=\"async\" height=\"439\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-445-1.webp\" title=\"Naudojamo REGEXEXTRACT pavyzdys\" width=\"780\"\/><\/figure>\n<ul>\n<li>Nor\u0117dami i\u0161gauti telefono numerius, naudokite: <code>=REGEXEXTRACT(A4, \"(\\\\d{3})?[-.\\s]?\\\\d{3}[-.\\s]?\\\\d{4}\")<\/code>langelyje C4.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"REGEXEXTRACT \u012fvestis\" class=\"wp-image\" decoding=\"async\" height=\"517\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-446-1.webp\" title=\"REGEXEXTRACT \u012fvestis\" width=\"919\"\/><\/figure>\n<ul>\n<li>\u012evedus \u0161i\u0105 formul\u0119, jei nurodytas langelis turi telefono numer\u012f, jis bus rodomas kaip i\u0161vestis. Vilkite formul\u0119 \u017eemyn, kad pritaikytum\u0117te \u0161\u012f i\u0161skyrim\u0105 kitoms l\u0105stel\u0117ms.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"REGEXEXTRACT telefono numeri\u0173 i\u0161vestis\" class=\"wp-image\" decoding=\"async\" height=\"445\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-447-1.webp\" title=\"REGEXEXTRACT telefono numeri\u0173 i\u0161vestis\" width=\"791\"\/><\/figure>\n<ul>\n<li>Nor\u0117dami gauti papildom\u0173 atitik\u010di\u0173, pakeiskite \u012f: <code>=REGEXEXTRACT(A4, \"(\\\\d{3})?[-.\\s]?\\\\d{3}[-.\\s]?\\\\d{4}\", 1)<\/code>.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"REGEXEXTRACT naudojimas kelioms rungtyn\u0117ms\" class=\"wp-image\" decoding=\"async\" height=\"483\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-448-2.webp\" title=\"REGEXEXTRACT naudojimas kelioms rungtyn\u0117ms\" width=\"858\"\/><\/figure>\n<h2 id=\"using-the-regexreplace-function\">Pakeiskite duomen\u0173 \u0161ablonus naudodami REGEXREPLACE<\/h2>\n<p>Funkcija REGEXREPLACE leid\u017eia rasti konkre\u010dius duomen\u0173 \u0161ablonus ir pakeisti juos kitu formatu. Sintaks\u0117 yra <code>REGEXREPLACE(value, pattern, replacement, [occurrence], [case_sensitive])<\/code>. Tai <code>value<\/code>, k\u0105 norite pakeisti, <code>pattern<\/code>yra tai, k\u0105 norite rasti, ir <code>replacement<\/code>apibr\u0117\u017eia, kuo norite pakeisti.<\/p>\n<ul>\n<li>Naudodami ankstesnius duomenis, taikykite funkcij\u0105 taip: <code>=REGEXREPLACE(A4, \"(\\\\d{3})?[-.\\s]?\\\\d{3}[-.\\s]?\\\\d{4}\", \"$1-$2-$3\")<\/code>langelyje C4, kad standartizuot\u0173 telefono numeri\u0173 formatus.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"REGEXREPLACE pavyzdys telefon\u0173 numeriams standartizuoti\" class=\"wp-image\" decoding=\"async\" height=\"493\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-449-1.webp\" title=\"REGEXREPLACE pavyzdys telefon\u0173 numeriams standartizuoti\" width=\"876\"\/><\/figure>\n<h3 id=\"practical-tips-for-regex\">Pagrindiniai REGEX funkcij\u0173 naudojimo patarimai<\/h3>\n<ul>\n<li>Susipa\u017einkite su tokiais prieigos raktais kaip \u201e{}\u201c ir \u201e[]\u201c, kurie yra labai svarb\u016bs kuriant efektyvius REGEX \u0161ablonus.<\/li>\n<li>\u012esitikinkite, kad j\u016bs\u0173 duomenys yra gerai strukt\u016brizuoti, kad i\u0161vengtum\u0117te komplikacij\u0173 naudojant REGEX funkcijas.<\/li>\n<li>Apsvarstykite galimyb\u0119 naudoti s\u0105lygin\u012f formatavim\u0105 kartu su REGEX, kad pagerintum\u0117te duomen\u0173 vizualizavim\u0105 ir analiz\u0119.<\/li>\n<li>\u201eExcel\u201c si\u016blo kitus duomen\u0173 i\u0161gavimo b\u016bdus, pvz., \u201eFlash Fill\u201c ir TEXTSPLIT, kad dar labiau praturtint\u0173 duomen\u0173 tvarkymo \u012franki\u0173 rinkin\u012f.<\/li>\n<\/ul>\n<p>Norintiems geriau suprasti REGEX funkcijas programoje \u201eExcel\u201c, \u017er. oficiali\u0105 dokumentacij\u0105 <a href=\"https:\/\/support.microsoft.com\/en-us\/office\/excel-help-55 Cbd404-814f-5300-8357-7697ff98bca4\" rel=\"noopener noreferrer nofollow\" target=\"_blank\">\u201eMicrosoft Excel\u201c pagalbos puslapyje<\/a> arba per\u017ei\u016br\u0117kite <a href=\"https:\/\/www.regular-expressions.info\/\" rel=\"noopener noreferrer nofollow\" target=\"_blank\">\u0161\u012f i\u0161sam\u0173 REGEX vadov\u0105<\/a> .<\/p>\n<h2>Da\u017enai u\u017eduodami klausimai<\/h2>\n<h3><strong>1. Kam naudojamos REGEX funkcijos programoje Excel?<\/strong><\/h3>\n<p>\u201eExcel\u201c REGEX funkcijos leid\u017eia vartotojams efektyviai rasti konkre\u010dius \u0161ablonus, i\u0161gauti informacij\u0105 arba pakeisti tekst\u0105 l\u0105stel\u0117se. Jie gali b\u016bti ne\u012fkainojami tikrinant duomenis arba transformuojant duomen\u0173 rinkinius, kad jie atitikt\u0173 konkre\u010dius formatavimo standartus.<\/p>\n<h3><strong>2. Kaip u\u017etikrinti, kad mano REGEX modeliai b\u016bt\u0173 teisingi?<\/strong><\/h3>\n<p>REGEX \u0161ablon\u0173 tikrinimas naudojant atskir\u0105 REGEX \u012frank\u012f arba internetiniai REGEX testeriai gali pad\u0117ti u\u017etikrinti tikslum\u0105 prie\u0161 juos taikant programoje Excel. Be to, prad\u0117kite nuo paprast\u0173 \u0161ablon\u0173 ir palaipsniui didinkite sud\u0117tingum\u0105, kad i\u0161vengtum\u0117te sintaks\u0117s problem\u0173.<\/p>\n<h3><strong>3. Ar galiu naudoti REGEX \u012fvairi\u0173 tip\u0173 duomenims, i\u0161skyrus telefono numerius?<\/strong><\/h3>\n<p>absoliu\u010diai! REGEX yra universalus ir gali b\u016bti naudojamas \u012fvairi\u0173 tip\u0173 duomenims identifikuoti, i\u0161gauti arba pakeisti, \u012fskaitant el. pa\u0161to adresus, URL adresus, produkt\u0173 kodus ir bet koki\u0105 teksto eilut\u0119, kuri seka atpa\u017e\u012fstamu \u0161ablonu.<\/p>\n<p><a class=\"xiaomi\" href=\"https:\/\/allthings.how\/how-to-use-the-regex-functions-in-excel\/\" rel=\"noopener noreferrer nofollow\" target=\"_blank\">\u0160altinis ir vaizdai<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>2024 m. \u201eMicrosoft\u201c i\u0161pl\u0117t\u0117 \u201eExcel\u201c galimybes pristatydama tris galingas funkcijas, orientuotas \u012f reguliari\u0105sias i\u0161rai\u0161kas (REGEX). \u0160ios funkcijos, b\u016btent REGEXTEST, REGEXEXTRACT ir REGEXREPLACE, suteikia vartotojams galimyb\u0119 efektyviai identifikuoti, i\u0161gauti ir pakeisti sud\u0117tingus duomen\u0173 \u0161ablonus. Juos galima pasiekti naudojant \u201eExcel\u201c \u017einiatinklio versij\u0105 ir \u201eMicrosoft 365\u201c, tod\u0117l naudotojai gali \u017eymiai geriau valdyti duomenis. \u201eExcel\u201c REGEX funkcij\u0173 supratimas Reguliarios [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[123,282],"class_list":["post-7446","post","type-post","status-publish","format-standard","hentry","category-how-to","tag-microsoft","tag-microsoft-excel"],"acf":[],"_links":{"self":[{"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/posts\/7446","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/comments?post=7446"}],"version-history":[{"count":1,"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/posts\/7446\/revisions"}],"predecessor-version":[{"id":7447,"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/posts\/7446\/revisions\/7447"}],"wp:attachment":[{"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/media?parent=7446"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/categories?post=7446"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/howtogeek.blog\/lt\/wp-json\/wp\/v2\/tags?post=7446"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}