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

7-stellige Zahl aus Excel-Zelle herausfiltern

Forumthread: 7-stellige Zahl aus Excel-Zelle herausfiltern

7-stellige Zahl aus Excel-Zelle herausfiltern
29.07.2024 15:05:57
Tim Uhing
Hallo zusammen,
ich habe eine umfassende Excel-Liste in der unter Verwendungszweck unterschiedliche Informationen (Text, Zahlen etc.) gepflegt sind. Ich bräuchte hieraus extrahiert die reine Rechnungsnummer, die immer mit dem Jahr ("24") und einer 5 anfängt, danach noch folgend 4 Stellen fortlaufend.
Also soll aus dieser Zelle:

Re-Nr. 2450909 / 28.05.2024 KD-Nr. 12104 Rechnungseingang 05.07., daher Skonto Skonto 2 Prozent -0,33 EUR EREF: CT-1-08.07.2024

lediglich die 2450909 herausgefiltert und in einer separaten Spalte ausgegeben werden.
Danke für eure Hilfe. Gruß Tim
Anzeige

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: 7-stellige Zahl aus Excel-Zelle herausfiltern
29.07.2024 15:26:15
daniel
HI

=LET(x;TEIL(A1;SEQUENZ(LÄNGE(A1));1);y;TEXTTEILEN(GLÄTTEN(TEXTKETTE(WENN(ISTZAHL(-x);x;" ")));" ");FILTER(y;LÄNGE(y)=7;""))

bin mir aber nicht ganz sicher, ob deine Excelversion das schon kann.

Gruß Daniel
Außer Konkurrenz …
29.07.2024 15:51:08
RPP63
Moin!
Betrifft nicht 2021 und wird in 365 momentan ausgerollt.
In Google Sheets und Libre Office Calc ist es auch implementiert.
Das erste Pattern "\d{7}" findet eine siebenstellige Zahl,
bei "245[0-9]{4}" müssen die ersten 3 Ziffern 245 lauten, gefolgt von 4 Ziffern.
 RST
2Re-Nr. 2450909 / 28.05.2024 KD-Nr. 1210424509092450909

ZelleFormel
S2=REGEXEXTRACT(R2;"\d{7}")
T2=REGEXEXTRACT(R2;"245[0-9]{4}")


[/off topic]
Gruß Ralf
Anzeige
Nachbau von RegExExtract in VBA
29.07.2024 17:44:09
Yal
So, jetzt weiß ich wieder, warum ich diese UDF "RegExExtract" benannt hatte :-)) 

Function RegExExtract(ByVal Target, Pattern As String, Optional Occurence As Integer = 1) As String

Dim R

Dim erg

    If TypeOf Target Is Range Then Target = Target.Cells(1).Value

    Set R = CreateObject("VBScript.RegExp")

    R.Pattern = Pattern

    R.Global= True

    On Error Resume Next

    Set erg = R.Execute(Target)

    RegExExtract = R.Execute(Target)(Occurence - 1)

End Function


245\d{4}
sollte auch funktionieren. Siehe https://regexr.com/843le

VG
Yal
Anzeige
AW: Nachbau von RegExExtract in VBA
29.07.2024 21:23:57
Yal
Oops, ein paar Debug-Zeilen waren noch drin.

hier die schlankere Version 

Function RegExExtrakt(ByVal Target, Pattern As String, Optional Occurence As Integer = 1) As String

Dim R

    If TypeOf Target Is Range Then Target = Target.Cells(1).Value

    Set R = CreateObject("VBScript.RegExp")

    R.Pattern = Pattern

    R.Global= True

    On Error Resume Next

    RegExExtrakt = R.Execute(Target)(Occurence - 1)

End Function


VG
Yal
Anzeige
AW: Nachbau von RegExExtract in VBA
29.07.2024 21:25:41
Onur
Aber wozu all der Aufwand, wenn eine simple Formel 
=TEIL(A1;SUCHEN("245";A1);7)

dafür reicht ? :)
AW: Nachbau von RegExExtract in VBA
30.07.2024 06:28:00
RPP63
Moin Onur!
Die simple TEIL()-Formel reicht natürlich für das einzige Beispiel aus der Threaderöffnung.
So ein Pattern macht aber durchaus Sinn:
 RST
3245 Äpfel aus RE-Nr. 2450909 Kd 12104245 Äpf2450909

ZelleFormel
S3=TEIL(R3;SUCHEN("245";R3);7)
T3=REGEXEXTRACT(R3;"245\d{4}")


Gruß Ralf
Anzeige
AW: Nachbau von RegExExtract in VBA
30.07.2024 09:15:34
Yal
... einfach weil es Spass macht :-)

Nein, es ist auch so, dass wir verschiedene Facetten vorschlagen und der Fragende wählt aus, was für Ihn passt.
Ausserdem gibt es auch Leute, die auf diesem Beitrag stossen und eventuell leicht abweichende Anforderungen haben. Idealerweise gibt es damit eine Antwort.

Was bei der Sache noch nicht geklärt ist, ist ob davor und danach immer eine Leerzeichen gibt. Da könnte man die 12541234 oder x12541234x auschliessen.

VG
Yal
Anzeige
AW: Nachbau von RegExExtract in VBA
30.07.2024 11:30:19
Onur
Nix für Ungut, liegt wohr daran, dass ich RegEx hasse. :)
RegEx ist wie ein viel zu gross und schwer geratenes schweizer Offiziersmesser - vieeel zu viele Funktionen hineingepackt, um "praktisch und überall dabei" zu sein.....
AW: 7-stellige Zahl aus Excel-Zelle herausfiltern
29.07.2024 17:38:00
Onur 
=TEIL(A1;SUCHEN("245";A1);7)
Anzeige
AW: im Normalfall dürfte es reichen ...
29.07.2024 15:36:46
neopa C
Hallo Tim,

... wenn Du folgende Formel (nach unten kopierbar) einsetzt( hab dazu lediglich Deine Vorgabetext verschieden umgestellt):
Arbeitsblatt mit dem Namen 'Tabelle4'
 AB
1Re-Nr. 2450909 / 28.05.2024 KD-Nr. 12104 Rechnungseingang 05.07., daher Skonto Skonto 2 Prozent -0,33 EUR EREF: CT-1-08.07.20242450909
228.05.2024 / Re-Nr. 2450909 / KD-Nr. 12104 Rechnungseingang 05.07., daher Skonto Skonto 2 Prozent -0,33 EUR EREF: CT-1-08.07.20252450909
3CT-1-08.07.2026 Re-Nr. 2450909 / 28.05.2024 KD-Nr. 12104 Rechnungseingang 05.07., daher Skonto Skonto 2 Prozent -0,33 EUR EREF2450909
42450909 Re-Nr / 28.05.2024 KD-Nr. 12104 Rechnungseingang 05.07., daher Skonto Skonto 2 Prozent -0,33 EUR EREF: CT-1-08.07.20242450909
528.05.2024 KD-Nr. 12104 Rechnungseingang 05.07., daher Skonto Skonto 2 Prozent -0,33 EUR EREF: CT-1-08.07.2024 Re-Nr. 24509092450909
6  

ZelleFormel
B1=AGGREGAT(14;6;--TEIL(WECHSELN(A1;".";"!");ZEILE(A$1:A999);8);1)
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


Gruß Werner
.. , - ...
Anzeige
AW: 7-stellige Zahl aus Excel-Zelle herausfiltern
29.07.2024 15:56:53
Oppawinni
wenn die betreffende Zelle A1 wäre, dann könnte es doch sowas:
=WERT(WENNFEHLER(TEIL(A1;FINDEN("245";A1);7);"0"))
tun, oder hab ich was übersehen?
AW: im Normalfall auch völlig ausreichend ...
29.07.2024 16:14:24
neopa C
Hallo ...(?)

... allerdings kann man wie auch bei meiner Formellösung Ausnahmetexte "konstruieren" wo sich ein ungewolltes Ergebnis ergibt.
Z.B.: "245 Teile Re-Nr. 2450909 / ..."

Gruß Werner
.. , - ...
Anzeige
AW: im Normalfall auch völlig ausreichend ...
29.07.2024 21:22:29
Oppawinni
Ich hab ja bewusst im Konjunktiv geschrieben "könnte".
Kommt halt drauf an, wie kritisch die Geschichte ist. Notfalls muss es halt vielleicht dann doch RegEx werden.
AW: z.B. mit einer Erweiterung meiner Formel ...
30.07.2024 14:20:41
neopa C
Hallo,

... sollte schon z.B so:
=AGGREGAT(14;6;--TEIL(WECHSELN(A1;".";"!");ZEILE(A$1:A$999);7)/(TEIL(WECHSELN(A1;".";"!");ZEILE(A$1:A$999);3)="245");1)
mit größerer Wahrscheinlichkeit das gesuchte Ergebnis erbringen.

Und mit wohl fast höchster Wahrscheinlichkeit folgende:
=AGGREGAT(14;6;--TEIL(WECHSELN(A1;".";"!");ZEILE(A$1:A$999);7)/(TEIL(WECHSELN(A1;".";"!");ZEILE(A$1:A$999);3)="245")/ISTFEHLER(--TEIL(WECHSELN(A1&" ";".";"!");ZEILE(A$8:A$999);1));1)

Wobei wenn mehrere derartige Zahlen im Textstring sind, kann man durch entsprechende Wahl des 1. Argument von AGGREGAT() auch noch nach der größten oder kleinsten entsprechenden Zahl suchen lassen.

Gruß Werner
.. , - ...
Anzeige

Forumthreads zu verwandten Themen

Wenn in Zelle folgender String, dann
mehmet

Hallo Forum,
In Spalte G4:G92 sind viele kodierte Zeichenfolgen.
Fest steht aber, dass diese zu erkennen sind, z.B.
in Zelle G4: METAR EKAH 052150Z AUTO 12005KT 9999NDV DZ OVC080/// 15/14 Q1005=
in Zelle G5: METAR ESSA 051940Z 20006KT CAVOK 11/10 Q1006 NOSIG=
und so weiter.....
06.09.2008 01:08:00
Thread-ID: 1007801
16 Beiträge
Wenn in Zelle folgende Zeichen, dann
mehmet

Hallo Forum,
in Spalte G4:G92 sind folge Codes:
in G4= TAF LSZH 090600Z 090716 VRB03KT 0200 FG VV001 BECMG 0709 CAVOK=
in G5= TAF DTTA 090600Z 090716 VRB03KT 0700 FG BKN013 BECMG 1012 07010KT 8000 SCT026=
in G6= TAF EPKT 090500Z 090615 24002KT 0500 FG NSC BECMG 0608 26005KT 9999...
09.09.2008 09:39:00
Thread-ID: 1008320
6 Beiträge
Anzeige
Wenn bistimmte Werte in zwei Zellen dann Zeile ..
Micha

Hallo zusammen,
habe folgendes Problem:
habe eine große Datentabelle (B1:O31407); in dieser sollen diejenigen Zeilen gelöscht werden
in denen sowohl in der Spalte B als auch in der Spalte H der Zellwert "1" ist.
steht in einer der besagten Zellen ein anderer Werte soll nichts...
14.05.2009 08:36:57
Thread-ID: 1074984
4 Beiträge
Anzeige
Wenn Dann Funktion mit zählen wenn
Christian

Hallo zusammen,
ich brauche eine Funktion, die es mir ermöglicht, in einem Datenblatt erst nach einem bestimmten Wert zu suchen und dann zum gefundenen Wert einen weiteren Wert zu suchen und diese dann in der Anzahl zusammen zu zählen.
Bisher dachte ich, ich komme so zu meinem Ziel:
...
07.08.2009 10:26:38
Thread-ID: 1094218
3 Beiträge
Nummer auf 8 Stellen erhöhen mit führender Zahl
Thorsten

Hallo,
habe folgende Datei hochgeladen:
https://www.herber.de/bbs/user/66598.xls
Folgende Bitte: In Spalte B hätte ich gerne eine Formel, welche die Kundennummer aus Spalte A mit einer führenden 5 auswirft und gleich auf 8 Stellen ausfüllt.
Vielen Dank für die Hilfe
Thorste...
15.12.2009 09:27:50
Thread-ID: 1123956
2 Beiträge
Anzeige
Wenn Jahreszahl übereinstimmt,dann, sonst...
Wolfgang

Hallo,
der nachfolgende Code teilt eine Summe ggfs. nach Jahren auf. Wie muss ich den Code ändern, damit eine Abfrage "eingebaut" wird, die überprüft, ob das Jahr -intB5j- mit der Jahreszahl in Tabelle "Daten", Zelle M1 übereinstimmt. Wenn ja, dann entsprechend weiter.
Wenn nein, dann n...
21.02.2010 08:29:01
Thread-ID: 1139974
6 Beiträge
Wenn in Zelle P Inhalt dann schreibe in Zelle X
Viktoria

Hallo Leute,
hab ne doofe Frage:
Also Ich hab in Zelle P einen Inhalt dann möchte ich, das mir das Makro den Inhalt überprüft, und anhand des Inhaltes in eine Zelle einen String setzt den ich vorgebe.
Bei meinem Lösungsweg ist das Problem, das das Makro komischerweise nicht am Ende...
13.04.2010 09:14:04
Thread-ID: 1151360
6 Beiträge
Wenn in Zelle dann
Viktoria

Hallo Leute,
stehe seit Wochen schon vor dem gleichen Problem.. und hab keine Ahnung wie ich es beheben soll... :(
Also in Spalte Q habe ich Abteilungsbezeichnungen. Dort setzte ich einen Filter und möchte jetzt das mir mein Makro in Spalte M nur die ersten beiden Chars kopiert und dies...
19.04.2010 13:09:51
Thread-ID: 1152553
4 Beiträge
Anzeige
Zahlen mit zwei Stellen
Björn

Ich denke mal für hier ist diese Frage eine etwas banale.
Aber ich komme nicht darauf.
Ich möchte eine Zahl im einstelligen Bereich mit einer Null vorangestellt haben.
Dies möchte ich im Benutzerdefinierten Format so abbilden dass z.B. KW02/11 dargestellt wird.
Ich habe bisher a...
06.12.2010 17:06:35
Thread-ID: 1190291
5 Beiträge
Wenn wert in Zelle dann Button
Rene

Hallo,
warum funktioniert die Auswahl der Buttons nicht mehr wenn Sie im Ttabellenblatt an eine andere Position gesetzt werden.
Die Datei https://www.herber.de/bbs/user/72744.xls wurde aus Datenschutzgründen gelöscht
Würde das Tool gern verwenden.
MfG René
19.12.2010 23:05:53
Thread-ID: 1192146
2 Beiträge
Wenn wert in Zelle dann Button
Rene

Hallo,
warum funktioniert die Auswahl der Buttons nicht mehr wenn Sie im Ttabellenblatt an eine andere Position gesetzt werden.
Die Datei https://www.herber.de/bbs/user/72744.xls wurde aus Datenschutzgründen gelöscht
Würde das Tool gern verwenden.
MfG René
20.12.2010 03:06:11
Thread-ID: 1192147
3 Beiträge
wenn wert in zahlenreihe vorhanden, dann
Sascha

einen schönen guten morgen an alle...
folgendes problem: (Diese Daten stehen in Spalte A, B, C untereinander, die nummern sind komma getrennt in einer zelle)
A--------B-------------C (Spalten)
Farbe--System1----System 2
Rot-----1,2,3,4
Grün---5,6-----------1,2,3...
05.05.2011 12:08:27
Thread-ID: 1213163
14 Beiträge

Beliebteste Forumthreads (12 Monate)

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