Wie führt man einen SVERWEIS für als Text gespeicherte Zahlen in Excel durch?
Bei der Verwendung von VLOOKUP in Excel können Formatinkonsistenzen – etwa wenn der Suchwert als Text vorliegt, die Suchspalte jedoch Zahlen enthält, oder umgekehrt – zu Fehlern oder erfolglosen Suchvorgängen führen. Solche Probleme treten besonders häufig auf, wenn Daten aus externen Quellen stammen, importiert wurden oder in großen, gemeinsam genutzten Datensätzen verwendet werden. Um sicherzustellen, dass VLOOKUP zuverlässig und wie erwartet funktioniert und korrekte Ergebnisse liefert, ist es entscheidend, diese Formatabweichungen zu beheben. Diese Schritt-für-Schritt-Anleitung bietet mehrere praktische Lösungen, um solche Inkonsistenzen effizient zu erkennen und zu korrigieren – unabhängig davon, wie Zahlen in Ihrer Arbeitsmappe gespeichert sind.
Dieser Artikel stellt effektive Methoden zur Behebung solcher Fehler vor – von Formelanpassungen über integrierte Excel-Tools bis hin zur VBA-Automatisierung für die Massen- oder automatisierte Verarbeitung. Zudem werden die Vor- und Nachteile jedes Ansatzes erläutert, damit Sie die für Ihr Szenario beste Lösung wählen können.
- VLOOKUP für als Text gespeicherte Zahlen mithilfe von Formeln
- Beheben Sie Formatinkonsistenzen schnell mit Kutools für Excel
- VBA-Makro: Standardisieren Sie Formate vor VLOOKUP
- Weitere integrierte Excel-Methoden: Verwenden Sie „Text in Spalten“, um Datenformate zu korrigieren
VLOOKUP für als Text gespeicherte Zahlen mithilfe von Formeln
Wenn Ihre Suchdaten an einer Stelle als Text gespeicherte Zahlen und an anderer Stelle echte Zahlen enthalten, scheitert VLOOKUP aufgrund dieser Formatinkonsistenz beim Finden von Übereinstimmungen. Eine der direktesten Lösungen ist die Verwendung von Excel-Formeln, die den Suchwert oder die Suchspalte dynamisch in ein einheitliches Format konvertieren. Dieser Ansatz eignet sich hervorragend für die meisten Arbeitsblattoperationen, lässt sich mühelos umsetzen und bewahrt Ihre Originaldaten unverändert.
Wenn beispielsweise Ihr Suchwert als Text gespeichert ist, während das entsprechende Feld in der Tabelle als Zahl formatiert ist, können Sie innerhalb der VLOOKUP-Formel die WERT-Funktion nutzen, um den Text in eine Zahl umzuwandeln.
Geben Sie die folgende Formel in eine leere Zelle ein, in der das Ergebnis angezeigt werden soll:
=VLOOKUP(VALUE(G1),A2:D15,2,FALSE)Drücken Sie nach Eingabe der Formel die Eingabetaste, um den Wert abzurufen, der Ihren Kriterien entspricht, wie in der folgenden Abbildung dargestellt:
Erläuterung der Parameter und Tipps:
- G1: Die Zelle, die den gesuchten Wert enthält – egal ob Text oder Zahl.
- A2:D15: Der Bereich Ihrer Datentabelle, der sowohl die Suchspalte als auch die Spalten mit den gewünschten Rückgabewerten umfasst.
- 2: Die Spaltennummer (gezählt ab der linken Spalte des Tabellenbereichs) des gewünschten Ergebnisses.
Achten Sie darauf, ob der Suchwertbereich führende oder abschließende Leerzeichen enthält, da diese ebenfalls zu Suchfehlern führen können. Kombinieren Sie gegebenenfalls die GLÄTTEN-Funktion, falls Ihre Daten überflüssige Leerzeichen enthalten könnten.
Wenn der Suchwert eine echte Zahl (im Zahlenformat) ist, das entsprechende Feld in der Tabelle jedoch als Text gespeichert wurde, müssen Sie die Zahl vor der Suche in Text umwandeln. Dafür eignet sich die TEXT-Funktion:
=VLOOKUP(TEXT(G1,0),A2:D15,2,FALSE)Geben Sie dies in Ihre Zielzelle ein, drücken Sie Eingabe, und das korrekte Ergebnis wird wie unten gezeigt zurückgegeben:
Dabei sorgt der Zahlenformatcode „0“ innerhalb der TEXT-Funktion dafür, dass Ihre Zahl vor dem Abgleich in einen reinen Textwert umgewandelt wird.
Wenn Sie sich bezüglich der möglichen Formate Ihrer Suchwertbereich unsicher sind oder erwarten, dass sowohl Nach Text und Zahl aufteilen in Ihrer Suchspalte vorkommen können, können Sie beide Ansätze mithilfe der WENNFEHLER-Funktion verschachteln, um nahtlos alle Möglichkeiten abzudecken:
=IFERROR(VLOOKUP(VALUE(G1),A2:D15,2,0),VLOOKUP(TEXT(G1,0),A2:D15,2,0))Geben Sie diese Formel in Ihre Ergebniszelle ein. Zunächst versucht sie, die Suche durch Umwandlung Ihres Werts in eine Zahl durchzuführen; falls dies fehlschlägt (z. B. weil der Wert nicht in eine Zahl umgewandelt werden kann), versucht sie stattdessen, Ihren Wert in Text umzuwandeln und erneut zu suchen. Dies ist besonders nützlich bei Datensätzen mit gemischten Formaten oder in gemeinsam genutzten Dateien, bei denen keine einheitlichen Dateneingabestandards vorliegen.
Nach Eingabe einer der oben genannten Formeln können Sie diese bei Bedarf ganz einfach auf benachbarte Zellen übertragen – markieren Sie dazu die Zelle und ziehen Sie am Ausfüllkästchen nach unten oder verwenden Sie Strg+C und Strg+V. Bei großen Tabellen sorgen diese Formeln für eine zuverlässige Übereinstimmung, ohne Ihre ursprüngliche Datenbank zu verändern.
Diese Methode bietet eine flexible und universell einsetzbare Lösung für die meisten arbeitsblattbasierten Suchvorgänge. Für sehr große Datensätze oder bei automatisierter Massenverarbeitung empfiehlt sich jedoch der Einsatz von Automatisierungstools wie VBA, um noch höhere Effizienz zu erzielen.
Beheben Sie Formatinkonsistenzen schnell mit Kutools für Excel
Wenn Sie eine schnellere Lösung ohne Formeln bevorzugen, bietet Kutools für Excel das benutzerfreundliche Werkzeug „Umwandlung zwischen Text und Zahl“. Damit konvertieren Sie mit nur wenigen Klicks als Text gespeicherte Zahlen in echte Zahlen – oder umgekehrt. Das ist besonders hilfreich, um Formatprobleme vor Suchvorgängen wie VLOOKUP oder VERGLEICH zu beheben.
Nach der Installation von Kutools für Excel gehen Sie bitte wie folgt vor.
- Wählen Sie den Bereich aus, der Ihre problematischen Daten enthält – beispielsweise als Text gespeicherte Zahlen.
- Gehen Sie zu „Kutools“ > „Inhalt“ > „Umwandlung zwischen Text und Zahl“.
- Im erscheinenden Dialogfeld:
- Wählen Sie „Text zu Zahl“, wenn Sie Suchfehler beheben möchten, die auf als Text formatierte Zahlen zurückzuführen sind.(Oder wählen Sie „Zahl zu Text“, wenn die Suchwertbereich als Text gespeichert sind.)
- Klicken Sie auf „OK“, um das Datenformat sofort zu konvertieren.
- Wählen Sie „Text zu Zahl“, wenn Sie Suchfehler beheben möchten, die auf als Text formatierte Zahlen zurückzuführen sind.
Nach der Konvertierung von Text in Zahlen verhalten sich die umgewandelten Zellen wie echte Zahlen und zeigen keine grünen Dreiecksmarkierungen für Inkonsistenzen mehr an.
Dieser Ansatz macht Hilfsspalten, Formeln oder VBA überflüssig und eignet sich daher ideal für eine schnelle Bereinigung vor der Anwendung von VLOOKUP.
Kutools für Excel– Erweitern Sie Excel mit über 300 unverzichtbaren Tools, um Ihre Arbeit schneller und einfacher zu erledigen, und nutzen Sie KI-Funktionen für intelligentere Datenverarbeitung und höhere Produktivität.Jetzt herunterladen
VBA-Makro: Standardisieren Sie Formate vor VLOOKUP
Für Anwender, die regelmäßig mit großen Datensätzen arbeiten, externe Dateien einbinden oder wiederkehrende Automatisierung benötigen, bietet ein einfaches VBA-Makro die ideale Lösung: Es standardisiert programmgesteuert das Datenformat sowohl in der Suchspalte als auch in der Zielspalte. So stellen Sie sicher, dass alle Daten vor der Ausführung von VLOOKUP einheitlich entweder als Text oder als Zahl vorliegen – und vermeiden Abgleichfehler durch Formatunterschiede. VBA eignet sich besonders für die Massenverarbeitung, spart manuelle Anpassungen und gewährleistet dank Automatisierung eine durchgängig hohe Datenqualität.
Vorteile: Automatisiert die Formatierung großer Bereiche oder häufig wiederkehrender Arbeitsabläufe; minimiert das Risiko fehlender oder inkonsistenter Formatierung und eignet sich ideal für repetitive Aufgaben.
Nachteile: Nicht geeignet für Benutzer mit Makroeinschränkungen oder für alle, die mit der Verwendung von VBA-Makros nicht vertraut sind.
So verwenden Sie ein Makro zur Standardisierung von Zellenformat:
1. Wechseln Sie zur Entwickler-Registerkarte und klicken Sie auf Visual Basic, um den VBA-Editor zu öffnen. Klicken Sie im neuen Fenster auf Einfügen > Modul, und fügen Sie anschließend den folgenden Code in den Modulbereich ein:
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. Schließen Sie den VBA-Editor. Um das Makro auszuführen, kehren Sie zu Excel zurück, drücken Sie Alt+F8, wählen Sie StandardizeLookupFormats aus und klicken Sie auf Ausführen.
Vorgehensweise und Tipps:
- Dieses Makro fordert Sie auf, die Spalte auszuwählen – entweder Ihren SUCHBEREICH oder TABELLENBEREICH –, deren Format standardisiert werden soll.
- Nach der Auswahl werden Sie gefragt, ob der Bereich in Zahlen („Ja“ klicken) oder in Text („Nein“ klicken) konvertiert werden soll. Wählen Sie für Ihre Such- und Tabellenspalten stets dasselbe Format, um eine zuverlässige Übereinstimmung bei VLOOKUP zu gewährleisten.
- Nach Ausführung dieses Makrosmüssen Sie unter Umständen das Arbeitsblatt neu berechnen (drücken Sie)F9), oder Ihre VLOOKUP-Formeln erneut anwenden, falls die Ergebnisse nicht sofort angezeigt werden.
- Sollten Sie eine Fehlermeldung erhalten, dass Makros deaktiviert sind, aktivieren Sie diese in Ihren Excel-Einstellungen, bevor Sie fortfahren.
Diese Lösung eignet sich ideal für wiederkehrende Datenimporte oder zur Bereinigung inkonsistenter Spalten in großen Datensätzen – bevor VLOOKUP oder andere Suchvorgänge zum Einsatz kommen.
Weitere integrierte Excel-Methoden: Verwenden Sie „Text in Spalten“, um Datenformate zu korrigieren
Ein schneller Weg, um Zahlen- und Textformate in Excel anzugleichen, ist die Verwendung der integrierten Funktion Text in Spalten. Dieses Standardtool wird häufig eingesetzt, um Daten aufzuteilen, kann aber auch eine Formatumwandlung erzwingen – ganz ohne Formeln bearbeiten zu müssen. Es eignet sich daher ideal für einmalige Korrekturen oder die Arbeit mit einfachen Listen.
Vorteile: Sehr einfach – keine Formeln oder Codes erforderlich, und die ursprüngliche Datenstruktur bleibt erhalten. Nachteile: Am besten geeignet für einmalige Korrekturen, da es sich bei Änderungen der Daten nicht automatisch aktualisiert.
So verwenden Sie diese Methode, um in einer Spalte als Text gespeicherte Zahlen (oder umgekehrt) umzuwandeln:
- Wählen Sie die Spalte mit dem vermuteten Formatkonflikt aus (z. B. Ihre Suchspalte oder die von VLOOKUP referenzierte Spalte).
- Klicken Sie auf der Registerkarte Daten auf Text in Spalten.
- Wählen Sie im Assistenten Getrennt und klicken Sie anschließend auf Weiter.
- Deaktivieren Sie alle Trennzeichen-Kontrollkästchen (da Sie die Daten nicht aufteilen möchten) und klicken Sie auf Weiter.
- Wählen Sie unter Spaltenformat Standard (um Excel zu zwingen, Zahlen als solche zu erkennen) oder wählen Sie Text (um Zahlen in Text zu konvertieren).
- Klicken Sie auf Fertig stellen, um den Vorgang abzuschließen.
Sobald Sie fertig sind, wird das Format Ihrer Daten zwangsweise entweder auf Zahl oder Text ausgerichtet, wodurch VLOOKUP-Unstimmigkeiten behoben werden. Prüfen Sie stets einige Zellen, um sicherzustellen, dass die Umwandlung wie erwartet funktioniert hat. Wiederholen Sie gegebenenfalls den Vorgang sowohl für Ihre Suchspalte als auch für Ihr Suchwertbereich, um maximale Konsistenz zu gewährleisten.
Praktische Hinweise: „Text in Spalten“ verändert Ihre Daten direkt und kann Inhalte in den unmittelbar rechts daneben liegenden Zellen überschreiben. Sichern Sie Ihre Spalte daher vorab, indem Sie sie in einen leeren Bereich kopieren, und erstellen Sie stets eine Sicherungskopie Ihrer Datei, bevor Sie Massenbearbeitungswerkzeuge einsetzen.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins