Použitie funkcie rozdelenia VBA
Funkcia VBA Split vám umožňuje oddeliť časti komponentov zo štandardného textového reťazca, kde každý komponent používa špecifický znak oddeľovača, napr. čiarka alebo dvojbodka. Na vyhľadanie oddeľovačov v reťazci a následné extrahovanie hodnôt je jednoduchšie použiť ako písanie kódu.
Dalo by sa to použiť, ak čítate v riadku z hodnoty oddelenej čiarkou (súbor CSV) alebo máte poštovú adresu, ktorá je na jednom riadku, ale chcete ju vidieť ako viac riadkov.
Syntax je:
| 1 | Rozdeliť výraz, oddeľovač [voliteľné], obmedziť [voliteľné], porovnať [voliteľné] |
Funkcia VBA Split má štyri parametre:
- Výraz - Reťazec textu, ktorý chcete rozdeliť na rôzne časti.
- Oddeľovač (voliteľné)- reťazec alebo netlačiteľný znak - Definuje znak oddeľovača, ktorý sa použije na rozdelenie. Ak nie je zadaný žiadny oddeľovač, použije sa predvolená medzera.
- Limit (voliteľné) - číslo - Definuje, koľko rozdelení sa vykoná. Ak je pole prázdne, v reťazci sa vykonajú všetky dostupné rozdelenia. Ak je nastavená na 1, nebudú sa robiť žiadne rozdelenia. V zásade vám to umožňuje oddeliť konkrétny počet hodnôt začínajúcich na začiatku reťazca, napr. kde reťazec je veľmi dlhý a potrebujete iba prvé tri medzičasy.
- Porovnaj (voliteľné) - Ak je oddeľovač textový znak, používa sa na prepínanie medzi tým, či oddeľovač rozlišuje malé alebo veľké písmená. Hodnoty sú vbBinaryCompare (rozlišujúce malé a veľké písmená) a vbTextCompare (nerozlišujú sa malé a veľké písmená).
Funkcia rozdelenia vždy vráti pole.
Jednoduchý príklad funkcie Split
| 123456789101112 | Sub SplitExample ()'Definujte premennéDim MyArray () ako reťazec, MyString ako reťazec, ja ako variant„Ukážkový reťazec s oddeľovačmi medzierMyString = "Jeden dva tri štyri"'Na rozdelenie jednotlivých častí reťazca použite funkciu RozdeliťMyArray = Split (MyString)'iterujte cez pole vytvorené na zobrazenie každej hodnotyZa každú I In MyArrayMsgBox IDalej jaKoniec pod |
V tomto prípade nie je určený žiadny oddeľovač, pretože všetky slová majú medzi sebou medzeru, takže je možné použiť predvolený oddeľovač (medzera).
Pole nemá žiadne rozmery a je nastavené ako reťazec. Premenná I, ktorá sa používa v slučke For … Next, musí byť dimenzovaná ako variant.
Po spustení tohto kódu sa zobrazia štyri polia so správami, jedno pre každé z rozdelení, napr. Jeden dva tri. Štyri.
Všimnite si toho, že ak je medzi slovami v reťazci dvojitá medzera, bude to vyhodnotené ako rozdelenie, aj keď v ňom nie je nič. Možno to nie je výsledok, ktorý by ste chceli vidieť.
Tento problém môžete vyriešiť pomocou funkcie Nahradiť a nahradiť všetky dvojité medzery jediným priestorom:
| 1 | MyString = Nahradiť (MyString, "", "") |
Koncový alebo vedúci priestor môže tiež spôsobovať problémy tým, že spôsobí prázdne rozdelenie. Tieto sú často veľmi ťažko viditeľné. Tieto nadbytočné medzery môžete odstrániť pomocou funkcie Orezať:
| 1 | MyString = Trim (MyString) |
Použitie funkcie rozdelenia so znakom oddeľovača
Môžeme použiť oddeľovač bodkočiarky (;). Často sa to nachádza v reťazcoch e -mailových adries na oddelenie adries. Možno vám bude zaslaný e -mail, ktorý je zdieľaný s niekoľkými kolegami, a vo svojom pracovnom hárku chcete vidieť zoznam, komu bol adresovaný. E -mailové adresy môžete jednoducho skopírovať z políčok „Na“ alebo „Kopírovať“ do svojho kódu.
| 123456789101112131415 | Sub SplitBySemicolonExample ()'Definujte premennéDim MyArray () ako reťazec, MyString ako reťazec, I ako variant, N ako celé číslo„Ukážkový reťazec s oddeľovačmi bodkočiarkyMyString = "[email protected]; [email protected]; [email protected]; [email protected]"'Na rozdelenie jednotlivých častí reťazca použite funkciu RozdeliťMyArray = Split (MyString, ";")„Vymažte pracovný listActiveSheet.UsedRange.Clear'iterujte cez polePre N = 0 až UBound (MyArray)'Každú e -mailovú adresu umiestnite do prvého stĺpca pracovného hárkaRozsah ("A" & N + 1). Hodnota = MyArray (N)Ďalej N.Koniec pod |
Všimnite si toho, že slučka For… Next sa používa na iteráciu cez pole. Prvý prvok v poli vždy začína od nuly a na získanie maximálneho počtu prvkov sa používa funkcia Upper Bound.
Po spustení tohto kódu bude váš pracovný hárok vyzerať takto:
Použitie limitného parametra v rozdelenej funkcii
Parameter limit umožňuje vykonať konkrétny počet rozdelení od začiatku reťazca. Bohužiaľ nemôžete poskytnúť počiatočnú pozíciu ani rozsah rozdelení, ktoré je potrebné vykonať, takže je to celkom základné. Môžete si vytvoriť svoj vlastný kód VBA a vytvoriť na to funkciu, ktorá bude popísaná ďalej v tomto článku.
| 123456789101112131415 | Sub SplitWithLimitExample ()'Vytvorte premennéDim MyArray () ako reťazec, MyString ako reťazec, I ako variant, N ako celé číslo„Ukážkový reťazec s oddeľovačmi čiarokMyString = „Jeden, dva, tri, štyri, päť, šesť“'Na rozdelenie jednotlivých častí reťazca použite funkciu RozdeliťMyArray = Split (MyString, ",", 4)„Vymažte pracovný listActiveSheet.UsedRange.Clear„Opakujte polePre N = 0 až UBound (MyArray)'Každé rozdelenie umiestnite do prvého stĺpca pracovného hárkaRozsah ("A" & N + 1). Hodnota = MyArray (N)Ďalej N.Koniec pod |
Po spustení tohto kódu bude váš pracovný hárok vyzerať takto:
Samostatne sú zobrazené iba prvé tri rozdelené hodnoty. Neskoršie tri hodnoty sú zobrazené ako jeden dlhý reťazec a nerozdelia sa.
Ak zvolíte limitnú hodnotu, ktorá je väčšia ako počet oddeľovačov v reťazci, nevyvolá to chybu. Reťazec bude rozdelený na všetky jeho súčasti, ako keby nebola poskytnutá limitná hodnota.
Použitie parametra porovnania vo funkcii rozdelenia
Parameter Porovnať určuje, či oddeľovač rozlišuje veľké a malé písmena alebo nie. To neplatí, ak sú oddeľovačmi čiarky, bodkočiarky alebo dvojbodky.
Poznámka: Namiesto toho môžete vždy umiestniť možnosť Porovnať text <> v hornej časti modulu, aby sa eliminovala citlivosť veľkých a malých písmen na celý modul.
| 123456789101112131415 | Sub SplitByCompareExample ()'Vytvorte premennéDim MyArray () ako reťazec, MyString ako reťazec, I ako variant, N ako celé číslo„Ukážkový reťazec s oddeľovačmi XMyString = "OneXTwoXThreexFourXFivexSix"'Na rozdelenie jednotlivých častí reťazca použite funkciu RozdeliťMyArray = Split (MyString, "X",, vbBinaryCompare)„Vymažte pracovný listActiveSheet.UsedRange.Clear'iterujte cez polePre N = 0 až UBound (MyArray)'Každé rozdelenie umiestnite do prvého stĺpca pracovného hárkaRozsah ("A" & N + 1). Hodnota = MyArray (N)Ďalej N.Koniec pod |
V tomto prípade reťazec, ktorý sa má rozdeliť, používa ako oddeľovač znak „X“. V tomto reťazci je však zmes veľkých a malých písmen „X“. Parameter Porovnať vo funkcii Rozdeliť používa veľké písmeno „X“.
Ak je parameter Porovnať nastavený na vbBinaryCompare, malé písmena „x“ budú ignorované a váš pracovný hárok bude vyzerať takto:
Ak je parameter Porovnať nastavený na vbTextCompare, v rozdelení sa použijú malé písmena „x“ a váš pracovný hárok bude vyzerať takto:
Upozorňujeme, že hodnota v bunke A6 je skrátená, pretože obsahuje malé písmeno „x“. Pretože v rozdelení sa nerozlišujú malé a veľké písmená, akýkoľvek oddeľovač, ktorý je súčasťou podreťazca, spôsobí, že dôjde k rozdeleniu.
Toto je dôležitý bod, ktorý je potrebné mať na pamäti pri použití oddeľovača textu a vbTextCompare. Môžete ľahko skončiť s nesprávnym výsledkom.
Použitie netlačiteľných znakov ako oddeľovača
Ako oddeľovač môžete použiť netlačiteľné znaky, napríklad návrat na koniec riadka (zalomenie riadka).
Tu pomocou vbCr zadáme návrat vozíka <>
| 123456789101112131415 | Sub SplitByNonPrintableExample ()'Vytvorte premennéDim MyArray () ako reťazec, MyString ako reťazec, I ako variant, N ako celé číslo„Ukážkový reťazec s oddeľovačmi návratu vozíkaMyString = "One" & vbCr & "Two" & vbCr & "Three" & vbCr & "Four" & vbCr & "Five" & vbCr & "Six"'Na rozdelenie jednotlivých častí reťazca použite funkciu RozdeliťMyArray = Split (MyString, vbCr,, vbTextCompare)„Vymažte pracovný listActiveSheet.UsedRange.Clear„Opakujte polePre N = 0 až UBound (MyArray)'Každé rozdelenie umiestnite do prvého stĺpca pracovného hárkaRozsah ("A" & N + 1). Hodnota = MyArray (N)Ďalej N.Koniec pod |
V tomto prípade je reťazec vytvorený ako oddeľovač vbCr (návratový znak vozíka).
Po spustení tohto kódu bude váš pracovný hárok vyzerať takto:
Použitie funkcie Pripojiť na obrátenie rozdelenia
Funkcia Pripojiť znova spojí všetky prvky poľa, ale pomocou zadaného oddeľovača. Ak nie je zadaný žiadny oddeľovač, použije sa medzera.
| 123456789101112131415 | Príklad podpojenia ()'Vytvorte premennéDim MyArray () ako reťazec, MyString ako reťazec, I ako variant, N ako celé čísloDim Target as String„Ukážkový reťazec s oddeľovačmi čiarokMyString = „Jeden, dva, tri, štyri, päť, šesť“„Umiestnite MyString do bunky A1Rozsah („A1“). Hodnota = MyString'Na rozdelenie jednotlivých častí reťazca použite funkciu RozdeliťMyArray = Split (MyString, ",")'Pomocou funkcie Pripojenie znova vytvorte pôvodný reťazec pomocou oddeľovača bodkočiarkyCieľ = Pripojiť sa (MyArray, ”;”)'Umiestnite reťazec výsledkov do bunky A2Rozsah („A2“). Hodnota = cieľKoniec pod |
Tento kód rozdelí reťazec s oddeľovačmi čiarok do poľa a spojí ho späť pomocou oddeľovačov bodkočiarky.
Po spustení tohto kódu bude váš pracovný hárok vyzerať takto:
Bunka A1 má pôvodný reťazec s oddeľovačmi čiarok a bunka A2 má nový spojený reťazec s oddeľovačmi bodkočiarkou.
Na výpočet počtu slov použite funkciu rozdelenia
Vzhľadom na to, že reťazcová premenná v programe Excel VBA môže mať dĺžku až 2 Gb, môžete na rozdelenie textu do textu použiť funkciu rozdelenia. Program Microsoft Word to robí automaticky, ale môže to byť užitočné pre jednoduchý textový súbor alebo text skopírovaný z inej aplikácie.
| 1234567891011121314 | PodčísloOslovExample ()'Vytvorte premennéDim MyArray () ako reťazec, MyString ako reťazec„Ukážkový reťazec s oddeľovačmi medzierMyString = "Jeden dva tri štyri päť šesť"„Odstráňte všetky medzeryMyString = Nahradiť (MyString, "", "")„Odstráňte všetky medzery na začiatku alebo na konciMyString = Trim (MyString)'Na rozdelenie jednotlivých častí reťazca použite funkciu RozdeliťMyArray = Split (MyString)'Ukážte počet slov pomocou funkcie UBoundMsgBox „Počet slov“ a UBound (MyArray) + 1Koniec pod |
Jedným z nebezpečenstiev tohto kódu počtu slov je, že ho budú vyvolávať dvojité medzery a úvodné a koncové medzery. Ak sú k dispozícii, budú sa počítať ako ďalšie slová a počet slov skončí ako nepresný.
Kód na odstránenie týchto medzier používa funkcie Nahradiť a Orezať.
Posledný riadok kódu zobrazuje počet slov nájdených pomocou funkcie UBound na získanie maximálneho počtu prvkov v poli a potom ich zvýšenie o 1. Dôvodom je, že prvý prvok poľa začína na nule.
Rozdelenie adresy do buniek pracovného hárka
Poštové adresy sú často dlhé reťazce textu s oddeľovačmi čiarok. Možno budete chcieť rozdeliť každú časť adresy do samostatnej bunky.
| 123456789101112131415 | Podadresa Príklad ()'Vytvorte premennéDim MyArray () ako reťazec, MyString ako reťazec, N ako celé číslo'Nastavte reťazec s adresou spoločnosti Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Na rozdelenie reťazca pomocou oddeľovača čiarok použite funkciu rozdeleniaMyArray = Split (MyString, ",")„Vymažte pracovný listActiveSheet.UsedRange.Clear'iterujte cez polePre N = 0 až UBound (MyArray)'Každé rozdelenie umiestnite do prvého stĺpca pracovného hárkaRozsah ("A" & N + 1). Hodnota = MyArray (N)Ďalej N.Koniec pod |
Spustenie tohto kódu použije oddeľovač čiarok na vloženie každého riadka adresy do samostatnej bunky:
Ak ste chceli vrátiť iba PSČ (posledný prvok poľa), môžete použiť kód:
| 123456789101112 | PodadresaZipCodeExample ()'Vytvorte premennéDim MyArray () ako reťazec, MyString ako reťazec, N ako celé číslo, teplota ako reťazec'Nastavte reťazec s adresou spoločnosti Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Na rozdelenie reťazca pomocou oddeľovača čiarok použite funkciu rozdeleniaMyArray = Split (MyString, ",")„Vymažte pracovný listActiveSheet.UsedRange.Clear„Vložte PSČ do bunky A1Rozsah ("A1"). Hodnota = MyArray (UBound (MyArray))Koniec pod |
Toto použije iba posledný prvok v poli, ktorý sa nájde pomocou funkcie UBound.
Na druhej strane možno budete chcieť vidieť všetky riadky v jednej bunke, aby ich bolo možné vytlačiť na štítok s adresou:
| 1234567891011121314151617 | Podadresa Príklad ()'Vytvorte premennéDim MyArray () ako reťazec, MyString ako reťazec, N ako celé číslo, teplota ako reťazec'Nastavte reťazec s adresou spoločnosti Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Na rozdelenie reťazca pomocou oddeľovača čiarok použite funkciu rozdeleniaMyArray = Split (MyString, ",")„Vymažte pracovný listActiveSheet.UsedRange.Clear'iterujte cez polePre N = 0 až UBound (MyArray)'umiestnite každý prvok poľa plus znak posuvu riadka do reťazcaTemp = Temp & MyArray (N) & vbLfĎalej N.„Vložte reťazec do pracovného hárkaRozsah ("A1") = teplotaKoniec pod |
Tento príklad funguje rovnako ako predchádzajúci, ibaže vytvára dočasný reťazec všetkých prvkov poľa, ale za každý prvok vloží znak posuvu riadka.
Po spustení kódu bude pracovný hárok vyzerať takto:
Rozdeľte reťazec do buniek pracovného hárka
Pole Split môžete skopírovať do buniek pracovného hárka <> jediným príkazom:
| 12345678910 | Sub CopyToRange ()'Vytvorte premennéDim MyArray () ako reťazec, MyString ako reťazec„Ukážkový reťazec s oddeľovačmi medzierMyString = „Jeden, dva, tri, štyri, päť, šesť“'Na rozdelenie jednotlivých častí reťazca použite funkciu RozdeliťMyArray = Split (MyString, ",")„Skopírujte pole do pracovného hárkaRozsah ("A1: A" & UBound (MyArray) + 1). Hodnota = WorksheetFunction.Transpose (MyArray)Koniec pod |
Po spustení tohto kódu bude váš pracovný hárok vyzerať takto:
Vytvorenie novej funkcie, ktorá umožní rozdelenie z daného bodu
Parameter Limit vo funkcii Split vám umožňuje určiť iba hornú hranicu, na ktorej sa má rozdelenie zastaviť. Začína sa vždy od začiatku reťazca.
Bolo by veľmi užitočné mať podobnú funkciu, kde môžete zadať počiatočný bod rozdelenia v reťazci a počet rozdelení, ktoré chcete od tohto bodu ďalej vidieť. Bude tiež extrahovať iba rozdelenia, ktoré ste zadali do poľa, a nebude mať ako posledný prvok v poli obrovskú hodnotu reťazca.
Vo VBA si môžete jednoducho vytvoriť funkciu (nazývanú SplitSlicer) sami, aby ste to urobili:
| 123456789101112131415161718192021222324 | Funkcia SplitSlicer (cieľ ako reťazec, del ako reťazec, začiatok ako celé číslo, N ako celé číslo)'Vytvorte premennú poľaDim MyArray () ako reťazec'Zachyťte rozdelenie pomocou počiatočnej premennej pomocou oddeľovačaMyArray = Split (Target, Del, Start)„Skontrolujte, či je počiatočný parameter vyšší ako počet rozdelení - môže to spôsobiť problémyAk Štart> UBound (MyArray) + 1 Potom„Chyba zobrazenia a ukončenie funkcieMsgBox „Počiatočný parameter je väčší ako počet dostupných rozdelení“SplitSlicer = MyArrayUkončiť funkciuKoniec Ak'Vložte posledný prvok poľa do reťazcaTarget = MyArray (UBound (MyArray))'Rozdeľte reťazec pomocou N ako limituMyArray = Split (Target, Del, N)„Keď kód odstráni posledný prvok, skontrolujte, či je horný limit väčší ako nulaAk je UBound (MyArray)> 0, potom'Na odstránenie konečného prvku poľa použite ReDimReDim Preserve MyArray (UBound (MyArray) - 1)Koniec Ak„Vráťte nové poleSplitSlicer = MyArrayKoncová funkcia |
Táto funkcia je postavená na štyroch parametroch:
- Cieľ - reťazec - toto je vstupný reťazec, ktorý chcete rozdeliť
- Del - reťazec alebo netlačiteľný znak - toto je oddeľovač, ktorý používate napr. čiarka, dvojbodka
- Začnite - číslo - toto je počiatočné rozdelenie pre váš plátok
- N. - číslo - toto je počet rozdelení, ktoré chcete urobiť vo svojom reze
Žiadny z týchto parametrov nie je voliteľný alebo nemá predvolené hodnoty, ale môžete ho zapracovať do kódu funkcie, ak ho chcete ďalej rozšíriť.
Funkcia používa funkciu Split na vytvorenie poľa pomocou parametra Start ako limitu. To znamená, že prvky poľa budú držať rozdelenia až po štartovací parameter, ale zvyšok reťazca bude posledným prvkom a nebude rozdelený.
Posledný prvok v poli sa prenesie späť do reťazca pomocou funkcie UBound, aby sa určilo, o ktorý prvok ide.
Reťazec sa potom znova rozdelí do poľa s použitím N ako limitnej premennej. To znamená, že pre reťazec sa urobí rozdelenie do polohy N, potom zvyšok reťazca vytvorí posledný prvok v poli.
Príkaz ReDim sa používa na odstránenie posledného prvku, pretože chceme, aby v poli zostali iba konkrétne prvky. Upozorňujeme, že sa použije parameter Preserve, inak sa stratia všetky údaje v poli.
Nové pole sa potom vráti do kódu, z ktorého bolo volané.
Upozorňujeme, že kód je „odolný voči chybám“. Používatelia budú často robiť podivné veci, o ktorých ste neuvažovali. Ak sa napríklad pokúsia použiť funkciu s parametrom Start alebo N vyšším, ako je dostupný počet rozdelení v reťazci, pravdepodobne to spôsobí zlyhanie funkcie.
Kód je zahrnutý na kontrolu počiatočnej hodnoty a tiež na zaistenie toho, že existuje prvok, ktorý je možné odstrániť, keď sa v poli použije príkaz ReDim.
Tu je kód na testovanie funkcie:
| 123456789101112 | Sub TestSplitSlicer ()'Vytvorte premennéDim MyArray () ako reťazec, MyString ako reťazec'Definujte vzorový reťazec oddeľovačmi čiarokMyString = "Jeden, dva, tri, štyri, päť, šesť, sedem, osem, deväť, deväť, desať"'Na definovanie nového poľa použite funkciu SplitslicerMyArray = SplitSlicer (MyString, ",", 4, 3)„Vymažte aktívny hárokActiveSheet.UsedRange.Clear„Skopírujte pole do pracovného hárkaRozsah ("A1: A" & UBound (MyArray) + 1). Hodnota = WorksheetFunction.Transpose (MyArray)Koniec pod |
Spustite tento kód a váš pracovný hárok bude vyzerať takto:
