Mehrstellige Zahl aus Zelle mit diversem Text extrahieren

[{Boris}]

Hi LCohen,

die Formel braucht aber ein Leerzeichen vor der Zahl - das ist oft nicht der Fall. Und Excel365 hat der TE nicht

[derkneifel]

Hallo LCohen,

leider erkennt die Formel (bei mir) die 7-stellige Zahl nur, wenn sie freistehend ist.

---

Ich habe das hier noch gefunden:

Postleitzahl extrahieren * (herber.de)

=VERWEIS(9^9;1*TEIL(WECHSELN(A1;" ";"#")&"#";SPALTE(A1:IQ1);7))

Wie kann ich die Formel verändern, so dass mir anstatt #NV ein vorgegebener Wert x (z.B. "9999999") ausgegeben wird?

Gruß
derkneifel

[LCohen]

{Boris}, Leerzeichen: Stimmt, hatte ich im Beispiel nicht gesehen. Herstellung der Leerzeichen hier (365) (unter Hinzunahme von TEXTVERKETTEN).

kneifel: Schau Dir WENNFEHLER an, das macht genau das!

[{Boris}]

Hi LCohen,

aber auch der Link zeigt ne xl365-Variante. Die Version hat der TE aber nicht

[LCohen]

Deshalb habe ich (365) reingeschrieben.

Außerdem kann der TE XLWeb verwenden. Solche Fragestellungen sind meist einmalig, nicht täglich, durchzuführen.

[{Boris}]

Hi,

etwas kürzer geht es, wenn man beide Ansätze kombiniert.

=WENN(ANZAHL(--TEIL(WECHSELN(A1&" ";" ";"#");ZEILE($1:$100);7))*(ANZAHL(--TEIL(WECHSELN(A1&" ";" ";"#");ZEILE($1:$100);8))=0);VERWEIS(9^9;--TEIL(WECHSELN(A1&" ";" ";"#");ZEILE($1:$100);7));"keine 7-stellige Zahl")

[Gast 123]

Hallo

ich habe mir erlaubt mal eine VBA Lösung hochzuladen. War etwas knifflig, funktioniert aber.

Dem Makro ist es egal ob vor oder hinter der 7stelligen Zahl ein Text steht, sie wird als Zahl erkannt.
Miir fiel dabei auf, das das Zahlenformat auf 7stellig ausgelegt ist. Bei kleineren Zahlen steht eine Null davor.
Das war etwas knifflig, aber selbst das erkennt mein Makro.

Leider ene alte Excel2003 Datei, aber das Makro kannst du ja nach dem Test in deine Datei kopieren.
Viel Spass beim Testen mit Originaldaten.  Bin gespannt auf deine Rückmelldung.

mfg Gast 123

[derkneifel]

Danke Boris!
Wie bei deiner Formel zuvor ein perfektes Ergebnis und dazu auch noch ein ganzes Stück kürzer.
In den nächsten Wochen werde ich eure Formeln mal einem Praxis-Stresstest unterziehen und ich hoffe, dass der/euer Aufwand mir die Arbeit erleichtern wird.

Gruß
derkneifel

[HKindler]

Hi,

hier mal noch eine kleine VBA-Routine aus meinem Fundes. Sie sucht einfach die erste Zahl innerhalb eines Strings - egal wie lang diese ist. Optional kann man statt am Anfang des Strings auch irgendwo mittendrin mit der Suche nach der Zahl starten. Wenn nur siebenstellige Zahlen erlaubt sein sollen, könnte man die Routine noch ausbauen.

Code:

Function NumberFilter(ByVal strText As String, Optional ByVal Start As Integer = 1) As Double
'Extrahiert eine Zahl aus String ab Start
Dim intCounter As Integer
strText = Replace(strText, ",", ".")
For intCounter = Start To Len(strText)
    If IsNumeric(Mid(strText, intCounter, 1)) Then  'hier geht die Zahl los
        If intCounter > 1 Then  'nicht beim ersten Buchstaben!
            If Mid(strText, intCounter - 1, 1) = "-" Then   'Prüfen ob davor ein Minus
                intCounter = intCounter - 1 'Zahl geht eins früher los
            End If
        End If
        NumberFilter = Val(Right(strText, Len(strText) - (intCounter - 1)))
        Exit Function
    End If
Next intCounter
End Function

[shift-del]

Ich werfe auch noch eine 365er-Lösung in den Ring.

Code:

=LET(
text_aufteilen;TEIL(A1;SEQUENZ(LÄNGE(A1));1);
wert_ist_eine_zahl;ISTZAHL(--text_aufteilen);
text_entfernen;WENN(wert_ist_eine_zahl;text_aufteilen;" ");
ziffern_verketten;--TEXTTEILEN(GLÄTTEN(TEXTKETTE(text_entfernen));" ");
anzahl_stellen;LÄNGE(ziffern_verketten)=7;
gülitge_zahlen;WENN(anzahl_stellen;ziffern_verketten);
anzahl_gültige_zahlen;ANZAHL(gülitge_zahlen);
ergebnis;WENNS(anzahl_gültige_zahlen=0;"keine 7-stellige Zahl";anzahl_gültige_zahlen>1;"mehr als eine 7-stellige Zahl";WAHR;SUMME(gülitge_zahlen));
ergebnis)