Office: Zahl aus Text extrahieren

Hallo,

es geht um Folgendes, im muss irgendwie an die Zahl Links bzw rechts kommen.
Dies kann ich immerhin getrennt angehen, Linke Zahl Spalte B, rechte Zahl Spalte C

Bisher bin ich immer so vorgegangen.

=SUBSTITUTE(MID($A1;FIND("-";$A1)-2;2);" ";"")*1


So klappt es mit 1 und zwei stellige Zahlen soweit super, nun kommen aber 3 bis 4 stellige Zahlen hinzu.

2 Stellige Zahlen kann ich direkt uebernehmen, bei 3 bis 4 stellige Zahlen kommt es darauf an ob diese mit xx80 enden oder nicht und muss diese weiter verarbeiten.
Dies dann zu bewerkstelligen ist soweit kein Problem, dies bekomme ich hin.

Aber wie kann ich zu Anfang am sinvollsten an die Zahl kommen.
Meine obere Formel Spuckt fehler aus wenn ich dies auf bis zu 4 Stellen erweitere, da auch Buchstaben enthalten seien koennen, falls die Zahl eben nur 1 oder 2 Stellig ist.

Meist wie im Beispiel unten gibt es nur 1 leerzeichen bevor die Zahl beginnt.

Also koennte ich sagen...

=FIND("-";$A1)-FIND(" ";$A1)-1

...und weiss dann wieviele Ziffern die Zahl hat, bis zwei und mehr als 2. Soweit auch gut.
Nur, sollte doch mal ein weiteres leerzeichen hinzu kommen, wird es zu Fehler kommen.

Code:

MEINE FRAGE:

Um dies zu vermeiden lautet nun meine Frage, mit ... =FIND("-";$A1) finde ich den Bindestrich zwischen den Zahlen, also an welcher Stelle sich dieser befindet.
Wie kann ich nun sagen, von dieser stelle an "rueckwaerts" suche mir das erste vorkommende leerzeichen. Also falls es dazu einen Weg geben sollte, wuerde es meine Formel ungemein vereinfachen.

Vielen Dank
Flo

:)

 

Hallo Flo,

Nach deiner Beschreibung kommt das "-" jeweils nur einmal im Text vor.
Nach deinen Beispieldaten und der Beschreibung steht vor und hinter den Ziffern um das "-" jeweils ein Leerzeichen.

Wenn das so richtig ist kannst du zB folgendes machen:

Den Text mit der SPLIT-Funktion in ein Variant-Array schreiben und dann mit einer Schleife über alle Elemente des Arrays aus dem Element mit dem "-" mit LEFT und RIGHT die Zahlen lesen.

 

Halo Oge,

Erst einmal richtig, bin jetzt erst einmal dran nur diese Variante durchzugehen, kann aber min. 2 x, wie ich auch gesehen habe 3 x vorkommen...

Das ist richtig. Es gibt aber auch zahlen mit 3 bloecke also 1234-5678-9012 Aber diese stehen immer Einzeln fuer sich, also am Anfang wie auch am Ende steht ein Leerzeichen.

Damit meinst du aber einen Weg ueber VBA? Dachte es gibt eine einfache Formel...

Gerade haette ich es FAST geschafft. Also zu erkennen ob 1,2,3 oder 4 Stellig.

SUBSTITUTE(MID($A1;FIND("-";$A1)-3;3);" ";"")*1

 

schonmal hier geschaut?

 

Hallo Scorefun,

also diese uebersichts Seite habe ich nicht direkt gefunden, aber ueber Google bin ich schon auf direkte Seiten von excelformeln gekommen.

Nur hier geht es nicht um alle Zahen.
Den Zahlen koennen mehrmals im Text vor kommen, die die mich interessieren sind mit einem - gekenzeichnet.

Darum auch schon mal der Weg ueber ... =FIND("-";A1)

nur das dann nach links weg es mal 1 bis 4 Stellen werden kann, und dies macht mir Probleme.


ABCD 1234-5678

=SUBSTITUTE(MID($P11;FIND("-";$P11)-4;4);" ";"") Findet mir 3 oder 4 Stellen. bei 1 oder 2 kommt aber der vorherige Text hinzu also D oder CD.
Gibt es nicht eine Funktion welche sagt. Schmeiss alle Buchstaben raus?

 

Hallo,

Mit etwas VBA ist das ein Kinderspiel.
Schau es dir mal an und überlege ob du dich damit anfreunden kannst.
Tabelle1

	A	B	C	D	E	F
1	xxxx 1230-1280 OK	1230	1280
2	xxxx 20-4 789 text98765OK	20	4	789	98765
3	xxxx 7-7 OK	7	7
4	xxxx 780-2340 OK	780	2340
5	xxx xxx 1230-1280 OK	1230	1280
6	asd

Zelle	Formel
B1	=machs($A1;SPALTE(A1))

Excel Tabellen im Web darstellen >>

 

Hallo ransi,

danke fuer dein Script. Habe dies gleich mal Copy&Paste in mein jetziges Modul1 mit eingefuegt und auch eine neue Tabelle erstellt.
Aber auch da will es nicht klappen, nur #VALUE!.

Ich habe alles direkt so uebernommen. Gab ja nichts anzupassen.
Auch deine Test Daten.

Wo kann ein Fehler sein?

 

Hallo,

Kann ich nicht nachvollziehen.
Dies ist meine Testdatei.
Probier mal...

ransi

 

Danke, mit deinem Beispiel hat es Klick gemacht. Habe deine Formel 1:1 uebernommen.
Aus...

=machs($A1;SPALTE(A1))
haette ich aber ...
=machs(A1;COLUMN(A1))
machen muessen.

Nun funktioniert es. Danke dir erst einmal. Nun muss ich nur mal schauen wie ich das am besten mit umsetzen kann.

Kannst du mir aber noch verraten warum ich 2 Angaben machen muss, also einaml die Zelle, und einmal die Spalte selbst?

 

Die Spalte ist keine Spalte!

Sondern ein Index, der höher wird, je weiter Du die Funktion in der Tabelle nach rechts kopierst. In der n-ten Spalte ermittelt Dir die Funktion das n-te Auftreten einer Zahl.

 

HAllo,

Das Regex sucht in dem String die einzelnen ZAhlenfolgen und legt sie in einem Array ab.
Mit der zweiten Angabe sagst du welche Zahlenfolge aus dem Array ausgegeben werden soll.
Aus Spalte(A1) wird beim kopieren nach rechts -->Spalte(B1) -->Spalte(C1), usw.
Also 1, 2, 3, usw....

ransi

 

Hallo Floh,
da ich bisher verstanden habe, dass nur Zahlen direkt um "-" ausgewiesen werden sollen hier ein Beispiel mit SPLIT:

Code:

In der Funktion tuwat kann die Position anhand der Nummer (0,1,...) vorgegeben werden, in tuwat2 anhand der Positionskennung ("v","h").

 

Hallo und Danke euch allen.

Mit eurer hilfe konnte ich nun die Formel fertigstellen.
Und dies wirklich wesentlich einfacher.

Nun habe ich das auch mit dem Index Verstanden, also das sich dies auf das Array bezieht und keine reele Spalten gemeint sind.

Ich haette aber nicht gedacht, das dies wirklich nur ueber VBA zu realisieren ist.
Haette eher vermutet, das es schon eine Funktion gibt, die einfach alle Buchstaben oder Zahlen aus einem String entfernt.
Dem scheint es aber nicht zu sein und nun weiss ich auch, das man in vba auch mit RegEx arbeiten kann.

Man lernt ja nie aus.

Und vielen vielen Dank euch allen, dies war nun soweit mein letzter offene Punkt, welches ich gerade eben ab arbeiten konnte.

Viele Gruesse
Flo

*EDIT*
Testlauf nun durch... Dies was es machen sollte, macht es nun Perfekt. ABER ...
Erst jetzt habe ich gesehen das es solche faelle auch gibt.

Code:

Dort klappt die RegEx Methode nicht mehr, da er daraus schon, bei dem ersten und zweiten Beispiel, die 2 extrahiert und diese an erster Stelle ins array hinterlegt.
Klar koennte ich nun sagen stat A1 nehme B2 und B3, das Problem ist, das dies eine allg. Formel seien soll fuer mehrere hunderte Zellen.

Ist es evtl. moeglich die Regex Methode soweit anzupassen, das dieser sich nur auf den zahlenblock 123-456-789 bezieht, also alles zusammenhaengende mit einem Minus als Trenner einbezieht und den Rest ausser acht laest?
*EDIT*

 

So, nun muss ich diesen Thread doch noch mal auf machen.
Bin doch der Loesung sehr nahe gekommen...

Habe die Pattern folgend abgeaendert...

.Pattern = "[^a-zA-Z]d+"

Fuer die erste Zahl klappt es nun Super, nun hat aber die Zweite das - mit angehaengt... Nun koennte ich meine Formel komplett durch gehen nd ueberall sagen entferne das erste Zeichen, was nicht schoen waere.

Nun wollte ich in die Runde Fragen, ob man dieses Script noch ein klein wenig anpassen koennte, so das er eben das MINUS nicht an die Zahl mit dran haengt, dann ist es wirklich fuer meinen Fall Perfekt.

*EDIT*
Mit folgendem klappt es schon besser, warum auch immer hat er dann aber Probleme mit ein stelligen Zahlen.
[^D]d+
*EDIT*

*EDIT 2*
Das beste Ergebnis habe ich nun folgendem Patter erreicht...
([^D]d+|sd+)

Hier mal ein ausschnitt. Er findet alles, bis auf eben einstellige Zahlen (am ende) also nach dem -
Code:

 

Halloechen,

so, nach wirklich vielem Probieren, habe ich fuer meine Aufgabenstellung eine Loesung gefunden...

Das Problem bisher, das ganz einfache "/d+" hat mir zu viele Treffer gefunden.
Schnell bin ich dann auf Folgenden Pattern gestossen "[^a-zA-Z]/d+"
Hier war das Problem, das die 2te/3te entnommene Zahl, immer das "-" vorangestellt hatte.

Daraufhin habe ich mit den Pattern experimentiert, siehe unten.
Keines hat wirklich brauchbare Ergebnisse geliefert. Nun bin ich zum Ausgangspunkt zurueck, und habe das Script um ein einfaches "grcalc = Replace(grcalc, "-", "")" ergaenzt.
Haette nicht gedacht, das ich dafuer die passende stelle finde.

Aber es hat wirklich Funktioniert, er hat aus dem String das "-" abgeschnitten und nun passt es wirklich Perfekt.

Code: