{"id":7677,"date":"2025-01-24T08:58:46","date_gmt":"2025-01-24T08:58:46","guid":{"rendered":"https:\/\/howtogeek.blog\/cs\/?p=7677"},"modified":"2025-01-24T08:58:46","modified_gmt":"2025-01-24T08:58:46","slug":"a-comprehensive-guide-to-using-regex-functions-in-excel","status":"publish","type":"post","link":"https:\/\/howtogeek.blog\/cs\/a-comprehensive-guide-to-using-regex-functions-in-excel\/","title":{"rendered":"Komplexn\u00ed pr\u016fvodce pou\u017e\u00edv\u00e1n\u00edm funkc\u00ed REGEX v Excelu"},"content":{"rendered":"<p>V roce 2024 Microsoft pov\u00fd\u0161il mo\u017enosti Excelu zaveden\u00edm t\u0159\u00ed v\u00fdkonn\u00fdch funkc\u00ed zam\u011b\u0159en\u00fdch na regul\u00e1rn\u00ed v\u00fdrazy (REGEX). Tyto funkce, jmenovit\u011b REGEXTEST, REGEXEXTRACT a REGEXREPLACE, umo\u017e\u0148uj\u00ed u\u017eivatel\u016fm efektivn\u011b identifikovat, extrahovat a nahrazovat slo\u017eit\u00e9 vzory dat. Jsou dostupn\u00e9 ve webov\u00e9 verzi Excelu a v r\u00e1mci Microsoft 365, co\u017e u\u017eivatel\u016fm v\u00fdrazn\u011b zlep\u0161uje manipulaci s daty.<\/p>\n<h2 id=\"introduction-to-regex\">Pochopen\u00ed funkc\u00ed REGEX v Excelu<\/h2>\n<p>Regul\u00e1rn\u00ed v\u00fdrazy jsou sekvence znak\u016f, kter\u00e9 tvo\u0159\u00ed vyhled\u00e1vac\u00ed vzor, \u200b\u200bco\u017e usnad\u0148uje podrobnou anal\u00fdzu dat. Tyto funkce transformuj\u00ed zp\u016fsob, jak\u00fdm u\u017eivatel\u00e9 aplikace Excel interaguj\u00ed se sv\u00fdmi datov\u00fdmi sadami, a zjednodu\u0161uj\u00ed a intuitivuj\u00ed \u00fakoly, jako je ov\u011b\u0159ov\u00e1n\u00ed dat, extrakce a \u00fapravy.<\/p>\n<h2 id=\"using-the-regextest-function\">Vyu\u017eit\u00ed funkce REGEXTEST<\/h2>\n<p>Funkce REGEXTEST zji\u0161\u0165uje, zda v \u010d\u00e1sti textu existuje zadan\u00fd vzor, \u200b\u200ba vytv\u00e1\u0159\u00ed v\u00fdstup TRUE nebo FALSE. Syntaxe funkce je <code>REGEXTEST(value, pattern, [case_sensitive])<\/code>. Zde <code>value<\/code>je c\u00edlov\u00fd text pro testov\u00e1n\u00ed, <code>pattern<\/code>je hledan\u00fd vzor REGEX a parametr rozli\u0161uj\u00edc\u00ed mal\u00e1 a velk\u00e1 p\u00edsmena je voliteln\u00fd.<\/p>\n<ul>\n<li>Poj\u010fme nap\u0159\u00edklad analyzovat list Excelu obsahuj\u00edc\u00ed k\u00f3dy produkt\u016f nebo \u00fa\u010dt\u016f.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"P\u0159\u00edklad k\u00f3d\u016f produkt\u016f\" class=\"wp-image\" decoding=\"async\" height=\"456\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-437-1.webp\" title=\"P\u0159\u00edklad k\u00f3d\u016f produkt\u016f\" width=\"811\"\/><\/figure>\n<ul>\n<li>Chcete-li ov\u011b\u0159it, zda k\u00f3d obsahuje n\u011bjak\u00e9 \u010d\u00edslice, klikn\u011bte na bu\u0148ku C4 a zadejte: <code>=REGEXTEST(A4, \"[0-9]\")<\/code>.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"Vstup do bu\u0148ky C4 pro REGEXTEST\" class=\"wp-image\" decoding=\"async\" height=\"446\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-438-1.webp\" title=\"Vstup do bu\u0148ky C4 pro REGEXTEST\" width=\"792\"\/><\/figure>\n<ul>\n<li>Pokud jsou v k\u00f3du n\u011bjak\u00e9 \u010d\u00edslice, na v\u00fdstupu se zobraz\u00ed &#8216;TRUE&#8217;.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"V\u00fdstup REGEXTEST TRUE\" class=\"wp-image\" decoding=\"async\" height=\"476\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-439-1.webp\" title=\"V\u00fdstup REGEXTEST TRUE\" width=\"846\"\/><\/figure>\n<ul>\n<li>Chcete-li to replikovat na dal\u0161\u00ed bu\u0148ky, uchopte \u00fachyt v\u00fdpln\u011b v rohu bu\u0148ky vzorce a p\u0159et\u00e1hn\u011bte jej dol\u016f, aby se automaticky vyplnily sousedn\u00ed bu\u0148ky.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"P\u0159eta\u017een\u00edm vzorce dol\u016f vypl\u0148te bu\u0148ky\" class=\"wp-image\" decoding=\"async\" height=\"430\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-440-1.webp\" title=\"P\u0159eta\u017een\u00edm vzorce dol\u016f vypl\u0148te bu\u0148ky\" width=\"764\"\/><\/figure>\n<ul>\n<li>Chcete-li zkontrolovat telefonn\u00ed \u010d\u00edsla, pou\u017eijte: <code>=REGEXTEST(A4, \"\\(?\\d{3}\\)?[-.\\s]?\\d{4}\")<\/code>.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"REGEXTEST pro telefonn\u00ed \u010d\u00edsla\" 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 pro telefonn\u00ed \u010d\u00edsla\" width=\"849\"\/><\/figure>\n<ul>\n<li>Stejn\u011b jako d\u0159\u00edve p\u0159et\u00e1hn\u011bte \u00fachyt pro pou\u017eit\u00ed na dal\u0161\u00ed bu\u0148ky.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"Mo\u017enost p\u0159eta\u017een\u00ed pro kontrolu telefonn\u00edho \u010d\u00edsla\" class=\"wp-image\" decoding=\"async\" height=\"455\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-444-1.webp\" title=\"Mo\u017enost p\u0159eta\u017een\u00ed pro kontrolu telefonn\u00edho \u010d\u00edsla\" width=\"809\"\/><\/figure>\n<h2 id=\"using-the-regexextract-function\">Extrahov\u00e1n\u00ed textu pomoc\u00ed REGEXEXTRACT<\/h2>\n<p>Funkce REGEXEXTRACT se specializuje na vytahov\u00e1n\u00ed konkr\u00e9tn\u00edch textov\u00fdch segment\u016f z dan\u00e9 hodnoty. Jeho syntaxe je <code>REGEXEXTRACT(value, pattern, [first_match], [all_matches], [case_sensitive])<\/code>. Zde ur\u010d\u00edte, <code>value<\/code>z \u010deho se m\u00e1 extrahovat, a <code>pattern<\/code>obrysy, z \u010deho se m\u00e1 extrahovat. Voliteln\u00fd parametr umo\u017e\u0148uje u\u017eivatel\u016fm vybrat si mezi vyhled\u00e1n\u00edm pouze prvn\u00ed shody nebo v\u0161ech v\u00fdskyt\u016f a zda se p\u0159i vyhled\u00e1v\u00e1n\u00ed rozli\u0161uj\u00ed velk\u00e1 a mal\u00e1 p\u00edsmena.<\/p>\n<ul>\n<li>Pokra\u010dujeme-li v na\u0161em p\u0159edchoz\u00edm p\u0159\u00edkladu, m\u016f\u017eeme bez probl\u00e9m\u016f na\u010d\u00edst telefonn\u00ed \u010d\u00edsla.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"P\u0159\u00edklad pou\u017eit\u00ed REGEXEXTRACT\" class=\"wp-image\" decoding=\"async\" height=\"439\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-445-1.webp\" title=\"P\u0159\u00edklad pou\u017eit\u00ed REGEXEXTRACT\" width=\"780\"\/><\/figure>\n<ul>\n<li>Chcete-li z\u00edskat telefonn\u00ed \u010d\u00edsla, pou\u017eijte: <code>=REGEXEXTRACT(A4, \"(\\\\d{3})?[-.\\s]?\\\\d{3}[-.\\s]?\\\\d{4}\")<\/code>v bu\u0148ce C4.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"Vstup pro REGEXEXTRACT\" class=\"wp-image\" decoding=\"async\" height=\"517\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-446-1.webp\" title=\"Vstup pro REGEXEXTRACT\" width=\"919\"\/><\/figure>\n<ul>\n<li>Pokud m\u00e1 odkazovan\u00e1 bu\u0148ka po zad\u00e1n\u00ed tohoto vzorce telefonn\u00ed \u010d\u00edslo, zobraz\u00ed se jako v\u00fdstup. P\u0159eta\u017een\u00edm vzorce dol\u016f pou\u017eijte tuto extrakci na dal\u0161\u00ed bu\u0148ky.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"REGEXEXTRACT V\u00fdstup pro telefonn\u00ed \u010d\u00edsla\" 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 V\u00fdstup pro telefonn\u00ed \u010d\u00edsla\" width=\"791\"\/><\/figure>\n<ul>\n<li>Pro dal\u0161\u00ed shody upravte na: <code>=REGEXEXTRACT(A4, \"(\\\\d{3})?[-.\\s]?\\\\d{3}[-.\\s]?\\\\d{4}\", 1)<\/code>.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"Pou\u017eit\u00ed REGEXEXTRACT pro v\u00edce shod\" class=\"wp-image\" decoding=\"async\" height=\"483\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-448-2.webp\" title=\"Pou\u017eit\u00ed REGEXEXTRACT pro v\u00edce shod\" width=\"858\"\/><\/figure>\n<h2 id=\"using-the-regexreplace-function\">Zm\u011b\u0148te datov\u00e9 vzory pomoc\u00ed REGEXREPLACE<\/h2>\n<p>Funkce REGEXREPLACE v\u00e1m umo\u017e\u0148uje naj\u00edt konkr\u00e9tn\u00ed vzory v datech a nahradit je alternativn\u00edm form\u00e1tem. Syntaxe je <code>REGEXREPLACE(value, pattern, replacement, [occurrence], [case_sensitive])<\/code>. To <code>value<\/code>je to, co chcete nahradit, <code>pattern<\/code>je to, co chcete naj\u00edt, a <code>replacement<\/code>definuje, v \u010dem nahrazujete.<\/p>\n<ul>\n<li>S vyu\u017eit\u00edm p\u0159edchoz\u00edch dat pou\u017eijte funkci n\u00e1sledovn\u011b: <code>=REGEXREPLACE(A4, \"(\\\\d{3})?[-.\\s]?\\\\d{3}[-.\\s]?\\\\d{4}\", \"$1-$2-$3\")<\/code>v bu\u0148ce C4 pro standardizaci form\u00e1t\u016f telefonn\u00edch \u010d\u00edsel.<\/li>\n<\/ul>\n<figure class=\"wp-block-image\"><img alt=\"P\u0159\u00edklad REGEXREPLACE pro standardizaci telefonn\u00edch \u010d\u00edsel\" class=\"wp-image\" decoding=\"async\" height=\"493\" loading=\"lazy\" src=\"https:\/\/cdn.howtogeek.blog\/wp-content\/uploads\/2025\/01\/image-449-1.webp\" title=\"P\u0159\u00edklad REGEXREPLACE pro standardizaci telefonn\u00edch \u010d\u00edsel\" width=\"876\"\/><\/figure>\n<h3 id=\"practical-tips-for-regex\">Z\u00e1kladn\u00ed tipy pro pou\u017e\u00edv\u00e1n\u00ed funkc\u00ed REGEX<\/h3>\n<ul>\n<li>Seznamte se s tokeny jako &#8216;{}&#8217; a &#8216;[]&#8217;, kter\u00e9 jsou z\u00e1sadn\u00ed pro vytv\u00e1\u0159en\u00ed efektivn\u00edch vzor\u016f REGEX.<\/li>\n<li>Ujist\u011bte se, \u017ee jsou va\u0161e data dob\u0159e strukturovan\u00e1, abyste p\u0159ede\u0161li komplikac\u00edm p\u0159i pou\u017e\u00edv\u00e1n\u00ed funkc\u00ed REGEX.<\/li>\n<li>Zva\u017ete vyu\u017eit\u00ed podm\u00edn\u011bn\u00e9ho form\u00e1tov\u00e1n\u00ed spolu s REGEX ke zlep\u0161en\u00ed vizualizace a anal\u00fdzy dat.<\/li>\n<li>Excel nab\u00edz\u00ed dal\u0161\u00ed techniky extrakce dat, jako je Flash Fill a TEXTSPLIT, k dal\u0161\u00edmu obohacen\u00ed va\u0161\u00ed sady n\u00e1stroj\u016f pro manipulaci s daty.<\/li>\n<\/ul>\n<p>Pro ty, kte\u0159\u00ed cht\u011bj\u00ed prohloubit sv\u00e9 znalosti funkc\u00ed REGEX v Excelu, nahl\u00e9dn\u011bte do ofici\u00e1ln\u00ed dokumentace na <a href=\"https:\/\/support.microsoft.com\/en-us\/office\/excel-help-55 Cbd404-814f-5300-8357-7697ff98bca4\" rel=\"noopener noreferrer nofollow\" target=\"_blank\">str\u00e1nce n\u00e1pov\u011bdy aplikace Microsoft Excel<\/a> nebo si prohl\u00e9dn\u011bte <a href=\"https:\/\/www.regular-expressions.info\/\" rel=\"noopener noreferrer nofollow\" target=\"_blank\">tuto komplexn\u00ed p\u0159\u00edru\u010dku REGEX<\/a> .<\/p>\n<h2>\u010casto kladen\u00e9 ot\u00e1zky<\/h2>\n<h3><strong>1. K \u010demu slou\u017e\u00ed funkce REGEX v Excelu?<\/strong><\/h3>\n<p>Funkce REGEX v Excelu umo\u017e\u0148uj\u00ed u\u017eivatel\u016fm efektivn\u011b naj\u00edt konkr\u00e9tn\u00ed vzory, extrahovat informace nebo nahradit text v bu\u0148k\u00e1ch. Mohou b\u00fdt neoceniteln\u00e9 pro ov\u011b\u0159ov\u00e1n\u00ed dat nebo transformaci datov\u00fdch sad, aby spl\u0148ovaly specifick\u00e9 standardy form\u00e1tov\u00e1n\u00ed.<\/p>\n<h3><strong>2. Jak zajist\u00edm, \u017ee m\u00e9 vzory REGEX jsou spr\u00e1vn\u00e9?<\/strong><\/h3>\n<p>Testov\u00e1n\u00ed vzor\u016f REGEX v samostatn\u00e9m n\u00e1stroji REGEX nebo pomoc\u00ed online tester\u016f REGEX m\u016f\u017ee pomoci zajistit p\u0159esnost p\u0159ed jejich pou\u017eit\u00edm v Excelu. Nav\u00edc za\u010dn\u011bte s jednoduch\u00fdmi vzory a postupn\u011b zvy\u0161ujte slo\u017eitost, abyste se vyhnuli probl\u00e9m\u016fm se syntax\u00ed.<\/p>\n<h3><strong>3. Mohu pou\u017e\u00edt REGEX pro r\u016fzn\u00e9 typy dat krom\u011b telefonn\u00edch \u010d\u00edsel?<\/strong><\/h3>\n<p>Absolutn\u011b! REGEX je univerz\u00e1ln\u00ed a lze jej pou\u017e\u00edt k identifikaci, extrahov\u00e1n\u00ed nebo nahrazen\u00ed r\u016fzn\u00fdch typ\u016f dat, v\u010detn\u011b e-mailov\u00fdch adres, adres URL, k\u00f3d\u016f produkt\u016f a jak\u00e9hokoli \u0159et\u011bzce textu, kter\u00fd sleduje rozpoznateln\u00fd vzor.<\/p>\n<p><a class=\"xiaomi\" href=\"https:\/\/allthings.how\/how-to-use-the-regex-functions-in-excel\/\" rel=\"noopener noreferrer nofollow\" target=\"_blank\">Zdroj a obr\u00e1zky<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>V roce 2024 Microsoft pov\u00fd\u0161il mo\u017enosti Excelu zaveden\u00edm t\u0159\u00ed v\u00fdkonn\u00fdch funkc\u00ed zam\u011b\u0159en\u00fdch na regul\u00e1rn\u00ed v\u00fdrazy (REGEX). Tyto funkce, jmenovit\u011b REGEXTEST, REGEXEXTRACT a REGEXREPLACE, umo\u017e\u0148uj\u00ed u\u017eivatel\u016fm efektivn\u011b identifikovat, extrahovat a nahrazovat slo\u017eit\u00e9 vzory dat. Jsou dostupn\u00e9 ve webov\u00e9 verzi Excelu a v r\u00e1mci Microsoft 365, co\u017e u\u017eivatel\u016fm v\u00fdrazn\u011b zlep\u0161uje manipulaci s daty. Pochopen\u00ed funkc\u00ed REGEX v [&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":[126,283],"class_list":["post-7677","post","type-post","status-publish","format-standard","hentry","category-how-to","tag-microsoft","tag-microsoft-excel"],"acf":[],"_links":{"self":[{"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/posts\/7677","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/comments?post=7677"}],"version-history":[{"count":1,"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/posts\/7677\/revisions"}],"predecessor-version":[{"id":7678,"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/posts\/7677\/revisions\/7678"}],"wp:attachment":[{"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/media?parent=7677"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/categories?post=7677"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/howtogeek.blog\/cs\/wp-json\/wp\/v2\/tags?post=7677"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}