Die beste Möglichkeit, führende Nullen aus einem nicht numerischen Wert in Excel zu entfernen

104985
Chris Farmer

Ich habe viele Zellen in einer Excel-Tabelle mit 9 Zeichen von 0-9 und AZ, die eine Anzahl von vorangestellten Nullen haben:

000000123  000001DA2  0000009Q5 0000L210A 0000014A0 0000A5500 00K002200

Ich möchte die führenden Nullen entfernen, um folgende Werte zu erhalten:

123  1DA2  9Q5 L210A 14A0 A5500 K002200

Wie mache ich das in einer Excel-Formel? Ich würde es vorziehen, die Verwendung eines VBA-Makros zu vermeiden.

11
Wenn Sie einige weitere Einschränkungen in den Daten haben, kann ich möglicherweise eine spezifischere Lösung finden. Lance Roberts vor 15 Jahren 0
Vielen Dank. Die Werte haben führende Nullen, so dass sie insgesamt 9 Zeichen enthalten. Ansonsten handelt es sich bei den Werten um Ziffern aus Ziffern 0-9, die mit dem Großbuchstaben AZ gemischt werden. Es ist möglich, dass eine abschließende Null vorhanden ist und Teil des tatsächlichen Werts ist und nicht getrimmt werden sollte. Chris Farmer vor 15 Jahren 0
Um ausdrücklich zu sein, gibt es niemals eingebettete Leerzeichen. Nur 0-9 und AZ. Chris Farmer vor 15 Jahren 0
Bearbeitet, um diese nachgestellten Null-Beispiele einzuschließen Chris Farmer vor 15 Jahren 0
@ Chris, ich kann es tun, wenn wir die maximale Anzahl von Nullen kennen, die jemals in der Zeichenfolge eingebettet erscheinen oder nachfolgen (obwohl es eine lange Formel ist). Lance Roberts vor 15 Jahren 0
Zellen sind billig, aber nicht erforderlich ... Toc hat eine brillante neue Antwort gepostet :-) http://superuser.com/questions/42844/best-way-to-remove-leading-zeros-from-a-non-numeric -Wert in Excel / 44279 # 44279 Arjan vor 15 Jahren 0

5 Antworten auf die Frage

13
Isaac Moses

Hier ist eine Lösung, die zellintensiv, aber korrekt ist.

Tragen Sie Ihre Daten in Spalte A ein.

In B1 füge die Formel hinzu:

=IF( LEFT(A1) = "0", RIGHT(A1, LEN(A1)-1), A1)

Dies prüft auf eine führende Null und entfernt diese.

Kopieren Sie diese Formel nach rechts, so viele Spalten, wie sich Zeichen in Ihren Daten befinden können (in diesem Fall 9, damit Sie zur Spalte J gelangen). Kopieren Sie es für jede Datenzeile.

Die letzte Spalte enthält Ihre Daten ohne führende Nullen.

Nach vielen Versuchen ist dies die einzige Version, die ohne den Einsatz von VBA funktioniert. skamradt vor 15 Jahren 0
Kommentar von @Richard: Ich mag Isaacs Lösung. Wenn es sich um mehr als eine führende Null handelt, kopieren Sie weiterhin aus Zellen, die seine Formel enthalten, und fügen Sie die Werte nur in die ursprünglichen Zellen ein: Jedes Mal wird eine andere führende Null entfernt (falls vorhanden). Ivo Flipse vor 13 Jahren 0
6
Andreas Flueckiger

Die folgende Formel benötigt weder zusätzliche Zellen noch muss sie als Matrixformel eingegeben werden:

=RIGHT(A1,LEN(A1)-FIND(LEFT(SUBSTITUTE(A1&" ","0",""),1),A1&" ")+1)

Wenn für Zeichenfolgen wie 0oder eine einzelne Null zurückgegeben werden soll 00, kann folgende Formel verwendet werden:

=IF(A1="","",RIGHT(A1,LEN(A1)-FIND(LEFT(SUBSTITUTE(LEFT(A1,LEN(A1)-1)&" ","0",""),1),LEFT(A1,LEN(A1)-1)&" ")+1))
Diese Antwort funktioniert mit mehreren führenden Nullen und scheint die vollständigste und einfachste der hier genannten Antworten zu sein. Peter vor 9 Jahren 0
5
Lance Roberts

Dies ist ein schwieriges Problem bei einer Arbeitsblattfunktion. Das Folgende wird den Trick ausführen, aber nur, wenn die Nullen, die nicht führend sind, jeweils nur eins sind und es keine nachfolgenden Nullen und keine eingebetteten Leerzeichen gibt.

Er setzt alle Nullen durch Leerzeichen ein, schneidet sie ab (alle außer eingebetteten einzelnen Leerzeichen) und setzt dann die Nullen zurück.

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,"0"," "))," ","0")
Warum * nur dann, wenn die Nullen, die nicht führen, nur eine zu einem Zeitpunkt * sind? Und was ist mit `= CLEAN (SUBSTITUTE (TRIM (SUBSTITUTE (A1 & CHAR (9)," 0 "," ")," "," 0 "))` (in der Hoffnung, dass mit CLEAN Tabs entfernt werden; falls nicht; Verwenden Sie einen anderen Wert in "CHAR") Arjan vor 15 Jahren 0
@Arjan, ich habe TRIM getestet und es wurden eingebettete mehrere Leerzeichen durch ein einziges Leerzeichen ersetzt. Ich werde jedoch jetzt Ihre Informationen recherchieren und nachsehen, was ich finden kann. (Ich benutze 2003). Lance Roberts vor 15 Jahren 0
Die CLEAN-Funktion speichert die eingebetteten Leerzeichen, aber auch alle führenden Leerzeichen. Lance Roberts vor 15 Jahren 0
Ich habe CLEAN hinzugefügt, um den CHAR (9) zu entfernen, den ich hinzugefügt habe. mit CLEAN für den TRIMmed-Wert sollten keine führenden Leerzeichen verwendet werden ...? Arjan vor 15 Jahren 0
Ja, aber wenn CHAR (9) hinzugefügt wurde (zu jeder Null), fügte es sich auch zu den führenden Nullen hinzu. Ich sehe, dass Sie es nur zum Speichern der nachgestellten Nullen verwendet haben, und ich werde es in die Antwort aufnehmen. Lance Roberts vor 15 Jahren 0
Tatsächlich spart das nur eine Null am Ende, wenn es mehrere nachfolgende Nullen gibt, dann funktioniert das nicht. Lance Roberts vor 15 Jahren 0
Ja, Sie müssen TRIM für die Leerzeichen verwenden. Leider verfügt EXCEL über keine LTRIM-Funktion, mit der sich alles lösen lässt. Lance Roberts vor 15 Jahren 0
Übrigens: Ich habe CHAR (9) nicht zu * jeder * Null hinzugefügt, oder? Ich habe es nur angehängt, als ich noch dachte, dass TRIM nur führende und nachfolgende Leerzeichen beeinflussen würde. (Es wird also versucht, durch Nachstellen von CHAR (9) sicherzustellen, dass kein CHAR (32) -Ablauf folgt.) Arjan vor 15 Jahren 0
5
Kije

Diese Lösung funktioniert für Eingabewerte, die alle genau neun Zeichen umfassen.

Richten Sie zuerst eine feste zehnzeilige Tabelle ein, die die folgenden als Text formatierten Zahlen enthält.

000000000

000000001

000000010

000000100

000001000

000010000

000100000

001000000

010000000

100000000

Nehmen wir an, die Tabelle befindet sich in den Zellen A1 bis 'A10

Nehmen wir an, Ihr Eingabewert befindet sich in Zelle B1 und Ihre Ergebniszelle ist C1

Verwenden Sie die folgende Formel in Zelle C1

= RECHTS (B1, SPIEL (B1, $ A $ 1: $ A $ 10,1) -1)

Clever! Für den dritten Parameter in der Funktion "MATCH" gilt: * 1 = Finden Sie den größten Wert, der kleiner oder gleich Lookup_value ist. * Arjan vor 15 Jahren 0
4
Toc

Verwenden Sie für Daten in A1 die folgende Formel:

= RECHTS (A1, LEN (A1) -MAX ((FINDB (REPT ("0", REIHE (A $ 1: A $ 100)), A1 & "-" & REPT ("0", 100)) = 1) * REIHE (A $ 1: A $ 100)))

Eingabe mit Strg + Umschalt + Enter. Es funktioniert für Strings mit bis zu 100 Zeichen.

** Wow! ** Einige kleine Anmerkungen: Strg-Umschalt-Eingabe (Cmd-Umschalt-Return auf einem Mac) erstellt eine Array-Formel. Fügen Sie die Formel wie gewohnt ein, drücken Sie jedoch nicht die Eingabetaste, sondern drücken Sie die Tastenkombination Strg-Umschalttaste. Es wird dann zwischen geschweiften Klammern angezeigt. In OpenOffice.org auf einem Mac konnte ich die Formel für die nächsten Zeilen nicht nach unten ziehen (da dies dann eine Super-Array-Formel ergeben würde). Aber Kopieren und Einfügen funktioniert, also bin ich sicher, dass es auch eine Verknüpfung gibt. Und OpenOffice.org kennt FINDB nicht. Die Verwendung von FIND scheint ebenfalls zu funktionieren. Arjan vor 15 Jahren 0
Elegante Lösung! wilson vor 12 Jahren 0

Verwandte Probleme