wanneer u met VBA in Excel werkt, moet u mogelijk een tekenreeks in verschillende delen splitsen op basis van een scheidingsteken.

bijvoorbeeld, als u een adres hebt, kunt u de VBA Split-functie gebruiken om verschillende delen van het adres te krijgen die gescheiden zijn door een komma (wat in dit geval het scheidingsteken zou zijn).

SPLIT is een ingebouwde tekenreeksfunctie in Excel VBA die u kunt gebruiken om een tekenreeks te splitsen op basis van het scheidingsteken.,

Deze Tutorial omvat:

Excel VBA SPLIT Function – syntaxis 

Split ( Expression, , , )
  • expressie: dit is de tekenreeks die u wilt splitsen op basis van het scheidingsteken. Bijvoorbeeld, in het geval van het adres voorbeeld, het volledige adres zou de ‘uitdrukking’. In het geval dat dit een nul-lengte string (“”) SPLIT functie zou een lege array retourneren.
  • scheidingsteken: dit is een optioneel argument. Dit is het scheidingsteken dat gebruikt wordt om het ‘Expression’ argument te splitsen., In het geval van ons adres voorbeeld, een komma is een scheidingsteken dat wordt gebruikt om het adres op te splitsen in verschillende delen. Als u dit argument niet opgeeft, wordt een spatieteken beschouwd als het standaard scheidingsteken. In het geval u een nul-lengte string ( “” ) geeft, wordt de hele ‘expressie’ string geretourneerd door de functie.
  • limiet: dit is een optioneel argument. Hier geeft u het totale aantal substrings op dat u wilt retourneren. Als je bijvoorbeeld alleen de eerste drie substrings van het ‘Expression’ – argument wilt retourneren, dan is dit 3., Als je dit argument niet opgeeft, is de standaard -1, die alle substrings retourneert.
  • vergelijk: dit is een optioneel argument. Hier specificeert u het type vergelijking dat u wilt dat de SPLIT-functie uitvoert bij het evalueren van de substrings. De volgende opties zijn beschikbaar:
    • wanneer vergelijken 0 is: dit is een binaire vergelijking. Dit betekent dat als uw scheidingsteken een tekstreeks is (laten we zeggen ABC), dan is dit hoofdlettergevoelig. ‘ABC ‘zou niet gelijk zijn aan’abc’.
    • wanneer vergelijken is 1: Dit is een Tekstvergelijking., Dit betekent dat als je scheidingsteken een tekstreeks is (laten we zeggen ABC), dan zelfs als je ‘abc’ in de ‘Expression’ string hebt, het als een scheidingsteken zou worden beschouwd.

nu we de basis van de SPLIT-functie hebben behandeld, laten we een paar praktische voorbeelden bekijken.

Voorbeeld 1-splits de woorden in een zin

stel dat ik de tekst heb – “de snelle bruine vos springt over de luie hond”.

Ik kan de functie splitsen gebruiken om elk woord van deze zin als een apart item in een array te krijgen.,

de onderstaande code zou dit doen:

Sub SplitWords()Dim TextStrng As StringDim Result() As StringTextStrng = "The Quick Brown Fox Jumps Over The Lazy Dog"Result() = Split(TextStrng)End Sub

hoewel de code niets nuttigs doet, zal het u helpen te begrijpen wat de Split-functie in VBA doet.

Split functie splitst de tekstreeks en wijst Elk woord toe aan de Result array.

dus in dit geval:

  • resultaat(0) slaat de waarde “The”
  • resultaat(1) slaat de waarde “Quick”
  • resultaat (2) slaat de waarde “Brown” enzovoort op.

in dit voorbeeld hebben we alleen het eerste argument gespecificeerd – dat is de te splitsen tekst., Omdat er geen scheidingsteken is opgegeven, neemt het spatieteken als standaard scheidingsteken.

belangrijke opmerking:

  1. VBA SPLIT functie geeft een array terug die begint vanaf basis 0.
  2. wanneer het resultaat van de SPLIT-functie is toegewezen aan een array, moet die array worden gedeclareerd als een Stringgegevenstype. Als u het als een Variant data type declareert, zal het een type mismatch fout tonen). Merk in het bovenstaande voorbeeld op dat Ik resultaat() als een Stringgegevenstype heb gedeclareerd.,

Voorbeeld 2 – Tel het aantal woorden in een zin

u kunt de functie splitsen gebruiken om het totale aantal woorden in een zin te krijgen. De truc hier is om het aantal elementen in de array te tellen dat je krijgt wanneer je de tekst splitst.

de onderstaande code toont een berichtenvenster met het aantal woorden:

Sub WordCount()Dim TextStrng As StringDim WordCount As IntegerDim Result() As StringTextStrng = "The Quick Brown Fox Jumps Over The Lazy Dog"Result = Split(TextStrng)WordCount = UBound(Result()) + 1MsgBox "The Word Count is " & WordCountEnd Sub

In dit geval vertelt de functie Ubund ons de bovengrens van de array (d.w.z. het maximum aantal elementen dat de array heeft)., Aangezien de basis van de array 0 is, wordt 1 toegevoegd om het totale aantal woorden te krijgen.

u kunt een vergelijkbare code gebruiken om een aangepaste functie in VBA te maken die de tekst als invoer neemt en het aantal woorden retourneert.

onderstaande code maakt deze functie aan:

eenmaal aangemaakt, kunt u de functie WordCount gebruiken, net als elke andere reguliere functie.

Deze functie behandelt ook voorloop -, achterloop-en dubbele spaties tussen woorden. Dit is mogelijk gemaakt door de TRIM-functie in de VBA-code te gebruiken.,

Als u meer wilt weten over hoe deze formule werkt om het aantal woorden in een zin te tellen of als u meer wilt weten over een niet-VBA-formule om het aantal woorden te krijgen, bekijk dan deze tutorial.

Voorbeeld 3-Gebruik een ander scheidingsteken dan spatie teken

In de vorige twee voorbeelden hebben we slechts één argument gebruikt in de functie splitsen, en de rest waren de standaardargumenten.

wanneer u een ander scheidingsteken gebruikt, moet u dat opgeven in de gesplitste formule.,

in de onderstaande code retourneert de functie splitsen een array gebaseerd op een komma als het scheidingsteken, en toont vervolgens een bericht met elk woord in een aparte regel.

in de bovenstaande code heb ik de For Next lus gebruikt om door elk element van de array ‘Result’ te gaan.

Voorbeeld 4-Verdeel een adres in drie delen

met de functie splitsen kunt u opgeven hoeveel aantallen splitsingen u wilt krijgen. Als ik bijvoorbeeld niets opgeef, wordt elke instantie van het scheidingsteken gebruikt om de tekenreeks te splitsen.,

maar als ik 3 als de limiet specificeer, dan wordt de string slechts in drie delen gesplitst.

als ik bijvoorbeeld het volgende adres heb:

2703 Winifred Way, Indianapolis, Indiana, 46204

kan ik de Split-functie in VBA gebruiken om dit adres in drie delen te verdelen.

het splitst de eerste twee op basis van het komma-scheidingsteken en het resterende deel wordt het derde element van de array.,

onderstaande code toont het adres in drie verschillende regels in een berichtenvenster:

een van de praktische toepassingen hiervan kan zijn wanneer u een enkel regeladres wilt verdelen in het formaat dat in het berichtenvenster wordt getoond. Vervolgens kunt u een aangepaste functie maken die het adres in drie delen retourneert (met elk deel in een nieuwe regel).

de volgende code zou dit doen:

zodra u deze code in de module hebt, kunt u de functie (ThreePartAddress) in de werkmap gebruiken, net als elke andere Excel-functie.,

Deze functie heeft één argument – de celverwijzing die het adres heeft.

merk op dat om het resulterende adres in drie verschillende regels te laten verschijnen, u het wrap-tekstformaat op de cellen moet toepassen (het is op het tabblad Home in de Uitlijningsgroep). Als de ‘Wrap Text’ – indeling niet is ingeschakeld, ziet u het volledige adres als één enkele regel.

Voorbeeld 5-haal de naam van de stad van het adres

met de Split-functie in VBA kunt u opgeven welk deel van de resulterende array u wilt gebruiken.,

bijvoorbeeld, stel dat ik het volgende adres opsplits op basis van de komma als scheidingsteken:

2703 Winifred Way, Indianapolis, Indiana, 46204

De resulterende array zou er zo uitzien als hieronder:

{"2703 Winifred Way", "Indianapolis", "Indiana", "46204"}

aangezien dit een array is, kan ik ervoor kiezen om een specifiek deel van deze array weer te geven of terug te geven.

Hieronder is een code voor een aangepaste functie, waar u een getal kunt opgeven en het zal dat element uit de array retourneren. Bijvoorbeeld, als ik de status naam wil, kan ik 3 specificeren (omdat het het derde element in de array is).,

de bovenstaande functie heeft twee argumenten, de celverwijzing met het adres en het elementnummer dat u wilt retourneren. De Split-functie splitst de adreselementen en wijst deze toe aan de Resultaatvariabele.

dan geeft het het elementnummer terug dat u hebt opgegeven als het tweede argument. Merk op dat, aangezien de basis 0 is, elementnummer-1 wordt gebruikt om het juiste deel van het adres te retourneren.

deze aangepaste formule is het meest geschikt als u een consistent formaat in al het adres – dat wil zeggen, de stad wordt altijd genoemd na de twee komma ‘ s., Als de gegevens niet consistent zijn, krijgt u niet het gewenste resultaat.

in het geval u de naam van de stad wilt, kunt u 2 gebruiken als het tweede argument. Als u een getal gebruikt dat hoger is dan het totale aantal elementen, geeft dit de waarde #terug! fout.

u kunt de code verder vereenvoudigen zoals hieronder getoond:

in de bovenstaande code, in plaats van de Resultaatvariabele te gebruiken, geeft het alleen het opgegeven elementnummer terug.

dus als je hebt gesplitst (“Good Morning”) (0), zal het alleen het eerste element teruggeven, dat”Good” is.,

Op dezelfde manier geeft het in de bovenstaande code alleen het opgegeven elementnummer terug.

u kunt ook de volgende Excel-Tutorials leuk vinden:

  • Excel VBA InStr – functie-uitgelegd met voorbeelden.
  • Hoe gegevens in Excel te sorteren met behulp van VBA (een stap-voor-stap handleiding).
  • 7 verbazingwekkende dingen die Excel Tekst naar kolommen voor u kunnen doen.
  • Hoe het aantal woorden in Excel te krijgen.
  • VBA TRIM functie.