Microsoft Excel

Herbers Excel/VBA-Archiv

8 stelligen Zahlenblock in Textfeld finden

Betrifft: 8 stelligen Zahlenblock in Textfeld finden von: Claus
Geschrieben am: 19.11.2014 19:53:11

Hallo Excelspezialisten,

sorry, ich will euch nicht verkohlen. Ich sehe fast genau mein Problem hier, und das ist wirklich totaler Zufall. Nur komme ich nicht ganz klar damit... Deshalb frage ich jetzt doch mit meinem vorbereiteten Text:

Gibt es eine Formel, die innerhalb einer Zelle, z. B. A10, den ersten Block mit 8 aufeinanderfolgenden Ziffern findet und diese ausgibt?

Oder geht das nur als Makro?

Bsp.:

Hans 12345678hugo gibt aus: 12345678
123456780123 hugo gibt aus: 12345678
Hans 1234567hugo gibt aus: leer oder Fehler
Hänsle123456780123 hugo gibt aus: 12345678

Vielen Dank und liebe Grüße, Claus

  

Betrifft: MatrixFormeln! ... von: Matthias L
Geschrieben am: 19.11.2014 20:44:32

Hallo

 ABC
2Stringmit Fehlerohne Fehler
3Hans 12345678hugo gibt aus1234567812345678
4123456780123 hugo gibt aus1234567812345678
5Hans 1234567hugo gibt aus#WERT! 
6Hänsle123456780123 hugo gibt aus1234567812345678
7Matthias 6598736598 L6598736565987365

Formeln der Tabelle
ZelleFormel
B3{=ABS(LINKS(WECHSELN(A3;LINKS(A3;VERGLEICH(WAHR;ISTZAHL(TEIL(A3;SPALTE(1:1);1)*1);0)-1);"");8))}
C3{=WENNFEHLER(ABS(LINKS(WECHSELN(A3;LINKS(A3;VERGLEICH(WAHR;ISTZAHL(TEIL(A3;SPALTE(1:1);1)*1);0)-1);"");8));"")}
B4{=ABS(LINKS(WECHSELN(A4;LINKS(A4;VERGLEICH(WAHR;ISTZAHL(TEIL(A4;SPALTE(2:2);1)*1);0)-1);"");8))}
C4{=WENNFEHLER(ABS(LINKS(WECHSELN(A4;LINKS(A4;VERGLEICH(WAHR;ISTZAHL(TEIL(A4;SPALTE(2:2);1)*1);0)-1);"");8));"")}
B5{=ABS(LINKS(WECHSELN(A5;LINKS(A5;VERGLEICH(WAHR;ISTZAHL(TEIL(A5;SPALTE(3:3);1)*1);0)-1);"");8))}
C5{=WENNFEHLER(ABS(LINKS(WECHSELN(A5;LINKS(A5;VERGLEICH(WAHR;ISTZAHL(TEIL(A5;SPALTE(3:3);1)*1);0)-1);"");8));"")}
B6{=ABS(LINKS(WECHSELN(A6;LINKS(A6;VERGLEICH(WAHR;ISTZAHL(TEIL(A6;SPALTE(4:4);1)*1);0)-1);"");8))}
C6{=WENNFEHLER(ABS(LINKS(WECHSELN(A6;LINKS(A6;VERGLEICH(WAHR;ISTZAHL(TEIL(A6;SPALTE(4:4);1)*1);0)-1);"");8));"")}
B7{=ABS(LINKS(WECHSELN(A7;LINKS(A7;VERGLEICH(WAHR;ISTZAHL(TEIL(A7;SPALTE(5:5);1)*1);0)-1);"");8))}
C7{=WENNFEHLER(ABS(LINKS(WECHSELN(A7;LINKS(A7;VERGLEICH(WAHR;ISTZAHL(TEIL(A7;SPALTE(5:5);1)*1);0)-1);"");8));"")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Matthias


  

Betrifft: AW: MatrixFormeln! ... von: Claus
Geschrieben am: 19.11.2014 21:09:43

Hallo Matthias,

wow! Mit den von mir gegebenen Beispielen klappt das super (perfekt auch der Hinweis wie man Matrixformel eingibt) Dankeschön.

Das einzige Problem: Mein Hirn. Ich stelle nämlich fest, dass ich jede Menge Fälle habe, wo vor dem 8-stelligen Ziffernblock auch noch Ziffern stehen, eben weniger als 8. Und bei denen soll die Formel den ersten (am weitesten links stehenden) mindestens 8-stelligen Block ausgeben. Sorry.

Bsp.:
A2 electronic housing 343394550
soll 34339455 ausgeben. (die 9. Stelle ist manchmal eine Null, manchmal fehlt sie, deshalb gehe ich auf 8 Stellen und hänge dann noch eine Null an, das sollte ich aber selber hinbekommen... ;-)

Ich fürchte, dass das jetzt komplett anders geht, sorry, wäre aber spitze wenn du mir nochmals weiterhelfen könntest. Danke schon mal.

Claus


  

Betrifft: AW: MatrixFormeln! ... von: Matthias L
Geschrieben am: 19.11.2014 21:23:08

Hallo

Was klappt da nicht mit:
electronic housing 343394550

 ABC
2electronic housing 3433945503433945534339455

Formeln der Tabelle
ZelleFormel
B2{=ABS(LINKS(WECHSELN(A2;LINKS(A2;VERGLEICH(WAHR;ISTZAHL(TEIL(A2;SPALTE($1:$1);1)*1);0)-1);"");8))}
C2{=WENNFEHLER(ABS(LINKS(WECHSELN(A2;LINKS(A2;VERGLEICH(WAHR;ISTZAHL(TEIL(A2;SPALTE($1:$1);1)*1);0)-1);"");8));"")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Matthias


  

Betrifft: AW: MatrixFormeln! ... von: Claus
Geschrieben am: 19.11.2014 21:45:58

die Ziffer davor, das A2

A1 pc board FR4, 1 ,5mm, Cu 70pm/70pm 343081370

Also ich muss nicht nach der ersten Ziffer suchen und dann nach dem 8-stelligen Block, sondern egal wie viele Ziffern davor stehen... dann den 8-stelligen Block


  

Betrifft: Boris bitte ... wenn Du Zeit hast von: Matthias L
Geschrieben am: 19.11.2014 21:53:23

Hallo

Boris ist im Forum anwesend.
Der kann das alles schneller und besser.

Ich hoffe er schaut mal hier rein
Das A2 hatte ich als Zelladresse interpretiert.

Ich versuch nochmal was, hoffe aber das Boris reinschaut.

Gruß Matthias


  

Betrifft: AW: Boris bitte ... wenn Du Zeit hast von: {Boris}
Geschrieben am: 19.11.2014 22:04:04

Hi zusammen,

ggfls. so:

{=--TEIL(A1;MIN(WENN(ISTZAHL(TEIL(A1;SPALTE(1:1);8)*1)*(TEIL(A1;SPALTE(1:1);1)<>" ");SPALTE(1:1)));8) }

VG, Boris


  

Betrifft: Danke Boris ... von: Matthias L
Geschrieben am: 19.11.2014 22:13:33

Hallo

Nun nur noch der WennFehler drum und alles ist gut

 AB
2StringDanke Boris
3Hans 12345678hugo gibt aus12345678
4123456780123 hugo gibt aus12345678
5Hans 1234567hugo gibt aus 
6Hänsle123456780123 hugo gibt aus12345678
7Matthias 6598736598 L65987365
8A1 pc board FR4, 1 ,5mm, Cu 70pm/70pm 34308137034308137

Formeln der Tabelle
ZelleFormel
B3{=WENNFEHLER(--TEIL(A3;MIN(WENN(ISTZAHL(TEIL(A3;SPALTE($1:$1);8)*1)*(TEIL(A3;SPALTE($1:$1);1)<>" ");SPALTE($1:$1)));8);"")}
B4{=WENNFEHLER(--TEIL(A4;MIN(WENN(ISTZAHL(TEIL(A4;SPALTE($1:$1);8)*1)*(TEIL(A4;SPALTE($1:$1);1)<>" ");SPALTE($1:$1)));8);"")}
B5{=WENNFEHLER(--TEIL(A5;MIN(WENN(ISTZAHL(TEIL(A5;SPALTE($1:$1);8)*1)*(TEIL(A5;SPALTE($1:$1);1)<>" ");SPALTE($1:$1)));8);"")}
B6{=WENNFEHLER(--TEIL(A6;MIN(WENN(ISTZAHL(TEIL(A6;SPALTE($1:$1);8)*1)*(TEIL(A6;SPALTE($1:$1);1)<>" ");SPALTE($1:$1)));8);"")}
B7{=WENNFEHLER(--TEIL(A7;MIN(WENN(ISTZAHL(TEIL(A7;SPALTE($1:$1);8)*1)*(TEIL(A7;SPALTE($1:$1);1)<>" ");SPALTE($1:$1)));8);"")}
B8{=WENNFEHLER(--TEIL(A8;MIN(WENN(ISTZAHL(TEIL(A8;SPALTE($1:$1);8)*1)*(TEIL(A8;SPALTE($1:$1);1)<>" ");SPALTE($1:$1)));8);"")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Danke Boris


  

Betrifft: Alles gut :-)) VG, Boris von: {Boris}
Geschrieben am: 19.11.2014 22:23:40

....


  

Betrifft: AW: Alles gut :-)) VG, Boris von: Claus
Geschrieben am: 20.11.2014 10:13:16

Hallo Boris, hallo Matthias,

sorry, hatte gestern Abend keine Zeit mehr, deshalb erst jetzt meine Reaktion. Diese ist: Supererfreut und begeistert! Klappt jetzt prima. Supervielen Dank an euch zwei!

Eine Kleinigkeit, die aber NICHT ausgemerzt werden muss weil es nicht stört:

Seltsam, "A3 resin thixotropic RAKU-PUR 21-2113-1/23 Rampf" führt zu "77800". Evtl. werden also noch Minus und Schrägstrich als Rechenzeichen interpretiert?

Wenn dahinter aber wieder eine Sachnummer (mein Blocks sind Sachnummern mit denen ich per Copy & paste weiterarbeite) kommt, passt es nämlich trotzdem, z. B. "U-PUR 21-2113-1 31510333 test" gibt die gewünschte 31510333 aus.

Liebe Grüße
Claus, der Begeisterte


  

Betrifft: 77800 von: Rudi Maintaire
Geschrieben am: 20.11.2014 11:28:34

Hallo,
der Teilstring 2113-1/2 wird durch die Multiplikation zum 2.1.2113, was der Zahl 77800 entspricht.

Gruß
Rudi


  

Betrifft: Danke Rudi :-) von: Matthias L
Geschrieben am: 20.11.2014 12:50:17

Hallo

Dann benutze Wechseln() zusätzlich.
Dort änderst Du das Minus-Zeichen in Raute

 AB
9A3 resin thixotropic RAKU-PUR 21-2113-1/23 Rampf 
10A3 resin thixotropic RAKU-PUR 21-2113-1 31510333 test31510333

Formeln der Tabelle
ZelleFormel
B9{=WENNFEHLER(--TEIL(WECHSELN(A9;"-";"#");MIN(WENN(ISTZAHL(TEIL(WECHSELN(A9;"-";"#");SPALTE($1:$1);8)*1)*(TEIL(WECHSELN(A9;"-";"#");SPALTE($1:$1);1)<>" ");SPALTE($1:$1)));8);"")}
B10{=WENNFEHLER(--TEIL(WECHSELN(A10;"-";"#");MIN(WENN(ISTZAHL(TEIL(WECHSELN(A10;"-";"#");SPALTE($1:$1);8)*1)*(TEIL(WECHSELN(A10;"-";"#");SPALTE($1:$1);1)<>" ");SPALTE($1:$1)));8);"")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Evtl kann man auch noch den Slash "/" gegen Raute tauschen.

Danke Rudi

Gruß Matthias


  

Betrifft: AW: Danke Rudi :-) von: Rudi Maintaire
Geschrieben am: 20.11.2014 13:09:50

Hallo,
ein ähnliches Problem gabs hier schon mal:
http://www.excelformeln.de/formeln.html?welcher=457
Deshalb bin ich drauf gekommen.

Gruß
Rudi


  

Betrifft: Ja, was soll denn beim 1.Wert rauskommen, ... von: Luc:-?
Geschrieben am: 20.11.2014 13:06:31

…Claus,
ein Fehlerwert bzw nichts? Und dass das Gewünschte beim 2.Wert rauskommt, glaube ich auch nicht, denn hier treten ebenfalls die von Rudi erwähnten Effekte auf, weshalb ich da -131510 erhalte.
Wenn du dagg meine UDF-haltige Fml aus dem von dir erwähnten BT entsprd anpasst, kommt tatsächlich die von dir gewünschte Zahl heraus, weil MaskOn alle Nicht-Ziffern entfernt. Außerdem liefert sie bei nicht passenden Ausgangswerten den sinnvollen Fehler #NV (und ist außerdem noch etwas kürzer):
{=--INDEX(TEIL(Splint(MaskOn(A1; "num"));1;8);VERGLEICH(8;LÄNGE(TEIL(Splint(MaskOn(A1; "num"));1;8)); 0)) }
Gruß, Luc :-?


  

Betrifft: AW: Ja, was soll denn beim 1.Wert rauskommen, ... von: Claus
Geschrieben am: 20.11.2014 13:19:32

Da ich ja deutlich schrub, "Eine Kleinigkeit, die aber NICHT ausgemerzt werden muss weil es nicht stört" denke ich, dass ihr grade in der sportlichen Wettkampfphase seid?

Also, das mit dem Datum ist ja der Hammer! Da zieh ich mal den nicht vorhandenen Hut, Rudi.

Sodele, die Formel mit wechseln hab ich jetzt eingebaut - das funktioniert.

Luc:-? , bei deiner Formel erhalte ich "#NAME?" - wahrscheinlich ist da Excel nicht komplett installiert oder so? Aber bitte sei mir nicht böse, da wird mir jetzt der Aufwand zu groß - weil die andere Formel ja für das was ich brauche funktioniert und ich die auch schon recht kurz finde.

Nochmals herzlichen Dank an euch Excel-Perfektionierer (und das ist wirklich als dickes Lob gemeint)
Claus


  

Betrifft: Nee, Claus, Xl ist schon komplett, nur hast du ... von: Luc:-?
Geschrieben am: 20.11.2014 14:54:09

…nicht beachtet, was in dem verlinkten Thread steht! In der dortigen Fml sind die UDFs unterstrichen, also auch Links (halt mal den Mauszeiger drauf!). Aber du kannst dich auch gern mit den paar mickrigen Textauswertungsfktt in Xl bescheiden… ;->
Luc :-?


  

Betrifft: AW: Nee, Claus, Xl ist schon komplett, nur hast du ... von: Claus
Geschrieben am: 20.11.2014 15:13:51

Hallo Luc:-?,

oha, da bin ich fast überfordert. UDFs sind user defined formulas? Also müsste ich mir die quasi holen, denke ich.

Falls du nichts dagegen hast würde ich mich dann doch lieber "auch gern mit den paar mickrigen Textauswertungsfktt in Xl bescheiden… ;-"... (Textauswertungsfunktionen in Excel... richtig entkürzt?)

offtopic:
Sehe ich es richtig, dass ihr euch hier gerne mal mit Verbesserungen eigentlich bereits funktionierender Lösungen überbietet und euch gegenseitig, natürlich im Sinne von "was sich liebt das neckt sich", mit entsprechenden Sticheleien aufzieht? Find ich klasse. Das Forum hier ist echt spitze und ich würde ja auch gerne mal jemandem helfen, bei entsprechend einfachen Fragen kann ich das auch. Es ist mir aber sehr selten gelungen weil dann meist jemand von den Spezialisten schon schneller war.

Grüßle Claus


  

Betrifft: Noch eine AW, ... von: Luc:-?
Geschrieben am: 20.11.2014 18:41:04

…Claus;
1. UDF ist die gängige Abk(ürzung) für user-defined function, also benutzer­definierte Funktion (→ Abk Fkt, plur Fktt), nicht Formel.
2. „Absolution“ war bereits vorweg erteilt worden und die Abk wurde von dir richtig interpretiert (s.a.u. 1)… ;-]
3. Es ist lt Regularium weder unerwünscht noch verboten (wie in manchem anderen Forum), aber das sind in diesem Fall auch 2 Paar Schuhe — die immer komplizierter gewordene reine Standard­Fml­Lösung (Abk Fml →Formel) steht hier einer unveränderten, relativ einfachen und leicht durchschaubaren Fml­Lösung mit UDF-Einsatz ggüber, die nicht jeder anwenden kann bzw will. Dann muss derjenige uU aber auch häufiger mit Anpassungsarbeiten rechnen, falls plötzlich auch Texte auftreten, die zuvor nicht berücksichtigt wurden, wie du hier ja auch eindrucksvoll demonstriert hast (ein Bsp dafür ist jetzt ebenfalls im oben verlinkten Thread zu finden!). ;-]
Luc :-?


  

Betrifft: AW: Noch eine AW, ... und noch ein Dank von: Claus
Geschrieben am: 21.11.2014 10:57:47

Hallo Luc:-?,

ja, da bin ich vollkommen bei dir. Es ist sicher kein Nachteil, wenn man verschiedene Ansätze erhält und dann entscheiden kann, was im anstehenden Fall geeigneter erscheint. Das zeichnet dieses Forum auch irgendwie aus. Wobei ich kein anderes Excel Forum kenne, weil ich dieses als Erstes kennen lernte und mir hier ja stets geholfen wird, also kein Bedarf für was Anderes.

Also nochmals vielen Dank an alle Beteiligten, diese Lösung ist implementiert und wird schätzungsweise etwa ein / zwei mal monatlich meine Arbeit erleichtern. Super.

Liebe Grüße Claus


 

Beiträge aus den Excel-Beispielen zum Thema "8 stelligen Zahlenblock in Textfeld finden"