Hoe vindt u het eerste, laatste of een specifiek voorkomen van een teken in Excel?
Bij veel gegevensverwerkingsactiviteiten komt u lijsten met tekenreeksen tegen die speciale tekens bevatten—zoals het koppelteken „-”—en moet u de positie bepalen van het eerste, laatste of een specifiek (n-de) voorkomen ervan. Denk bijvoorbeeld aan het extraheren van informatie op basis van scheidingstekens, het analyseren van onderdeelcodes of het splitsen van gegevenscomponenten; daarbij moet u vaak precies weten waar een bepaald teken in elke reeks voorkomt. Excel biedt echter geen ingebouwde functie die direct de positie van het eerste, laatste of n-de voorkomen van een teken in een cel retourneert. In dit artikel worden diverse praktische technieken en stapsgewijze oplossingen beschreven om deze voorkomens nauwkeurig te vinden, zodat u flexibel kunt inspelen op verschillende behoeften bij het parseren en voorbereiden van gegevens.
Zoek het laatste voorkomen van een teken met formules
Zoek het laatste voorkomen van een teken met een gebruikersgedefinieerde functie
Zoek het eerste of Voorkomen voorkomen van een teken met een formule
Zoek het eerste of Voorkomen voorkomen van een specifiek teken met een eenvoudige functie
Zoek het laatste voorkomen van een teken met formules
Als u de positie van het laatste voorkomen van een specifiek teken (zoals „-”) in een tekenreeks wilt retourneren, bieden de standaardfuncties van Excel dit niet rechtstreeks. Door echter een paar formules te combineren, kunt u dit probleem efficiënt oplossen. Hieronder staan twee formule-aanpakken die handig zijn bij productcodes, Bestandspad of andere gegevens met een consistente scheidingstekenstructuur. Houd er rekening mee dat complexe formules de berekeningsbelasting kunnen verhogen bij grote datasets.
1. Voer in een lege cel (bijvoorbeeld C2) een van de volgende formules in of kopieer deze:
=SEARCH("^^",SUBSTITUTE(A2,"-","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))=LOOKUP(2,1/(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)="-"),ROW(INDIRECT("1:"&LEN(A2))))Beide formules zijn bedoeld om de positie (als getal) van het laatste voorkomen van het teken „-” in de gerefereerde cel (A2) te retourneren. De eerste formule gebruikt ZOEKEN en VERVANGEN om de positie te bepalen: het laatste „-” wordt vervangen door een uniek symbool, waarna daar naar gezocht wordt. De tweede methode maakt gebruik van VERT.ZOEKEN in combinatie met DEEL en RIJ om het laatste „-” te vinden. U kunt kiezen welke methode u het beste uitkomt, afhankelijk van uw vertrouwdheid met de functies of de omvang van uw gegevens. De VERT.ZOEKEN-aanpak is bovendien gemakkelijker aan te passen voor moderne Excel-versies met matrixondersteuning.
2. Nadat u de formule hebt ingevoerd, drukt u op Enter. Om deze formule toe te passen op extra rijen, sleept u de vulgreep omlaag tot het gewenste bereik is bereikt. Elke cel wordt dan direct gevuld met de positie van het laatste voorkomen van de overeenkomstige tekenreeks, zoals in de volgende afbeelding wordt weergegeven. Controleer altijd of de gerefereerde cellen correct zijn wanneer u de formule naar een ander gebied kopieert.
Opmerking: In deze voorbeelden verwijst A2 naar de cel met uw gegevens en is „-” het teken dat u zoekt. U kunt „-” vervangen door elk ander teken dat u wilt zoeken. Als uw cel leeg is of het opgegeven teken niet in de tekst voorkomt, kan de formule een foutmelding geven. Bij tekst met herhalende scheidingstekens of speciale symbolen raden we u aan om zorgvuldig te controleren op verborgen of niet-afdrukbare tekens die uw resultaten kunnen beïnvloeden.
Zoek het laatste voorkomen van een teken met een gebruikersgedefinieerde functie
Een andere flexibele methode is het gebruik van een gebruikersgedefinieerde functie (UDF) in een VBA-module, wat u meer controle en eenvoudiger hergebruik biedt—vooral wanneer u regelmatig het laatste voorkomen van een willekeurig teken moet vinden voor diverse analyses. Deze aanpak is ideaal als u een beknopte formule-interface wilt ()=lastpositionofchar(cel, teken)) die net als ingebouwde Excel-functies werkt. Houd er echter rekening mee dat macro’s ingeschakeld moeten zijn om VBA-oplossingen te laten werken, en sla uw werkmap altijd op als een macro-inschakelbaar bestand (*.xlsm) om de UDF te behouden.
1. Open het werkblad waarin u deze functie wilt toevoegen.
2. Druk tegelijkertijd op de toetsen ALT + F11 om de editor Microsoft Visual Basic for Applications te openen.
3. Klik in de VBA-editor op Invoegen > Module om een nieuwe module te maken. Kopieer en plak vervolgens de volgende code in het modulevenster.
VBA-code: zoek het laatste voorkomen van een teken
Function LastpositionOfChar(strVal As String, strChar As String) As Long
LastpositionOfChar = InStrRev(strVal, strChar)
End Function
Probleemoplossing: Als de functie geen resultaat retourneert, controleer dan of macro’s zijn ingeschakeld en of u de juiste celreferentie en het juiste teken in de formule hebt ingevoerd. Wilt u het laatste voorkomen van een ander teken vinden? Pas dan het argument dienovereenkomstig aan.
4. Sla het codevenster op en sluit het. Ga terug naar uw werkblad en voer de volgende formule in een lege cel in (bijvoorbeeld cel B2):=lastpositionofchar(A2,"-"). Vervang A2 door de juiste cel en „-” door uw doelteken indien nodig.
5. Sleep de vulgreep omlaag om de formule, indien nodig, op extra cellen toe te passen. Zo wordt de laatste positie van het opgegeven teken in elke tekenreeks weergegeven, zoals hieronder te zien is.
Opmerking: Het eerste argument ()A2) is de brondcel met uw tekenreeks en het tweede („-”) is het doelteken. U kunt deze parameters naar wens aanpassen. Als er geen overeenkomst wordt gevonden, retourneert de functie mogelijk 0. Sla altijd eerst uw werk op voordat u VBA-code uitvoert of bewerkt. Mocht u een foutmelding krijgen, controleer dan of er aanhalingstekens verkeerd zijn geplaatst of cellen onjuist zijn gerefereerd.
Zoek het eerste of Voorkomen voorkomen van een teken met een formule
Als uw taak niet alleen het laatste, maar ook het eerste, tweede of een ander specifiek voorkomen van een teken in een tekenreeks betreft, kunt u een andere formulestructuur gebruiken. Dit is vaak nodig bij het parseren van hiërarchische codes, het extraheren van delen uit gestructureerde ID’s of het identificeren van herhaalde symbolen in gegevensitems.
1. Voer de volgende formule in een lege cel in (bijvoorbeeld C2) of plak deze om de n-de positie van een opgegeven teken te bepalen:
=FIND(CHAR(160),SUBSTITUTE(A2,"-",CHAR(160),2))In deze formule is A2 de cel die wordt doorzocht, „-” het teken dat u zoekt, en 2 verwijst naar het voorkomen van het teken dat u wilt (bijvoorbeeld 2 voor het tweede voorkomen). U kunt het voorkomende nummer aanpassen aan uw gegevensbehoeften.
2. Druk op Enter. Om de formule op meer rijen toe te passen, sleept u de vulgreep naar beneden tot het gewenste bereik. Zo krijgt u bijvoorbeeld de positie van de tweede keer dat „-” voorkomt in elke tekstreeks. Als het opgegeven volgnummer hoger is dan het totale aantal keren dat het teken in de reeks voorkomt, geeft de formule een foutmelding. Controleer in dat geval uw tekstgegevens of de voorkomstparameter.
Opmerking: Het getal 2 in de formule geeft aan welke voorkomst gezocht moet worden. Pas dit getal aan om de positie van de eerste, derde, vierde of een andere voorkomst van het opgegeven teken te vinden. Als het teken niet zo vaak voorkomt als opgegeven, verschijnt er een #WAARDE!-fout; u kunt deze opvangen met ALS.FOUT() voor een nettere uitvoer.
Vind de eerste of Voorkomen voorkomst van een specifiek teken met een eenvoudige functie
Voor gebruikers die liever een directe en interactieve oplossing kiezen of zich ongemakkelijk voelen bij het bouwen van complexe formules of VBA-code, biedt Kutools voor Excel een handige tool met de naam Zoek de positie van de N-de voorkomst van een teken in een tekenreeks. Met dit hulpprogramma bepaalt u in slechts een paar klikken eenvoudig de positie van de eerste, tweede, derde of een willekeurige voorkomst van een specifiek teken in een cel. Ideaal om tijd te besparen bij herhaalde gegevensschoonmaakacties of bij het werken met grote datasets!
Na installatie van Kutools voor Excel, volgt u deze stappen voor snelle resultaten:
Stel dat u de positie wilt bepalen van de tweede keer dat het koppelteken „-” voorkomt in een reeks tekstreeksen:
1. Klik op de cel waarin u het resultaat wilt weergeven.
2. Klik op Kutools > Formulehulp > Formulehulp in het lint, zoals hieronder wordt getoond:
3. In het dialoogvenster Formulehulp:
- Selecteer Opzoeken in de Formuletype-keuzelijst.
- Kies Zoek de positie van de N-de voorkomst van een teken in een tekenreeks in de Selecteer een formule-lijst.
- Geef in het Argumentinvoer-gedeelte de cel met uw tekst op, voer het te zoeken teken in en geef het gewenste voorkomende nummer aan.
4. Klik op OK. Nadat de formule het resultaat heeft geretourneerd, sleept u de vulgreep naar beneden om deze toe te passen op andere rijen. Zo voorkomt u formulefouten door handmatige invoer.
Download nu de gratis proefversie van Kutools voor Excel!
Deze optie is met name geschikt voor niet-technische gebruikers of voor wie het proces regelmatig op verschillende werkbladen moet herhalen, waardoor handmatige fouten worden geminimaliseerd en tijd wordt bespaard bij het samenstellen van complexe formules.
Alternatieve formule voor recente Excel-versies (met dynamische matrices)
In nieuwere versies van Excel (Excel 365, Excel 2021 en nieuwer) die dynamische matrices ondersteunen, kunt u functies zoals REEKS en FILTER gebruiken om alle posities van een teken tegelijkertijd te retourneren. Zo identificeert u voorkomens eenvoudiger of toont u zelfs alle voorkomens in een apart bereik voor verdere verwerking.
1. Voer de volgende formule in een lege cel (bijvoorbeeld B2) in om alle posities van het teken „-” in cel A2 op te halen:
=FILTER(SEQUENCE(LEN(A2)), MID(A2,SEQUENCE(LEN(A2)),1)="-")Na het indrukken van Enter geeft de formule automatisch de positie weer van elk voorkomen van „-” in aangrenzende cellen. Als u alleen de voorkomens wilt retourneren, kunt u het volgende gebruiken:
=INDEX(FILTER(SEQUENCE(LEN(A2)), MID(A2,SEQUENCE(LEN(A2)),1)="-"),2)Vervang 2 door het gewenste volgnummer. Deze methode is bijzonder krachtig voor matrices of lijsten met variabele lengte, maar vereist een Excel-versie die deze functies ondersteunt.
Tip: Als u een #BEREKEN!-fout of een vergelijkbare foutmelding ziet, controleer dan of uw Excel-versie dynamische matrices ondersteunt en of de gerefereerde cellen tekstgegevens bevatten.
VBA-code om de Voorkomen positie van een teken te vinden
Als u een oplossing zoekt om niet alleen de laatste, maar ook een willekeurige voorkomstpositie van een teken te vinden, kan VBA u daarbij ondersteunen. De volgende macro is eenvoudig aan te passen aan diverse analysebehoeften en bespaart tijd wanneer u deze herhaaldelijk in meerdere werkmappen gebruikt.
1. Ga naar het tabblad Ontwikkelaar > Visual Basic, selecteer vervolgens Invoegen > Module. Plak de onderstaande code in het modulevenster:
Function NthPositionOfChar(cell As Range, ch As String, nth As Integer) As Long
Dim s As String
Dim i As Long, count As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
s = cell.Value
count = 0
For i = 1 To Len(s)
If Mid(s, i, 1) = ch Then
count = count + 1
If count = nth Then
NthPositionOfChar = i
Exit Function
End If
End If
Next i
NthPositionOfChar = 0
End Function2. Sluit de editor. Gebruik de formule =NthPositionOfChar(A2,"-",2) (vervang indien nodig de argumenten) in uw werkblad om de positie te bepalen van het tweede voorkomen van „-” in cel A2. Houd er rekening mee dat de functie 0 retourneert als dit voorkomen niet bestaat.
Deze code is ideaal om flexibele scheidingstekeninformatie uit gegevens te extraheren of om herhaalde analyseactiviteiten te vereenvoudigen. VBA-aangepaste functies kunnen bovendien worden afgestemd op specifiekere vereisten, zoals het zoeken naar hoofdlettergevoelige tekens of het verwerken van grotere datasets.
Controleer bij het werken met tekstreeksen en posities van tekenvoorkomens altijd op verborgen of niet-afdrukbare tekens — zoals spaties, carriage returns of speciale Unicode-symbolen — want deze kunnen de uitkomsten van uw formules beïnvloeden. Zorg ook voor consistente gegevensindeling als u verschillende scheidingstekens gebruikt, voordat u formules of macro’s toepast. Mocht u fouten tegenkomen, controleer dan nogmaals uw invoerparameters, formuleverwijzingen en of het datatype (tekst of getal) compatibel is met de gebruikte functie.
Het combineren van formules, VBA en Kutools-functies biedt uitgebreide flexibiliteit voor zowel routinematige als geavanceerde Excel-tekstverwerkingsopdrachten. Kies de methode die het beste aansluit bij uw technische ervaring, Excel-versie en de complexiteit van uw gegevens voor optimale nauwkeurigheid en efficiëntie.
Meer gerelateerde artikelen:
- Haal alle woorden behalve het eerste/laatste woord uit in Excel
- Het extraheren van alle woorden uit een cel, behalve het eerste of laatste woord, kan u helpen het ongewenste woord te verwijderen. In dat geval kunt u natuurlijk de gewenste woorden één voor één kopiëren en in een andere cel plakken. Dit wordt echter vervelend als er meerdere celwaarden zijn waaruit alle woorden behalve het eerste of laatste moeten worden gehaald. Hoe kunt u snel en eenvoudig alle woorden behalve het eerste of laatste in Excel extraheren?
- Tekens van rechts naar links uit een cel extraheren
- In dit artikel leest u hoe u tekens vanaf rechts uit een cel haalt of extraheert tot de eerste spatie, zodat u direct het gewenste resultaat krijgt in uw Excel-werkblad. Een handige formule in dit artikel lost deze taak snel en eenvoudig op.
- Verwijder de eerste, laatste X tekens of tekens op een bepaalde positie
- In dit artikel wordt uitgelegd hoe u tekens vanaf rechts uit een cel kunt extraheren tot het eerste spatieteken, zodat u direct het gewenste resultaat krijgt in uw Excel-werkblad. De handige formule die hier wordt gepresenteerd, lost deze taak snel en eenvoudig op.
- Vind de positie van de eerste kleine letter
- Als u een lijst met tekenreeksen hebt die zowel hoofdletters als kleine letters bevatten, hoe bepaalt u dan snel de positie van de eerste kleine letter in elke reeks in een Excel-werkblad—zonder ze handmatig één voor één te tellen?
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins