Víme, že funkce TRIM a CLEAN Excel se používají k vyčištění netisknutelných znaků a nadbytečných mezer od řetězců, ale příliš nepomáhají při identifikaci řetězců obsahujících jakýkoli speciální znak jako @ nebo! atd. V takových případech používáme UDF.
Pokud tedy chcete vědět, zda řetězec obsahuje nějaké speciální znaky, nenajdete k tomu žádný vzorec ani funkci Excelu. Speciální znaky, které jsou na klávesnici, můžeme najít ručním zadáním, ale nemůžete vědět, zda jsou v řetězci symboly nebo ne.
Nalezení speciálních znaků může být velmi důležité pro účely čištění dat. A v některých případech se to musí udělat. Jak to tedy uděláme v Excelu? Jak můžeme vědět, zda řetězec obsahuje nějaké speciální znaky? Můžeme k tomu použít UDF.
Níže uvedený vzorec vrátí hodnotu TRUE, pokud jakákoli buňka obsahuje jiné znaky než 1 až 0 a od A do Z (v obou případech). Pokud nenajde žádné speciální znaky, vrátí FALSE.
Obecný vzorec
| = Obsahuje speciální znaky (řetězec) |
Tětiva: Řetězec, u kterého chcete zkontrolovat speciální znaky.
Aby tento vzorec fungoval, budete muset do modulu sešitu vložit níže uvedený kód.
Otevřete tedy Excel. Stisknutím ALT+F11 otevřete VBE. Vložte modul z nabídky Vložit. Zkopírujte níže uvedený kód a vložte jej do modulu.
Funkce obsahuje speciální znaky (str jako řetězec) jako booleovský pro I = 1 až Len (str) ch = střední (str, I, 1) Vyberte Case ch Case "0" To "9", "A" To "Z", "a "Do" z "," "ContainsSpecialChar Character = False Case Else ContainsSpecialChar Character = True Exit For End Select Next End Function
Nyní je funkce připravena k použití.
Přejděte na list v sešitu, který obsahuje řetězce, které chcete zkontrolovat.
Do buňky C2 napište následující vzorec:
| = Obsahuje speciální znaky (B13) |
Vrací TRUE pro první řetězec, protože obsahuje speciální znak. Když zkopírujete vzorec dolů, zobrazí se FALSE pro řetězec B14 atd.
Ale kupodivu ukazuje TRUE pro poslední řetězec "Exceltip.com". Je to proto, že obsahuje tečku (.). Ale proč? Prozkoumejme kód, abychom porozuměli.
Jak funkce funguje?
Procházíme všemi znaky řetězce pomocí funkce For loop a mid VBA. Funkce Mid extrahuje jeden znak najednou z řetězce a ukládá jej do proměnné ch.
For I = 1 To Len (str) ch = Mid (str, I, 1)
Nyní přichází hlavní část. Pomocí příkazu select case zkontrolujeme, co proměnná ch obsahuje.
Říkáme VBA, aby zkontroloval, zda ch obsahuje některou z definovaných hodnot. Definoval jsem 0 až 9, A až Z, a až z a "" (mezera). Pokud c
h obsahuje některou z nich, nastavili jsme její hodnotu na hodnotu False.
Vyberte Case ch Case "0" To "9", "A" To "Z", "a" To "z", "" ContainsSpecialCharacters = False
Můžete vidět, že v seznamu nemáme tečku (.), A proto dostaneme TRUE pro řetězec, který obsahuje tečku. Do seznamu můžete přidat libovolný znak, který bude ze vzorce vyňat.
Pokud ch obsahuje jakýkoli jiný znak než uvedené znaky, nastavíme výsledek funkce na True a ukončíme smyčku právě tam.
Případ Else ContainsSpecialCharacter = True Exit For
Tak jo, takhle to funguje. Pokud ale chcete vyčistit speciální znaky, budete muset ve funkci provést nějaké změny.
Jak vyčistit speciální znaky z řetězců v aplikaci Excel?
Chcete -li vyčistit speciální znaky z řetězce v aplikaci Excel, vytvořil jsem ve VBA další vlastní funkci. Syntaxe funkce je:
| = CleanSpecialCharacter (řetězec) |
Tato funkce vrací vyčištěnou verzi řetězce, který byl do ní předán. Nový řetězec nebude obsahovat žádné speciální znaky.
Kód této funkce vypadá takto:
Funkce CleanSpecialCharacter (str As String) Dim nstr As String nstr = str For I = 1 To Len (str) ch = Mid (str, I, 1) Select Case ch Case "0" To "9", "A" To " Z "," a "Do" z "," "'Nedělat nic Případ Jiný nstr = Nahradit (nstr, ch," ") Konec Vybrat Další CleanSpecialChar Character = nstr Koncová funkce
Zkopírujte to do stejného modulu, který jste zkopírovali do výše uvedeného kódu.
Když použijete tento vzorec na řetězce, výsledky budou následující:
Můžete vidět, že každý speciální znak je odebrán z řetězce včetně tečky.
Jak to funguje?
Funguje stejně jako výše uvedená funkce. Jediným rozdílem je, že tato funkce nahradí každý speciální znak nulovým znakem. Vytvoří nový řetězec a vrátí tento řetězec.
Vyberte Case ch Case "0" To "9", "A" To "Z", "a" To "z", "" 'ContainsSpecialChar Character = False Case Else nstr = Replace (nstr, ch, "") End Select
Pokud chcete vyjmout jakýkoli znak z čištění, můžete jej přidat do seznamu v kódu. Excel tento speciální znak promine.
Takže ano, lidi, takto můžete najít a nahradit speciální znaky z řetězce v aplikaci Excel. Doufám, že to bylo dostatečně vysvětlující a užitečné. Pokud vám to nepomůže při řešení vašeho problému, dejte nám vědět v sekci komentáře níže.
Jak používat funkci TRIM v Excelu: Funkce TRIM se používá k oříznutí řetězců a vyčištění všech koncových nebo úvodních mezer od řetězce. To nám hodně pomáhá v procesu čištění dat.
Jak používat funkci CLEAN v Excelu: Funkce Clean se používá k vyčištění netisknutelných znaků z řetězce. Tato funkce se většinou používá s funkcí TRIM k vyčištění importovaných cizích dat.
Nahradit text od konce řetězce počínaje od proměnné polohy: K nahrazení textu z konce řetězce používáme funkci VYMĚNIT. Funkce REPLACE používá k nahrazení pozici textu v řetězci.
Jak zkontrolovat, zda řetězec obsahuje jeden z mnoha textů v aplikaci Excel: Abychom zjistili, zda řetězec obsahuje některý z více textů, použijeme tento vzorec. Pomocí funkce SUMA sečteme všechny shody a poté provedeme logiku, abychom zkontrolovali, zda řetězec obsahuje některý z více řetězců.
Počítejte buňky, které obsahují konkrétní text: Jednoduchá funkce COUNTIF udělá kouzlo. Pro počítání počtu více buněk, které obsahují daný řetězec, používáme zástupný operátor s funkcí COUNTIF.
Funkce Excel REPLACE vs SUBSTITUTE: Funkce REPLACE a SUBSTITUTE jsou nejvíce nepochopenými funkcemi. K nalezení a nahrazení daného textu používáme funkci SUBSTITUTE. Kde se REPLACE používá k nahrazení řady znaků v řetězci…
Oblíbené články:
50 zkratek aplikace Excel pro zvýšení produktivity | Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Jak používat funkci Excel VLOOKUP| Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která slouží k vyhledávání hodnot z různých rozsahů a listů.
Jak používat Excel Funkce COUNTIF| Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel | Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.