Při práci s VBA v Excelu může být potřeba rozdělit řetězec na různé části na základě oddělovače.
Pokud například máte adresu, můžete pomocí funkce VBA Split získat různé části adresy oddělené čárkou (což by v tomto případě znamenalo oddělovač).
SPLIT je vestavěná funkce řetězce v aplikaci Excel VBA, kterou můžete použít k rozdělení textového řetězce na základě oddělovače.
Funkce Excel VBA SPLIT - syntaxe
Rozdělit (výraz, [oddělovač], [mez], [porovnat])
- Výraz: Toto je řetězec, který chcete rozdělit na základě oddělovače. Například v případě příkladu adresy bude „výrazem“ celá adresa. V případě, že se jedná o řetězec nulové délky (“”), funkce SPLIT vrátí prázdné pole.
- Oddělovač: Toto je nepovinný argument. Toto je oddělovač, který se používá k rozdělení argumentu „Výraz“. V případě našeho příkladu adresy je čárka oddělovač, který se používá k rozdělení adresy na různé části. Pokud tento argument nezadáte, bude mezerník považován za výchozí oddělovač. Pokud zadáte řetězec nulové délky („“), funkce vrátí celý řetězec „Výraz“.
- Omezit: Toto je nepovinný argument. Zde zadáte celkový počet podřetězců, které chcete vrátit. Pokud například chcete vrátit pouze první tři podřetězce z argumentu „Výraz“, bude to 3. Pokud tento argument nezadáte, výchozí hodnota je -1, která vrátí všechny podřetězce.
- Porovnat: Toto je nepovinný argument. Zde určíte typ srovnání, které má funkce SPLIT provádět při vyhodnocování podřetězců. K dispozici jsou následující možnosti:
- Když je Porovnat 0: Toto je binární srovnání. To znamená, že pokud je vaším oddělovačem textový řetězec (řekněme ABC), pak by se rozlišovala velká a malá písmena. „ABC“ by se nerovnalo „abc“.
- Když je Porovnat 1: Toto je porovnání textu. To znamená, že pokud je vaším oddělovačem textový řetězec (řekněme ABC), pak i když v řetězci „Výraz“ máte „abc“, bude považován za oddělovač.
Nyní, když jsme pokryli základy funkce SPLIT, podívejme se na několik praktických příkladů.
Příklad 1 - Rozdělte slova ve větě
Předpokládejme, že mám text - „Rychlá hnědá liška přeskočí líného psa“.
Mohu použít funkci SPLIT, abych každé slovo této věty dostal jako samostatnou položku v poli.
Níže uvedený kód by k tomu:
Sub SplitWords () Dim TextStrng jako řetězec Dim Výsledek () Jako řetězec TextStrng = "Rychlá hnědá liška přeskočí líného psa" Výsledek () = Rozdělit (TextStrng) Konec Sub
Zatímco kód nedělá nic užitečného, pomůže vám pochopit, co funkce Split ve VBA dělá.
Funkce Split rozdělí textový řetězec a přiřadí každé slovo do pole Result.
Takže v tomto případě:
- Výsledek (0) ukládá hodnotu „The“
- Výsledek (1) ukládá hodnotu „Rychlý“
- Výsledek (2) ukládá hodnotu „Brown“ atd.
V tomto příkladu jsme zadali pouze první argument - text, který má být rozdělen. Protože nebyl určen žádný oddělovač, přebírá jako výchozí oddělovač znak mezery.
Důležitá poznámka:
- Funkce VBA SPLIT vrací pole, které začíná od základny 0.
- Je -li výsledek funkce SPLIT přiřazen poli, musí být toto pole deklarováno jako datový typ String. Pokud ho deklarujete jako datový typ Variant, zobrazí se chyba neshody typu). Ve výše uvedeném příkladu si všimněte, že jsem deklaroval Result () jako datový typ String.
Příklad 2 - Spočítejte počet slov ve větě
Pomocí funkce SPLIT můžete získat celkový počet slov ve větě. Jde o to spočítat počet prvků v poli, které získáte při rozdělení textu.
Níže uvedený kód by zobrazil okno se zprávou s počtem slov:
Dílčí WordCount () Dim TextStrng jako řetězec Dim WordCount jako Integer Dim Výsledek () jako řetězec TextStrng = "Rychlá hnědá liška přeskočí líného psa" Výsledek = Rozdělit (TextStrng) WordCount = UBound (Výsledek ()) + 1 MsgBox "The Počet slov je „& WordCount End Sub
V tomto případě nám funkce UBound říká horní hranici pole (tj. Maximální počet prvků, které pole má). Protože základ pole je 0, přidá se 1, aby se získal celkový počet slov.
Podobný kód můžete použít k vytvoření vlastní funkce ve VBA, která vezme text jako vstup a vrátí počet slov.
Níže uvedený kód vytvoří tuto funkci:
Funkce WordCount (CellRef As Range) Dim TextStrng As String Dim Result () As String Result = Split (WorksheetFunction.Trim (CellRef.Text), "") WordCount = UBound (Result ()) + 1 End Function
Po vytvoření můžete funkci WordCount používat stejně jako jakoukoli jinou běžnou funkci.
Tato funkce také zpracovává úvodní, koncové a dvojité mezery mezi slovy. To bylo možné pomocí funkce TRIM v kódu VBA.
V případě, že se chcete dozvědět více o tom, jak tento vzorec funguje při počítání počtu slov ve větě, nebo se chcete dozvědět více o způsobu, jak počet slov získat ve VBA, podívejte se na tento návod.
Příklad 3 - Použití oddělovače jiného než mezerníku
V předchozích dvou příkladech jsme ve funkci SPLIT použili pouze jeden argument a zbytek byly výchozí argumenty.
Když používáte nějaký jiný oddělovač, musíte to zadat ve vzorci SPLIT.
V níže uvedeném kódu funkce SPLIT vrací pole oddělené čárkou a poté zobrazí zprávu s každým slovem na samostatném řádku.
Sub CommaSeparator () Dim TextStrng As String Dim Result () As String Dim DisplayText As String TextStrng = "The, Quick, Brown, Fox, Jump, Over, The, Lazy, Dog" Result = Split (TextStrng, ",") For i = LBound (Result ()) To UBound (Result ()) DisplayText = DisplayText & Result (i) & vbNewLine Next i MsgBox DisplayText End Sub
Ve výše uvedeném kódu jsem použil smyčku For Next k procházení každého prvku pole „Výsledek“ a přiřazení proměnné „DisplayText“.
Příklad 4 - Rozdělte adresu na tři části
Pomocí funkce SPLIT můžete určit, kolik čísel rozdělení chcete získat. Pokud například nic nezadám, k rozdělení řetězce bude použita každá instance oddělovače.
Pokud ale zadám limit 3, bude řetězec rozdělen pouze na tři části.
Pokud mám například následující adresu:
2703 Winifred Way, Indianapolis, Indiana, 46204
Mohu použít funkci Split ve VBA k rozdělení této adresy na tři části.
Rozdělí první dva na základě oddělovače čárky a zbývající část se stane třetím prvkem pole.
Níže uvedený kód by zobrazoval adresu ve třech různých řádcích v okně se zprávou:
Sub CommaSeparator () Dim TextStrng As String Dim Result () As String Dim DisplayText As String TextStrng = "2703 Winifred Way, Indianapolis, Indiana, 46204" Result = Split (TextStrng, ",", 3) For i = LBound (Result (Result (Výsledek) )) To UBound (Result ()) DisplayText = DisplayText & Result (i) & vbNewLine Next i MsgBox DisplayText End Sub
Jedním z praktických využití by to mohlo být, když chcete rozdělit adresu jednoho řádku do formátu zobrazeného v okně zprávy. Poté můžete vytvořit vlastní funkci, která vrací adresu rozdělenou na tři části (s každou částí na novém řádku).
Následující kód by to udělal:
Funkce ThreePartAddress (cellRef As Range) Dim TextStrng As String Dim Result () As String Dim DisplayText As String Result = Split (cellRef, ",", 3) For i = LBound (Result ()) To UBound (Result ()) DisplayText = DisplayText & Trim (Result (i)) & vbNewLine Next i ThreePartAddress = Mid (DisplayText, 1, Len (DisplayText) - 1) End Function
Jakmile budete mít tento kód v modulu, můžete použít funkci (ThreePartAddress) v sešitu stejně jako všechny ostatní funkce Excelu.
Tato funkce má jeden argument - odkaz na buňku, který má adresu.
Všimněte si toho, že aby se výsledná adresa zobrazila ve třech různých řádcích, musíte na buňky použít formát zalamovacího textu (nachází se na kartě Domů ve skupině Zarovnání). Pokud není formát „Zabalit text“ povolen, uvidíte celou adresu jako jeden řádek.
Příklad 5 - Získejte název města z adresy
S funkcí Split ve VBA můžete určit, jakou část výsledného pole chcete použít.
Předpokládejme například, že rozděluji následující adresu na základě čárky jako oddělovače:
2703 Winifred Way, Indianapolis, Indiana, 46204
Výsledné pole bude vypadat nějak takto:
{"2703 Winifred Way", "Indianapolis", "Indiana", "46204"}
Protože se jedná o pole, mohu se rozhodnout zobrazit nebo vrátit konkrétní část tohoto pole.
Níže je uveden kód pro vlastní funkci, kde můžete zadat číslo a vrátí tento prvek z pole. Pokud například chci název státu, mohu zadat 3 (protože je to třetí prvek v poli).
Funkce ReturnNthElement (CellRef jako rozsah, ElementNumber jako celé číslo) Dim Result () As String Result = Split (CellRef, ",") ReturnNthElement = Result (ElementNumber - 1) End Function
Výše uvedená funkce přebírá dva argumenty, odkaz na buňku s adresou a číslo prvku, které chcete vrátit. Funkce Split rozdělí prvky adresy a přiřadí ji k proměnné Result.
Poté vrátí číslo prvku, které jste zadali jako druhý argument. Všimněte si toho, protože základna je 0, ElementNumber-1 se používá k vrácení správné části adresy.
V případě, že chcete název města, můžete jako druhý argument použít 2. V případě, že použijete číslo, které je vyšší než celkový počet prvků, vrátí se #HODNOTA! chyba.
Kód můžete dále zjednodušit, jak je uvedeno níže:
Funkce ReturnNthElement (CellRef jako rozsah, ElementNumber jako celé číslo) ReturnNthElement = Split (CellRef, ",") (ElementNumber - 1) Koncová funkce
Ve výše uvedeném kódu místo použití proměnné Result vrací pouze zadané číslo prvku.
Pokud tedy máte Split („Dobré ráno“) (0), vrátí pouze první prvek, který je „Dobrý“.
Podobně ve výše uvedeném kódu vrací pouze zadané číslo prvku.
