Generieke formule
=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))
Samenvatting
Om tekst op een willekeurig scheidingsteken (komma, spatie, pijp, enz.) Te splitsen, kunt u een formule gebruiken die is gebaseerd op de functies TRIM, MIDDEN, SUBSTITUTE, REPT en LEN. In het getoonde voorbeeld is de formule in C5:
=TRIM(MID(SUBSTITUTE($B5,"|",REPT(" ",LEN($B5))),(C$4-1)*LEN($B5)+1,LEN($B5)))
Opmerking: verwijzingen naar B5 en C4 zijn gemengde verwijzingen om de formule heen en weer te kopiëren.
Uitleg
De essentie van deze formule is om een bepaald scheidingsteken te vervangen door een groot aantal spaties met SUBSTITUTE en REPT, en vervolgens de MID-functie te gebruiken om tekst te extraheren die gerelateerd is aan het "n-de voorkomen" en de TRIM-functie om de extra spatie te verwijderen.
In dit fragment wordt het scheidingsteken (scheidingsteken) vervangen door een aantal spaties dat gelijk is aan de totale lengte van de string:
SUBSTITUTE(A1,delim,REPT(" ",LEN(A1)))
Vervolgens gebruikt de formule de MID-functie om de ne substring te extraheren. Het startpunt wordt berekend met de onderstaande code, waarbij N staat voor "n-de":
(N-1)*LEN(A1)+1
Het totale aantal geëxtraheerde tekens is gelijk aan de lengte van de volledige tekenreeks. De TRIM-functie verwijdert vervolgens alle extra spaties en retourneert alleen de n-de string.
Pak slechts één exemplaar uit
Hoewel het voorbeeld is ingesteld om 5 subtekenreeksen uit de tekst in kolom B te extraheren, kunt u gemakkelijk slechts 1 exemplaar extraheren. Als u bijvoorbeeld alleen het 4e item (stad) wilt extraheren, kunt u het volgende gebruiken:
=TRIM(MID(SUBSTITUTE(B5,"|",REPT(" ",LEN(B5))),(4-1)*LEN(B5)+1,LEN(B5)))
Tekst naar kolommen functie
Voor handmatige, eenmalige conversies heeft Excel een ingebouwde functie genaamd "Tekst naar kolommen" waarmee tekst in cellen kan worden gesplitst met een scheidingsteken naar keuze. U vindt deze functie op het tabblad Gegevens van het lint in het gedeelte Gegevenshulpmiddelen.
