Faq/Calc/Wie konvertiere ich als Text geschriebene Zahlen in numerische Daten?
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Spende
Wie konvertiere ich als Text geschriebene Zahlen in numerische Daten?
Es kann passieren, dass importierter oder kopierter Text bereits als Text formatiert ist und trotzdem wie eine Zahl oder wie Zeit aussieht. Text kann allerdings in Berechnungen nicht verwendet werden. Eine Zelle, die Text enthält, der (später) als Zahl umformatiert wird, bekommt in der Eingabezelle einen ' Apostroph vorangestellt. Dadurch wird gezeigt, dass der Text ggf. auch als Zahl interpretiert werden soll. Als Konsequenz führen arithmetische Kalkulationen mit einer solchen Zelle zu einem #VALUE!-Fehler. Bei einer numerischen Sequenz wie z.Bsp. SUM() wird eine solche Zelle ignoriert. Ein solcher numerischer Text wird folgendermaßen in eine echte Zahl konvertiert. Dadurch werden auch die Seperatoren (Dezimaltrennzeichen, Datum,...) entsprechend dem Gebietsschema verwendet. Eine ledigliche Umfornmatierung der Zelle ändert nicht den Inhalt. Die Werte müssen neu eingegeben werden, um konvertiert zu werden.
numerischen Text Suchen und Ersetzen
- Erst wird der Zellbereich markiert, der die zu konvertierenden Daten enthält markiert
- mit Strg + M oder einem Rechtsklick auf den markierten Kontext kommt man zum Kontextmenü und wählt Direkte Formatierung löschen
- Wenn das aktuelle Gebietsschema die benötigten Dezimaltrennzeichen zur Konversion nicht verwendet, sollte vorübergehend ein Zahlenforemat mit Dezimaltrennzeichen eingestellt werden (z. Bsp. Englisch-US). Später kann dann das gewünschte Format eingestellt werden. Oder wechseln Sie zum nächsten Beispiel weiter unten.
- Strg + M oder ▸ auswählen
- Im Feld Suchen wird ein + eingegeben
- Im Feld Ersetzen wird §0 eingegeben
- unter Weitere Optionen wird folgendes aktiviert
- Nur in Auswahl
- Reguläre Ausdrücke
- bestätigen mit
Das gibt den Zellinhalt wie von der Tastatur neu ein und erkennt dabei numerische/Datums-/Zeit-Werte.
Suchen und Ersetzen mit unterschiedlichem Dezimaltrennzeichen
Wenn die Zahl nicht als solche erkannt wird, weil das Gebietsschema ein anderes Dezimaltrernnzeichen verwendet (und daher die Eingabezeile kein ' Apostroph als Präfix anzeigt), dann wird
- der Zellbereich mit den zu konvertierenden Daten markiert
- mit Strg + M oder einem Rechtsklick das Kontextmenü aufgerufen und Direkte Formatierung löschen aufgerufen
- mit Strg + H oder ▸ der Suchen und Ersetzen Dialog aufgerufen und
- im Feld Suchen: .
- im Feld Ersetzen: , eingegeben und
- unter Andere Optionen
- Nur in Auswahl ausgewählt
- Reguläre Ausdrücke abgewählt
- alle ersetzen
Hinweis: das funktioniert natürlich nicht, wenn die Zahlen einen Mix aus Dezimaltrennzeichen enthalten. Die Ersetzung bringt identische Dezimaltrennzeichen.
Falsche Datumsreihenfolge Suchen und Ersetzen
Wenn Daten in unterschiedlichen Datumsformaten wie z. Bsp. 06/28/2021 vorliegen, und Ihr Gebietsschema Day/Month/Year verwendet, also z. Bsp. 28/06/2021, oder einige Daten wie 06/01/2021 erkannt aber falsch interpretiert werden, dann
- drücken Sie NICHT Strg + M und löschen Sie KEINE Zahlen-/Datums-Formatierungen für ausgewählte Zellen, lassen Sie alle Datumsformate intakt, Nur, wenn die Zellen als Text formatiert sind, dann und nur dann reformatieren Sie sie als Standard.
- Es wid der Zellbereich mit den zu konvertierenden Daten markiert.
- Über STRG + H oder ▸ wid der Dialog aufgerufen
- im Feld Suchen: wird (\d+)/(\d+)/(\d+) eingegeben
- im Feld ersetzen: wird $2/$1/$3 eingegeben
- unter Andere Optionen wird folgendes aktiviert:
- Nur in Auswahl
- Reguläre Ausdrücke
- alle ersetzen
Erklärung des Regulären Ausdruckes Suchen
- (\d+) eine Gruppe von Zahlen, Monat
- / der Datumstrenner
- (\d+) eine Gruppe von Zahlen, Tag des Monats
- / der Datumstrenner
- (\d+) eine Gruppe von Zahlen, Jahr
Erklärung des Regulären Ausdruckes Ersetzen
- (\d+) die zweite Gruppe, Gruppe von Zahlen, Tag des Monats
- / der Datumstrenner
- (\d+) die erste Gruppe, Gruppe von Zahlen, Monat
- / der Datumstrenner
- (\d+) die dritte Gruppe, Gruppe von Zahlen, Jahr
Der Quelltext und das Ziel-Gebietsschema haben ggf. andere Erfordernisse hinsichtlich der Reihenfolge von Tag, Monat und Jahr bzw. dem Datumstrenner. Diese sind zu adaptieren.
Eine Konvertierung in das internationale YYYY-MM-DD ISO 8601 Format wird von jedem Gebietsschema erkannt (unter Annahme der originalen Monat/Tag/Jahr Ordnung von oben)
- Ersetze: $3-$1-$2
oder, wenn das Jahr zweistellig angegeben ist, wird das Jahrhundert ergänzt. Ansonsten wird das Ergebnis zweideutig und nicht im ISO-Datum angegeben, so wie
- Ersetze: 20$3-$1-$2
falsche Datumsreihenfolge mit alphanumerischen Monatsnamen Suchen und Ersetzen
Wenn ein Datum mit Monatsnamen vorliegt, z. Bsp. April 1, 2021 und das Gebietsschema eine unterschiedliche Sortierung zu Tag/Monat wie z. Bsp. 1 April 2021 oder sogar einen Monatsnamen in einer anderen Sprache vorsieht, dann wird
- der Zellbereich mit den zu konvertierenden Daten markiert
- mit Strg + M oder einem Rechtsklick das Kontextmenü aufgerufen und Direkte Formatierung löschen aufgerufen
- wenn das Gebietsschema die benötigten Monatsnamen nicht enthält, dann sollte temporär ein Datumsformat gewählt werden, das die Namen enthält. Später nach der Konversion kann das endgültige Format eingestellt werden.
- mit Strg + H oder ▸ der Suchen und Ersetzen Dialog aufgerufen und
- im Feld Suchen: ([\p{Letter}]+)\s+(\d+).*\s+(\d+) eingegeben
- im Feld Ersetzen: $2 $1 $3 eingegeben und
- unter Andere Optionen
- Nur in Auswahl ausgewählt
- Reguläre Ausdrücke ausgewählt
- alle ersetzen
Erklärung des Regulären Ausdruckes Suchen
- ([\p{Letter}]+) eine Gruppe von Unicode-Zeichen, Monat
- \s+ mindestens ein Whitespace
- (\d+) eine Gruppe von Zahlen, Tag des Monats
- .* irgendein Zeichen, 0 oder mehr, ist das Komma im Beispiel
- \s+ mindestens ein Whitespace
- (\d+) eine Gruppe von Zahlen, Jahr
Erklärung des Ausdruckes Ersetzen
- $2 die zweite Gruppe, Gruppe von Zahlen, Tag des Monats
- ein Leerzeichen
- $1 die erste Gruppe, Gruppe von Buchstaben, Monat
- ein Leerzeichen
- $3 die dritte Gruppe, Gruppe von Zahlen, Jahr
Das Ziel-Gebietsschema hat ggf. andere Erfordernisse hinsichtlich der Reihenfolge von Tag, Monat und Jahr. Diese sind zu adaptieren.
CT2N - Convert Text To Number extension
Wenn diese Funktionalität öfters benötigt wird, kann die Extension CT2N - Convert Text To Number (and dates) installiert werden.
CSV Import
Beim CSV-Import kann im Import-Dialog ein Import-Gebietsschema angegeben werden. Dadurch werden Dezimaltrennzeichen, Währungskennzeichen und Tages- und Monats-Namen erkannt, um richtige numerische Zahlen zu erzeugen. Einige Datums- und Währungs-Formate werden nur erkannt, wenn die Option Spezielle Zahlen erkennen aktiviert ist. Das kann wiederum einige Texte in Zahlen konvertieren, bei denen eine Konvertierung nicht erforderlich ist.
Konvertieren mit Text in Spalten
▸ ist auch eine Option:
