Hoe zoekt u getallen op die in Excel als tekst zijn opgeslagen?
Bij het gebruik van VLOOKUP in Excel kunnen opmaakverschillen — bijvoorbeeld wanneer een opzoekwaarde als tekst is opgeslagen terwijl de opzoekkolom getallen bevat, of vice versa — leiden tot fouten of mislukte zoekopdrachten. Deze opmaakmismatch komt vaak voor, vooral bij gegevens uit externe bronnen, geïmporteerde datasets of bij het werken met grote, gedeelde bestanden. Het oplossen van deze inconsistenties is essentieel om te garanderen dat VLOOKUP naar verwachting werkt en u de juiste informatie ophaalt. Deze stapsgewijze handleiding biedt praktische oplossingen voor dergelijke opmaakproblemen, zodat u altijd betrouwbare en nauwkeurige zoekresultaten krijgt, ongeacht hoe getallen in uw werkmap zijn opgeslagen.
In dit artikel worden effectieve methoden getoond om dergelijke fouten aan te pakken, waaronder formule-aanpassingen, ingebouwde Excel-hulpmiddelen en VBA-automatisering voor bulkverwerking of volledig geautomatiseerde workflows. Daarnaast bespreken we de voordelen en belangrijke overwegingen van elke methode, zodat u de beste aanpak voor uw specifieke situatie kunt kiezen.
- Zoek getallen op die als tekst zijn opgeslagen met formules
- Los opmaakverschillen snel op met Kutools voor Excel
- VBA-macro: standaardiseer opmaak vóór VLOOKUP
- Andere ingebouwde Excel-methoden: gebruik ‘Tekst naar kolommen’ om gegevensopmaak te herstellen
Zoek getallen op die als tekst zijn opgeslagen met formules
Als uw opzoekgegevens getallen bevatten die op één plaats als tekst zijn opgeslagen en elders als echte getallen, vindt VLOOKUP geen overeenkomsten vanwege deze opmaakinconsistentie. Een van de meest directe oplossingen is het gebruik van Excel-formules om de opzoekwaarde of de opzoekkolom on-the-fly naar een consistente opmaak te converteren. Deze aanpak werkt uitstekend bij de meeste werkbladbewerkingen, is eenvoudig toe te passen en laat uw oorspronkelijke gegevens volledig intact.
Als uw opzoekwaarde bijvoorbeeld als tekst is opgeslagen, terwijl het overeenkomstige veld in de tabel als getal is opgemaakt, kunt u de functie WAARDE gebruiken om de tekst binnen de VLOOKUP-formule naar een getal te converteren.
Voer de volgende formule in een lege cel in waar u het resultaat wilt weergeven:
=VLOOKUP(VALUE(G1),A2:D15,2,FALSE)Nadat u de formule hebt ingevoerd, drukt u op Enterom de waarde op te halen die overeenkomt met uw criteria, zoals hieronder in de schermafbeelding wordt weergegeven:
Uitleg van parameters en tips:
- G1: De cel met de waarde die u wilt opzoeken (zowel tekst als getallen zijn toegestaan).
- A2:D15: Het bereik van uw gegevenstabel met de opzoekkolom en de kolommen die de informatie bevatten die u wilt ophalen.
- 2: Het kolomnummer (vanaf de meest linkse kolom in het tabelbereik) van het resultaat dat u wilt ophalen.
Let op eventuele voorloopspaties of volgende spaties in het zoekwaardebereik, aangezien deze ook tot zoekfouten kunnen leiden. Overweeg de functie TRIM te combineren als uw gegevens extra spaties kunnen bevatten.
Als de opzoekwaarde een echt getal is (met getalopmaak), maar het overeenkomstige veld in de tabel als tekst is opgeslagen, moet u het getal eerst converteren naar tekst voordat u de opzoekactie uitvoert. De functie TEKST is perfect geschikt voor dit scenario:
=VLOOKUP(TEXT(G1,0),A2:D15,2,FALSE)Voer dit in uw doelcel in, druk op Enter, en het juiste resultaat wordt geretourneerd zoals hieronder wordt weergegeven:
Hier zorgt de getalnotatiecode „0” in de TEKST-functie ervoor dat uw getal eerst naar een platte tekstwaarde wordt geconverteerd, voordat de vergelijking plaatsvindt.
Als u onzeker bent over de mogelijke opmaak van uw Zoekwaardebereik of verwacht dat zowel Splitsen op tekst en nummer in uw opzoekkolom kunnen voorkomen, kunt u beide benaderingen nesten met behulp van de functie ALS.FOUTom naadloos alle mogelijkheden af te handelen:
=IFERROR(VLOOKUP(VALUE(G1),A2:D15,2,0),VLOOKUP(TEXT(G1,0),A2:D15,2,0))Voer deze formule in uw resultaatcel in. De formule probeert eerst de opzoekactie uit door uw waarde naar een getal te converteren; als dat mislukt — bijvoorbeeld omdat de waarde niet naar een getal kan worden omgezet — probeert de formule vervolgens uw waarde naar tekst te converteren en opnieuw op te zoeken. Dit is vooral handig bij datasets met gemengde opmaak of bij gedeelde bestanden zonder uniforme invoerstandaarden.
Nadat u een van bovenstaande formules hebt ingevoerd, vergeet niet de formule naar aangrenzende cellen te kopiëren als u deze op meerdere zoekwaardebereiken wilt toepassen — selecteer eenvoudig de cel en sleep de vulgreep omlaag, of gebruik Ctrl+C en Ctrl+V indien nodig. Voor grote tabellen zorgen deze formules voor betrouwbare overeenkomsten zonder uw oorspronkelijke database aan te passen.
Deze methode biedt een flexibele en universeel toepasbare oplossing voor de meeste werkbladopzoekacties. Voor zeer grote datasets of wanneer u veel records automatisch moet verwerken, kunt u echter overwegen om automatiseringstools zoals VBA te gebruiken voor nog grotere efficiëntie.
Los opmaakverschillen snel op met Kutools voor Excel
Als u liever een snellere, formulevrije oplossing gebruikt, biedt Kutools voor Excel de gebruiksvriendelijke functie **Tekst naar getal conversie**. Met slechts enkele muisklikken zet u getallen die als tekst zijn opgeslagen om naar echte getallen — of vice versa. Dit is bijzonder handig om opmaakproblemen op te lossen vóór het uitvoeren van opzoekacties zoals VLOOKUP of VERGELIJKEN.
Na installatie van Kutools voor Excel gaat u als volgt te werk.
- Selecteer het bereik met uw problematische gegevens, zoals getallen die als tekst zijn opgeslagen.
- Ga naar **Kutools** > **Inhoud** > **Tekst naar getal conversie**.
- In het pop-updialoogvenster:
- Kies „Tekst naar getal” als u opzoekfouten wilt herstellen die ontstaan doordat getallen als tekst zijn opgemaakt.(Of selecteer „Getal naar tekst” als de Zoekwaardebereik als tekst zijn opgeslagen.)
- Klik op „OK” om het gegevensformaat direct te converteren.
- Kies „Tekst naar getal” als u opzoekfouten wilt herstellen die ontstaan doordat getallen als tekst zijn opgemaakt.
Na conversie van tekst naar getal gedragen de geconverteerde cellen zich als echte getallen en tonen ze geen groene driehoekjes meer die op inconsistentie wijzen.
Deze aanpak maakt hulpkolommen, formules of VBA overbodig en is daardoor perfect voor een snelle opschoning vóór het gebruik van VLOOKUP.
Kutools voor Excel– Geef Excel een boost met meer dan 300 essentiële hulpmiddelen, waardoor uw werk sneller en eenvoudiger verloopt, en profiteer van AI-functies voor slimmere gegevensverwerking en productiviteit.Nu verkrijgen
VBA-macro: standaardiseer opmaak vóór VLOOKUP
Voor gebruikers die regelmatig werken met grote datasets, bestanden ontvangen uit externe bronnen of herhaalde automatisering nodig hebben, biedt een eenvoudige VBA-macro de oplossing om de gegevensopmaak programmatisch te standaardiseren – zowel in de opzoekwaardekolom als in de kolommen van de opzoektabellen. Zo zorgt u ervoor dat alle gegevens vóór het uitvoeren van VLOOKUP consistent worden omgezet naar tekst of getal, waardoor overeenkomstfouten door opmaakmismatches volledig worden voorkomen. VBA is hierbij bijzonder krachtig voor bulkverwerking: het bespaart tijd door handmatige aanpassingen overbodig te maken en garandeert gegevensconsistentie via naadloze automatisering.
Voordelen: Automatiseer opmaak voor grote bereiken of frequente workflows; minimaliseert het risico op ontbrekende of inconsistente opmaak; ideaal voor repetitieve taken.
Nadelen: Niet geschikt voor gebruikers met macrobeperkingen of voor personen die niet vertrouwd zijn met het gebruik van VBA-macro’s.
Zo gebruikt u een macro om Celopmaak te standaardiseren:
1. Ga naar het tabblad Ontwikkelaar en klik op Visual Basic om de VBA-editor te openen. Klik in het nieuwe venster op Invoegen > Module, kopieer vervolgens de volgende code en plak deze in het modulevenster:
Sub StandardizeLookupFormats()
' Ask the user to select the lookup column and choose a target format
Dim rng As Range
Dim userChoice As Integer
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.InputBox("Select the range to standardize (lookup or data column):", xTitleId, Type:=8)
If rng Is Nothing Then Exit Sub
userChoice = MsgBox("Convert selected data to Number? (Click Yes to convert to Number, No to convert to Text)", vbYesNoCancel, xTitleId)
If userChoice = vbYes Then
For Each cell In rng
If IsNumeric(cell.Value) Then
cell.Value = Val(cell.Value)
cell.NumberFormat = "General"
End If
Next
ElseIf userChoice = vbNo Then
For Each cell In rng
If Not IsEmpty(cell.Value) Then
cell.Value = CStr(cell.Value)
cell.NumberFormat = "@"
End If
Next
Else
Exit Sub
End If
End Sub2. Sluit de VBA-editor. Om de macro uit te voeren, gaat u terug naar Excel, drukt u op Alt+F8, selecteert u StandardizeLookupFormats en klikt u op Uitvoeren.
Werkingsdetails en tips:
- Deze macro vraagt u om de kolom te selecteren (uw OPZOEKBEREIK of TABELBEREIK) die u wilt standaardiseren.
- Na uw selectie wordt u gevraagd of u het bereik naar getallen wilt converteren (klik Ja) of naar tekst (klik Nee). Kies dezelfde opmaak voor zowel uw opzoek- als tabelkolommen om te garanderen dat VLOOKUP betrouwbaar overeenkomt.
- Nadat u deze macro hebt uitgevoerd, moet u mogelijk het werkblad herberekenen (druk op)F9) of uw VLOOKUP-formules opnieuw toepassen als de resultaten niet direct verschijnen.
- Als u een foutmelding krijgt dat macro’s zijn uitgeschakeld, schakelt u deze eerst in via uw Excel-instellingen voordat u verdergaat.
Deze oplossing is ideaal voor terugkerende gegevensimports of voor het opschonen van inconsistente kolommen in grote datasets vóór het gebruik van VLOOKUP of andere opzoekfuncties.
Andere ingebouwde Excel-methoden: gebruik ‘Tekst naar kolommen’ om gegevensopmaak te herstellen
Een snelle manier om getallen en tekstformaat in Excel aan elkaar aan te passen, is het gebruik van de functie Tekst naar kolommen. Deze ingebouwde tool wordt vaak gebruikt om gegevens te splitsen, maar kan ook een formaatconversie afdwingen zonder dat je formules hoeft aan te passen. Ideaal voor eenmalige correcties of eenvoudige lijsten!
Voordelen: Zeer eenvoudig – geen formules of code nodig – en behoudt de oorspronkelijke gegevensstructuur.Nadelen: Het beste geschikt voor eenmalige correcties; werkt niet automatisch mee wanneer de gegevens wijzigen.
Gebruik deze methode om getallen die als tekst zijn opgeslagen (of vice versa) in een kolom om te zetten:
- Selecteer de kolom met de vermoedelijke opmaakmismatch (bijvoorbeeld uw opzoekkolom of de kolom waarnaar VLOOKUP verwijst).
- Op het tabblad Gegevens klikt u op Tekst naar kolommen.
- Kies in de wizard Gescheiden en klik vervolgens op Volgende.
- Schakel alle selectievakjes voor scheidingstekens uit (aangezien u de gegevens niet splitst) en klik op Volgende.
- Bij Kolomgegevensindeling selecteert u Algemeen (om Excel te dwingen getallen als getallen te herkennen) of selecteert u Tekst (om getal naar tekst te converteren).
- Klik op Voltooien om het proces af te ronden.
Zodra u klaar bent, is de opmaak van uw gegevens automatisch afgestemd op getal of tekst, zodat VLOOKUP-mismatches worden verholpen. Controleer altijd enkele cellen om te bevestigen dat de conversie naar verwachting is verlopen. Herhaal het proces indien nodig zowel voor uw opzoekkolom als voor uw zoekwaardebereik om de consistentie te maximaliseren.
Praktische herinneringen: „Tekst naar kolommen” wijzigt gegevens direct en kan celinhoud overschrijven als er direct rechts van bestaande gegevens staan. Kopieer uw kolom bij twijfel eerst naar een leeg gebied en sla altijd een back-up van uw bestand op vóór het gebruik van bulkgegevenstools.
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