Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1684to1688
1684to1688
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Führende Nullen entfernen

Forumthread: Führende Nullen entfernen

Führende Nullen entfernen
15.04.2019 11:26:46
Peter
Hallo VBA-Helfer,
ich bekomme eine Datei aus einem Vorsystem und da enthalten die Datensätze teilweise führende Nullen in unterschiedlicher Form. Mit dem bestehenden Makro klappt das auch, außer für die Konstellation in Zeile 8 bzw. 9 im Beispiel. In Zeile 8 bzw. 9 kann die Anzahl der Nullen unterschiedlich sein, im Beispiel sind es 9 Nullen, es können aber auch weniger sein, dafür ist die Ziffernfolge dann länger (z.B.:000000999123456789)
Kann mir bitte jemand den Code dafür ergänzen.
Besten Dank im Voraus.
Grüße Peter
https://www.herber.de/bbs/user/129151.xlsm
Anzeige

24
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Führende Nullen entfernen
15.04.2019 11:44:40
Matthias
Hallo Peter
Hinweis!
Leg Dir mal eine Schaltfläche auf deine Tabelle und weise ihr Dein Makro zu.
Nun klicke mal mehrfach darauf!
Ich glaube nicht das das so gewollt ist, oder?
Ich lass den Beitrag offen.
Gruß Matthias
AW: Führende Nullen entfernen
15.04.2019 11:44:44
Daniel
Hi
bei einer variablen Anzahl von führenden Nullen könntest du die "0" durch das Leerzeichen ersetzen.
die Leerzeichen zu beginn des Textes kannst du dann mit LTRIM entfernen.
Dann wieder die Leerzeichen in "0" zurücktauschen.
For j = 2 To LZ
If ws.Cells(j, 1).Value = "YYYP" Or ws.Cells(j, 1).Value = "YYYN" Or ws.Cells(j, 1).Value =  _
"YYYM" Then
ws.Cells(j, 2).Value = "'" & Replace(LTrim(Replace(ws.Cells(j, 2), "0", " ")), " ", "0") _
End If
Next j
das ganze geht natürlich nicht so einfach, wenn die Zeichenfolgen im auch Leerzeichen enthalten können.
Dann wären zwei zusätzlichliche Replace-Vorgänge erforderlich, um zuerst die regulären Leerzeichen durch ein anderes Zeichen auszutauschen und dann wieder zurück (sonst werden auch die regulären Leerzeichen in "0" gewandelt)
das "'" & dient ist dazu da, dass die Werte Texte bleiben, auch wenn sie wie Zahlen aussehen (Excel würde sonst diese WErte in Zahlen wandeln)
Gruß Daniel
Anzeige
AW: Führende Nullen entfernen
15.04.2019 12:06:13
Peter
Hallo Matthias, ja mehrfach geht nicht, das ist mir klar. Ist aber ok, da nur ich selbst diese Daten aufbereite. Trotzdem besten Dank für Deinen Hinweis.
@ Daniel: ich habe Deinen Code etwas modifiziert in mein Makro eingebaut und es funktioniert.
Besten Dank auch an Dich.
Gruß Peter
Anzeige
AW: Führende Nullen entfernen
15.04.2019 12:05:26
UweD
Hallo
so?
Sub Nullen()

    Dim j As Integer
    Dim LZ As Integer
    Dim ws As Worksheet, TMP
    
    Set ws = ActiveSheet
    LZ = ws.Cells(Rows.Count, 1).End(xlUp).Row
    For j = 2 To LZ
        If ws.Cells(j, 1).Value = "YYYP" Or ws.Cells(j, 1).Value = "YYYN" Or ws.Cells(j, 1).Value = "YYYM" Then
            TMP = ws.Cells(j, 2).Value
            If InStr(TMP, " ") > 0 Then
                TMP = Split(TMP, " ")
                ws.Cells(j, 2).Offset(0, 1).Value = CLng(TMP(0)) & TMP(1)
            Else
                ws.Cells(j, 2).Offset(0, 1).Value = CLng(TMP)
            End If
        End If
    Next j
    
End Sub

LG UweD
Anzeige
AW: Führende Nullen entfernen
15.04.2019 12:07:46
UweD
Das Leerzeichen fehlt noch...
ws.Cells(j, 2).Offset(0, 1).Value = CLng(TMP(0)) & " " & TMP(1)
Ihr seid alle Klasse !!!
15.04.2019 13:32:25
Peter
Hallo Uwe, Dein Ansatz funktioniert auch, kann ich an anderer Stelle sehr gut gebrauchen.
Auch Dir besten Dank.
Gruß Peter
AW: Führende Nullen entfernen
15.04.2019 14:02:11
Günther
Moin Peter,
das sollte sich auch mit Power Query lösen lassen.
Da ich aber keine VBA-verdächtigen Files aus dem Netz lade, kann ich es nicht prüfen ...
Gruß
Günther
Anzeige
AW: Führende Nullen entfernen
15.04.2019 14:30:59
Daniel
Hi Günther
naja, ein Paar Texte die mit "0" anfangen, solltest du dir auch selber hinschreiben können.
aber als Service für dich:
https://www.herber.de/bbs/user/129155.xlsx
dafür hätte dann aber gerne eine nachvollziehbare Beschreibung der anzuklickenden Menüpunkte, um das Ergebnis zu erhalten.
Danke, Daniel
Anzeige
AW: Führende Nullen entfernen
15.04.2019 14:52:02
Günther
Moin Daniel,
die Zeiten, wo ich abgetippt habe und dann oft genug auf der Nase gelandet bin, die sind vorbei ...
Und das beste Beispiel ist (wieder einmal) de aktuelle Thread. Der einzig offen sichtbare Wert ist "000000999123456789" und da ist es ja auch mit plain Excel kein Problem. ;-) Hier sind ja in der richtigen Welt (Mappe) so nette "Kleinigkeiten" wie "000000000727239210 IU19086" drin, also mit alphanumerischen Zeichen. - Wie gesagt, "unverhofft kommt oft ..."
Spalte Datensatz duplizieren
Kopie -> Spalte teilen, Ziffer zu nicht Ziffer
Kopie.1 -> Datentyp Ganze Zahl
Kopie.1 & Kopie.2 -> Spalten zusammenführen
F R E U E N
Muster-File auf Wunsch in meiner DE-Cloud bis zum 25.04.2019.
Gruß
Günther
Anzeige
AW: Führende Nullen entfernen
15.04.2019 15:22:06
Daniel
Hi
ich kann, wenn dich versuche deine Anleitung auf die Beispieldatei anzuwenden, bei "Spalte teilen" kein "Ziffer zu Ziffer" auswählen, sondern nur "nach Trennzeichen" oder "anzahl Zeichen".
"Ziffer zu nichtziffer" wird da nicht angeboten.
ein eindeutiges Trennzeichen gibt es ja nicht. Die Anzahl der Nullen ist ja auch unterschiedlich.
kannst du da bitte nochmal draufschauen.
Gruß Daniel
Anzeige
Kopie -> Spalte teilen, Ziffer zu nicht Ziffer !!
15.04.2019 15:28:55
robert
würde ich ja gerne, wenn PQ mir diese
15.04.2019 15:46:27
Daniel
Option anbieten würde.
ich sehe nur das. In der Haupmenüfunktion Spalte teilen bekomme ich die gleichen Optionen zur Auswahl.
wo finde ich "Ziffer zu nicht Ziffer"
Userbild
Gruß Daniel
Anzeige
AW: würde ich ja gerne, wenn PQ mir diese
15.04.2019 16:22:38
Günther
Stimmt,
ich habe mal eben bei meiner 2016er Version nachgesehen und siehe da ... Wie ich sehe, sehe ich nichts. Scheinbar ist das erst ab der 2019er Version integriert. Hier sind einfach einmal die "echten" 2019er gefragt, ob sie das erweiterte Menü sehen. Es kann nämlich sein, dass MS auf irgendwelchen Umwegen "merkt", dass ich auch noch eine 365er Version habe; da wird ja fleißig geupdated (welch Wortungetüm).
Der M-Code, der hinter dieser Funktionalität steckt, scheint nicht in der 16er Version laufen ...
Habe das File eben mal rüber kopiert, in den Quelldaten eine Änderung vorgenommen und dann ein Update gemacht. Mecker ... :-(
Wieder etwas schlauer geworden; mal sehen, wann/ob MS das 2016er (und evtl. auch früher) updatet ...
Gruß
Günther
Anzeige
Spalte teilen anklicken... Gruß
15.04.2019 16:47:57
robert
Sorry, Daniel-hast Du ja gemacht...dann ?
15.04.2019 16:53:03
robert
ja robert, was dann, was dann?
15.04.2019 17:32:29
Daniel
(womit sich mein eindruck, den ich von dir habe, mal wieder bestätigt findet)
Anzeige
was bedeutet ein Fragezeichen ? - Du ........
15.04.2019 18:00:57
robert
..lass mich lieber nicht schreiben, welchen Eindruck ich von Dir habe.......
AW: was bedeutet ein Fragezeichen ? - Du ........
15.04.2019 18:06:09
Daniel
Robert, schreib mal lieber ein paar inhalte statt nichtssagende Betreffzeilen.
Günter scheint plan von der Sache zu haben, also würde dich bitten, dich einfach rauszuhalten, wenn du nichst hilfreiches beizutragen hast.
Danke
Anzeige
dann lies mal alles,nicht nur Betreff..
15.04.2019 18:54:47
robert
..und Günther - ja -ist ein guter Mann bei Power Query, aber haben seine
Beiträge dir geholfen?
Mach doch mal was selber-zB.Neuinstallation des AddIn oder googeln..
Und wem ich was und wie schreibe kannst DU mir nicht vorgeben-merk dir das.
mehr als deine.
15.04.2019 19:01:03
Daniel
...
OT: Featurenachrüstung in ServicePacks ...
15.04.2019 19:29:58
lupo1
... gab es seit jeher selten. Es ging meist nur um Fehlerbehandlung - Ausnahmen mögen diese Regel bestätigen.
Beispiel:
Da es xl2019 vermutlich im Oktober 2018 schon gab, wird sogar xl2019 nicht die {}-Erkennung in dem Umfange haben bzw. bekommen, die genau dann in xl365 eingeführt wurde.
Technisch sind einige Nachrüstungen vermutlich auch nicht möglich, da ein Programm wie Excel im Code dafür zuviele Abhängigkeiten haben könnte.
Daher sind die heutigen Kauf-Versionen nur noch "Freezes" eines Abo-Zeitpunkts, z.B. xl2019 im August 2018 (gemutmaßt).
Anzeige
Korrektur...
15.04.2019 15:38:15
robert
Nach Wechsel von Ziffer zu Nicht-Ziffer
AW: Führende Nullen entfernen
15.04.2019 15:41:34
Günther
Wenn du Excel 2010/13 hast kann es sein, dass du nicht das neueste Update hast. Ich habe diese Möglichkeit auch erst vor kurzer Zeit entdeckt (2016/19 mit automatischem Update). Vielleicht hilft bei den Add-In - Versionen ein Update?
Bilde dich hier: ;-) Userbild
Gruß
Günther
Anzeige
AW: Führende Nullen entfernen
15.04.2019 18:03:15
Daniel
Hi
das ist meine Version mit Excel 2010
Version: 2.59.5135.201 32 Bit
da kommen nur die ersten beiden Optionen, die restlichen nicht.
Gruß Daniel
;

Forumthreads zu verwandten Themen

">" vor einer Zahl entfernen + sverweis
Roberto

Hallo,
ich habe in der Spalte A Zahlen vom 0 bis 55 mit oder ohne das Zeichen "Größer als". Nun möchte ich die Zahlen in der Spalte B mit Hilfe von SVERWEIS gruppieren. Sprich, wenn in der Spalte A 0,1,18,23,45 stehen, steht in der entsprechenden Zelle der Spalte B 0,1,2,3,5 (siehe unten)....
20.06.2010 14:19:31
Thread-ID: 1162955
6 Beiträge
VBA Autofilter und führende Nullen
Joachim

Wer hat eine Lösung?
Eine Spalte ist als Texte formatierte Spalte enthält 4-ziffrige Zahlen (0001 bis 9999).
Es sollen nun alle Zeilen gefiltert werden die nicht ="0010"sind. Selection.AutoFilter Field:=3, Criteria1:="0001", Operator:=xlAnd
bzw. als flexible Variante Zahl="0001" Se...
03.11.2010 23:59:34
Thread-ID: 1185073
5 Beiträge
Zahl mit "-" als Vorzeichen als 0 werten
Ralph Bühler

Hallo, ist es bei Excel möglich eine Zahl mir einem "-" als Vorzeichen als 0 zu werten?
Also ich meine, sobald die Zahl ins minus fällt, wird sie als 0 gewertet. Falls es was hilft, meine Formel
sieht zZ so aus: =20+SUMMEWENN(D41:D45;"x";O51:O55)-D13Danke schon mal im VorrausMfG Euer Ralp...
05.06.2002 00:05:55
Thread-ID: 125927
3 Beiträge
0 oder 00 vor einer Zahl
Jäger

Hallo liebe Excelgemeinde,
ich versuche ein txt-File in Excel zu importieren.
In Spate D stehen "Zahlen", die aber mit 0 oder 00 beginnen.
Excel lässt die 0 weg was ja auch richtig ist es gibt keine Zahl die mit 0 beginnt.
Ich habe versucht die Spalte als Text zu formatieren...
27.06.2012 20:43:09
Thread-ID: 1269230
9 Beiträge
führende Null in Formel
reinhardt

Guten Morgen zusammen,
bei www.excelformeln.de/formeln.html?welcher=206 stehen schöne Formeln:
In einer Datei mit z.B. den Tabellen 1 bis 35 soll von der Tabelle5 bis zur Tabelle25
jeweils die Zelle A2 aufaddiert werden.
=Tabelle5!A2+Tabelle6!A2+Tabelle7!A2+ ..... usw. ist ja se...
22.03.2013 07:52:59
Thread-ID: 1305427
4 Beiträge
Jahreszahl vor 1900
reiner

hallo Leute,
mit der Funktion JAHR lässt sich problemlos aus einem Datum die Jahreszahl ermitteln, aber nur wenn die Jahres gleich oder größer ale 1900ß ist.
Hat jemand einen Tipp wie aus einem Datum gleich oder kleiner 31.12.1899 die Jahreszahl ermittelt werden kann?
mfg
reiner...
05.07.2013 17:22:50
Thread-ID: 1321334
9 Beiträge
Zahlen am Anfang eines Texts entfernen
Christian

Hallo an alle,
ich wäre froh, wenn jemandem von euch die passende Formel einfällt.
Meine Spalte A der Tabelle beinhaltet 5341 Zellen, in denen so etwas steht wie
1002 Bürstadt 6 Hessen VRN
oder
1 Aachen Hbf 2 Nordrhein-Westfalen NVR
oder
105 Altenkirchen (Westerw) 6...
15.10.2013 14:36:21
Thread-ID: 1334455
4 Beiträge
am Anfang und am Ende die Leerzeichen entfernen
Selma

Hallo Leute,
wie kann ich bitte per VBA in aktivem Arbeitsblatt am Anfang und am Ende die Leerzeichen entfernen.
Es soll an gesamten Arbeitsblatt angewendet werden!
Gruß,
Selma
27.12.2013 19:19:05
Thread-ID: 1343589
7 Beiträge
Wenn 0 am Anfang einer Zahlenfolge, dann löschen
Justin

Hallöchen allerseits....
ich stoße an meine Grenzen:
ich teile durch ein Makro eine Vertragsnummer, zB "AB 0123456789" wird zu AB und 0123456789 in 2 nebeneinanderliegenden Zellen.
Die Zahlenfolge ist nicht immer gleich lang. Am Anfang können jetzt aber zwischen 1-3 Nullen sein, mu...
08.07.2016 09:38:23
Thread-ID: 1503272
4 Beiträge
Länge von Zahlen mit vorangestellter Null
Zahlen

Moin zusammen,
ich benötige mal wieder Eure Hilfe Bei folgendem Problem:
Ich möchte die Länge (Anzahl von Zeichen) von Zellen einer Spalte ermitteln. Diese Spalte enthällt auch Zahlen mit ein bis drei vorangestellten Nullen, die mitgezählt werden sollen.
Beispiel:
00025 Länge =...
30.10.2016 14:38:33
Thread-ID: 1522290
11 Beiträge
Anzeige
Zahl mit vorangestellten Nullen als String
Constantin

Hallo,
ich habe eine Mappe mit einem Listenfeld, welches drei Werte zur Auswahl hat (ABC, 004711 und H089). Nun möchte ich bei Aktivierung der Tabelle1, dass - wenn ABC voreingetragen ist, dieser Inhalt durch 004711 ersetzt wird. Leider waren meine Versuche erfolglos bisher. Es wurde immer...
23.03.2017 20:17:59
Thread-ID: 1549118
7 Beiträge
Excel 2013 Diagrammschaltflächen entfernen
xl-chen

Hallo Forum!
Gibt es eine Möglichkeit, die neuen Diagrammschaltflächen am rechten oberen Rand eines Diagramms (über die z.B. Diagrammelemente einfach hinzugefügt oder entfernt werden können), dauerhaft abzuschalten? Ich weiß, dass sie verschwinden, wenn das Diagramm nicht mehr ausgewählt is...
12.10.2017 09:51:03
Thread-ID: 1585455
3 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Führende Nullen in Excel Entfernen

Schritt-für-Schritt-Anleitung

Um führende Nullen in Excel zu entfernen, kannst du verschiedene Methoden nutzen. Hier sind einige der gängigsten:

  1. Mit einer Formel: Du kannst die folgende Formel verwenden, um führende Nullen zu entfernen:

    =WERT(A1)

    Diese Formel wandelt den Text in eine Zahl um und entfernt dabei alle führenden Nullen.

  2. Mit VBA: Du kannst ein einfaches VBA-Makro verwenden. Füge folgenden Code in ein Modul ein:

    Sub NullenEntfernen()
   Dim Zelle As Range
   For Each Zelle In Selection
       Zelle.Value = Val(Zelle.Value)
   Next Zelle
End Sub

    Damit werden die führenden Nullen der ausgewählten Zellen entfernt.

  3. Mit Power Query:

    • Lade deine Daten in Power Query.
    • Wähle die Spalte mit den führenden Nullen aus.
    • Klicke auf "Transformieren" und dann auf "Datentyp" und wähle "Ganze Zahl".
    • Klicke auf "Schließen & Laden", um die Änderungen zu übernehmen.

Häufige Fehler und Lösungen

  • Excel entfernt führende 0 nicht: Stelle sicher, dass die Zellen als Text formatiert sind, bevor du die Werte änderst. Du kannst dies tun, indem du die Zellen auswählst und unter "Start" den Formattyp auf "Text" änderst.

  • Formeln geben Fehler zurück: Überprüfe, ob die Zellen, auf die du in deiner Formel verweist, tatsächlich Zahlen oder Text sind. Möglicherweise musst du die Zellen zuerst mit WERT() konvertieren.

  • VBA funktioniert nicht: Stelle sicher, dass du das Makro in einer Excel-Arbeitsmappe speicherst, die Makros zulässt.

Alternative Methoden

Es gibt auch andere Ansätze, um führende Nullen zu entfernen:

  1. Excel-Funktion: Verwende die Formel =TEXT(A1, "0"), um die führenden Nullen zu entfernen, ohne die Zahl zu verändern.

  2. Daten importieren: Wenn du Daten aus einem anderen System importierst, achte darauf, dass sie im richtigen Format vorliegen, um das Problem der führenden Nullen von vornherein zu vermeiden.

  3. Power BI: Wenn du mit Power BI arbeitest, kannst du die gleiche Power Query Methode anwenden, um führende Nullen zu entfernen.

Praktische Beispiele

  • Beispiel 1: Angenommen, du hast die folgende Liste in Spalte A:

    A
000123
0456
0007890

    Mit der Formel =WERT(A1) in B1 wird das Ergebnis 123, 456 und 7890 sein.

  • Beispiel 2: Verwende das VBA-Makro, um eine gesamte Spalte auszuwählen und die führenden Nullen auf einmal zu entfernen.

Tipps für Profis

  • Vermeide Datenverlust: Wenn du führende Nullen entfernen möchtest, aber einige Daten als Text behalten möchtest, erwäge, die Zellen in eine Hilfsspalte zu kopieren und die ursprünglichen zu ändern.

  • Regelmäßige Backups: Stelle sicher, dass du regelmäßig Backups deiner Daten machst, bevor du Massenänderungen vornimmst.

  • Nutzung von Power Query: Wenn du häufig Daten bearbeitest, die führende Nullen enthalten, ist Power Query eine leistungsstarke Funktion, die dir viel Zeit sparen kann.

FAQ: Häufige Fragen

1. Wie kann ich führende Nullen in Excel mit einer Formel entfernen? Du kannst die Formel =WERT(A1) verwenden, um führende Nullen zu entfernen und die Zelle in eine Zahl zu konvertieren.

2. Kann ich VBA verwenden, um führende Nullen zu entfernen? Ja, du kannst ein VBA-Makro erstellen, das die führenden Nullen in einer Auswahl von Zellen entfernt, indem du den Wert jeder Zelle auf den numerischen Wert setzt.

3. Was ist der Unterschied zwischen Excel und Power Query in Bezug auf das Entfernen von Nullen? Excel bietet direkte Formeln und VBA zur Bearbeitung, während Power Query eine benutzerfreundliche Oberfläche hat, um Daten zu transformieren und zu laden, was bei größeren Datensätzen effizienter ist.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige