Wie kann man in Excel nach Zahlen suchen, die als Text gespeichert sind?
Wenn Sie in Excel VLOOKUP verwenden, können Formatunterschiede – insbesondere wenn ein Suchwert als Text gespeichert ist, während die Suchspalte Zahlen enthält oder umgekehrt – zu Suchfehlern oder Fehlern führen. Diese Formatunterschiede sind ein häufiges Problem, insbesondere wenn Daten aus externen Quellen stammen, importiert werden oder bei der Arbeit mit großen und kollaborativen Datensätzen. Das Auflösen dieser Unterschiede ist entscheidend, damit VLOOKUP wie erwartet funktioniert und Ihnen die richtigen Informationen liefert. Diese Schritt-für-Schritt-Anleitung stellt mehrere praktische Lösungen vor, um diese Formatinkonsistenzen zu beheben und zuverlässige und genaue Suchvorgänge unabhängig davon sicherzustellen, wie Zahlen in Ihrer Arbeitsmappe gespeichert sind.
Dieser Artikel wird effektive Möglichkeiten zur Behandlung solcher Fehler demonstrieren, einschließlich Formelkorrekturen, integrierter Excel-Tools und VBA-Automatisierung für Massen- oder automatisierte Verarbeitung. Wir besprechen auch die Vorteile und Überlegungen jeder Methode, um Ihnen bei der Auswahl des am besten geeigneten Ansatzes für Ihr Szenario zu helfen.
- Zahlen, die als Text gespeichert sind, mit Formeln suchen
- Formatunterschiede schnell mit Kutools für Excel beheben
- VBA-Makro: Formate vor VLOOKUP standardisieren
- Andere integrierte Excel-Methoden: 'Text in Spalten' verwenden, um Datenformate zu korrigieren
Zahlen, die als Text gespeichert sind, mit Formeln suchen
Wenn Ihre Suchdaten an einer Stelle Zahlen enthalten, die als Text gespeichert sind, und an anderer Stelle tatsächliche Zahlen, kann VLOOKUP aufgrund dieser Formatinkonsistenz keine Übereinstimmungen finden. Eine der direktesten Lösungen ist die Verwendung von Excel-Formeln, die den Suchwert oder die Suchspalte während des Vorgangs in ein konsistentes Format konvertieren. Dieser Ansatz funktioniert gut bei den meisten Tabellenblattoperationen, ist einfach anzuwenden und belässt Ihre ursprünglichen Daten unverändert.
Wenn Ihr Suchwert zum Beispiel als Text gespeichert ist, während das entsprechende Feld in der Tabelle als Zahl formatiert ist, können Sie die VALUE-Funktion verwenden, um den Text innerhalb der VLOOKUP-Formel in eine Zahl umzuwandeln.
Geben Sie die folgende Formel in eine leere Zelle ein, in der Sie das Ergebnis anzeigen möchten:
=VLOOKUP(VALUE(G1),A2:D15,2,FALSE)Drücken Sie nach dem Eingeben der Formel die Eingabetaste, um den Wert entsprechend Ihren Kriterien abzurufen, wie im folgenden Screenshot dargestellt:
Parametererklärung und Tipps:
- G1: Die Zelle, die den Wert enthält, nach dem Sie suchen möchten (kann entweder Text oder Zahl sein).
- A2:D15: Der Bereich Ihrer Datentabelle, der die Suchspalte und die Spalten mit den Informationen, die zurückgegeben werden sollen, enthält.
- 2: Die Spaltennummer (von der am weitesten links stehenden Spalte des Tabellenbereichs) für das gewünschte Ergebnis.
Seien Sie vorsichtig, führende/abschließende Leerzeichen in Suchwerten zu überprüfen, da diese ebenfalls zu Suchfehlern führen können. Berücksichtigen Sie die Kombination mit der TRIM-Funktion, wenn Ihre Daten zusätzliche Leerzeichen enthalten könnten.
Wenn der Suchwert eine echte Zahl (Zahlenformat) ist, aber das entsprechende Feld in der Tabelle als Text gespeichert ist, müssen Sie die Zahl vor der Suche in Text umwandeln. Die TEXT-Funktion eignet sich für dieses Szenario:
=VLOOKUP(TEXT(G1,0),A2:D15,2,FALSE)Geben Sie dies in Ihre Zielzelle ein, drücken Sie die Eingabetaste, und das richtige Ergebnis wird wie unten gezeigt zurückgegeben:
Hier 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 über die möglichen Formate Ihrer Suchwerte unsicher sind oder sowohl Text als auch Zahlen in Ihrer Suchspalte erwarten, können Sie beide Ansätze mit der IFERROR-Funktion verschachteln, um alle Möglichkeiten nahtlos zu behandeln:
=IFERROR(VLOOKUP(VALUE(G1),A2:D15,2,0),VLOOKUP(TEXT(G1,0),A2:D15,2,0))Geben Sie diese Formel in Ihre Ergebniszelle ein. Sie wird zunächst versuchen, die Suche durchzuführen, indem sie Ihren Wert in eine Zahl umwandelt; falls dies fehlschlägt (zum Beispiel, wenn der Wert nicht in eine Zahl umgewandelt werden kann), wird sie dann versuchen, Ihren Wert in Text umzuwandeln und erneut zu suchen. Dies ist besonders nützlich in Datensätzen mit gemischten Formaten oder in gemeinsam genutzten Dateien, in denen Eingabestandards nicht einheitlich sind.
Nachdem Sie eine der obigen Formeln eingegeben haben, denken Sie daran, die Formel in benachbarte Zellen zu kopieren, wenn Sie sie auf mehrere Suchwerte anwenden müssen – wählen Sie einfach die Zelle aus, ziehen Sie den Ausfüllgriff nach unten oder verwenden Sie bei Bedarf Strg+C und Strg+V. Bei großen Tabellen helfen diese Formeln dabei, zuverlässige Übereinstimmungen sicherzustellen, ohne Ihre ursprüngliche Datenbank zu verändern.
Diese Methode bietet eine flexible und universell anwendbare Lösung für die meisten tabellenbasierten Suchvorgänge. Für sehr große Datensätze oder wenn Sie viele Datensätze automatisch verarbeiten müssen, könnten Sie jedoch die Verwendung von Automatisierungstools wie VBA für noch größere Effizienz in Erwägung ziehen.
Formatunterschiede schnell mit Kutools für Excel beheben
Wenn Sie eine schnellere, formelfreie Lösung bevorzugen, bietet Kutools für Excel ein benutzerfreundliches Tool namens Zwischen Text und Zahl konvertieren. Diese Funktion ermöglicht es Ihnen, Zahlen, die als Text gespeichert sind, in echte Zahlen umzuwandeln – oder umgekehrt – mit nur wenigen Klicks. Es ist besonders hilfreich, um Formatprobleme zu lösen, bevor Sie Suchvorgänge wie VLOOKUP oder MATCH durchführen.
Führen Sie nach der Installation von Kutools für Excel bitte folgende Schritte aus:
- Wählen Sie den Bereich aus, der Ihre problematischen Daten enthält (z. B. Zahlen, die als Text gespeichert sind).
- Gehen Sie zu „Kutools“ > „Inhalt“ > „Zwischen Text und Zahl konvertieren“.
- Im Popup-Dialog:
- Wählen Sie „Text zu Zahl“, wenn Sie Suchfehler aufgrund textformatierter Zahlen beheben. (Oder wählen Sie „Zahl zu Text“, wenn die Suchwerte als Text gespeichert sind.)
- Klicken Sie auf „OK“, um das Datenformat sofort zu konvertieren.
- Wählen Sie „Text zu Zahl“, wenn Sie Suchfehler aufgrund textformatierter Zahlen beheben.
Nach der Konvertierung von Text zu Zahlen verhalten sich die konvertierten Zellen wie echte Zahlen und zeigen keine grünen Dreiecksindikatoren für Inkonsistenzen mehr an.
Dieser Ansatz eliminiert die Notwendigkeit von Hilfsspalten, Formeln oder VBA – was ihn ideal für eine schnelle Bereinigung vor der Anwendung von VLOOKUP macht.
Kutools für Excel - Verleihen Sie Excel mit über 300 essenziellen Tools einen echten Schub. Nutzen Sie dauerhaft kostenlose KI-Funktionen! Holen Sie es sich jetzt
VBA-Makro: Formate vor VLOOKUP standardisieren
Für Benutzer, die regelmäßig mit großen Datensätzen arbeiten, externe Dateien erhalten oder wiederholte Automatisierung benötigen, kann die Verwendung eines einfachen VBA-Makros programmgesteuert das Datenformat sowohl in der Suchwertspalte als auch in der Suchtabelle standardisieren. Auf diese Weise stellen Sie sicher, dass alle Daten entweder in Text oder Zahl konvertiert werden, bevor Sie VLOOKUP ausführen und somit Matching-Fehler aufgrund von Formatunterschieden eliminieren. VBA ist besonders nützlich für die Massenverarbeitung, spart manuelle Anpassungen und gewährleistet durch Automatisierung die Datenkonsistenz.
Vorteile: Automatisierung der Formatierung für große Datenbereiche oder häufige Workflows; minimiert das Risiko von fehlenden oder inkonsistenten Formatierungen; geeignet für repetitive Aufgaben.
Nachteile: Nicht geeignet für Benutzer mit Makroeinschränkungen oder solche, die mit der Verwendung von VBA-Makros nicht vertraut sind.
So können Sie ein Makro verwenden, um Zellformate zu standardisieren:
1. Gehen Sie zur Registerkarte Entwickler und klicken Sie auf Visual Basic, um den VBA-Editor zu öffnen. Klicken Sie im neuen Fenster auf Einfügen > Modul, und kopieren und fügen Sie 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 und klicken Sie auf Ausführen.
Betriebsdetails und Tipps:
- Dieses Makro fordert Sie auf, die Spalte (entweder Ihren LOOKUP-Bereich oder TABLE-Bereich) auszuwählen, die Sie standardisieren möchten.
- Nach der Auswahl wird gefragt, ob Sie den Bereich in Zahlen (klicken Sie auf Ja) oder in Text (klicken Sie auf Nein) konvertieren möchten. Wählen Sie dasselbe Format für Ihre Such- und Tabellenspalten, um sicherzustellen, dass VLOOKUP zuverlässig übereinstimmt.
- Nachdem Sie dieses Makro ausgeführt haben, müssen Sie möglicherweise das Arbeitsblatt neu berechnen (drücken Sie F9) oder Ihre VLOOKUP-Formeln erneut anwenden, wenn die Ergebnisse nicht sofort erscheinen.
- Wenn Sie eine Fehlermeldung erhalten, dass Makros deaktiviert sind, aktivieren Sie Makros in Ihren Excel-Einstellungen, bevor Sie fortfahren.
Diese Lösung ist ideal für wiederkehrende Datenimporte oder wenn Sie inkonsistente Spalten in großen Datensätzen bereinigen, bevor Sie VLOOKUP oder andere Suchoperationen anwenden.
Andere integrierte Excel-Methoden: 'Text in Spalten' verwenden, um Datenformate zu korrigieren
Eine schnelle Möglichkeit, Zahlen- und Textformate in Excel auszurichten, ist die Verwendung der Funktion 'Text in Spalten'. Dieses integrierte Tool wird oft verwendet, um Daten zu teilen, kann aber auch eine Formatkonvertierung erzwingen, ohne Formeln zu bearbeiten, was hilfreich ist, wenn Sie eine einmalige Korrektur oder einfache Listen vornehmen möchten.
Vorteile: Sehr einfach, keine Formeln oder Codes erforderlich, behält die ursprüngliche Datenstruktur bei; Nachteile: Am besten für einmalige Korrekturen geeignet, aktualisiert sich nicht automatisch, wenn sich die Daten ändern.
Um diese Methode zu verwenden, um Zahlen, die als Text (oder umgekehrt) in einer Spalte gespeichert sind, zu konvertieren:
- Wählen Sie die Spalte mit dem vermuteten falschen Format 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 'Durch Trennzeichen getrennt' aus und klicken Sie auf Weiter.
- Deaktivieren Sie alle Kontrollkästchen für Trennzeichen (da Sie die Daten nicht teilen); klicken Sie auf Weiter.
- Wählen Sie unter Spaltendatenformat 'Allgemein' (um Excel zu zwingen, Zahlen als Zahlen zu erkennen) oder 'Text' (um Zahlen in Text umzuwandeln).
- Klicken Sie auf Fertig, um den Vorgang abzuschließen.
Sobald Sie fertig sind, wird Ihr Datenformat entweder zu Zahlen oder Text angepasst, wodurch VLOOKUP-Unstimmigkeiten behoben werden. Überprüfen Sie immer einige Zellen, um sicherzustellen, dass die Konvertierung wie erwartet funktioniert hat. Wiederholen Sie bei Bedarf den Vorgang für Ihre Suchspalte und Ihre Suchwerte, um maximale Konsistenz zu erreichen.
Praktische Hinweise: 'Text in Spalten' ändert Daten direkt, daher kann es Zelleninhalte überschreiben, wenn Sie vorhandene Daten direkt rechts daneben haben. Kopieren Sie Ihre Spalte in einen leeren Bereich, wenn Sie unsicher sind, und speichern Sie immer eine Sicherungskopie Ihrer Datei, bevor Sie Massendatentools verwenden.
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins