Position der ersten Zahl in einer Zelle

[snipebear]

Hallo zusammen,

ich habe vor kurzem eine Funktion benötigt, welche mir die Position der ersten Zahl in einer Zelle zurückgibt. Z.B. bei "abc123" gibt die Funktion den Wert 4 zurück. Ich bin durch eine kurze Recherche im Netz auch schnell fündig geworden und es hat super funktioniert. Allerdings bin ich kein Freund von copy&paste, sondern möchte auch verstehen! Leider konnte ich mir einige Fragen nicht selbst beantworten, weshalb ich dieses Thema erstelle.

Dies ist die Funktion: =VERGLEICH(1;--ISTZAHL(--TEIL(A1;SPALTE(1:1);1));0)

Leider ist mir nicht ganz klar wie Excel jetzt durch meine Zeichenkette iteriert bzw. mir die entsprechende Zahl zurück gibt. Ich hatte bereits versucht mir die Ergebnisse der einzelnen Funktionen anzuzeigen, was leider nicht geklappt hat. Die Funktion funktioniert nur in voller Länge.

Ggf. kann mir ja hier jemand die Vorgehensweise von Excel bei dieser Funktion in seinen eigenen Worten erläutern, damit ich es kapiere.  

Danke schonmal im Voraus!

VG
Snipebear

[RPP63]

Moin!

Ich hatte bereits versucht mir die Ergebnisse der einzelnen Funktionen anzuzeigen, was leider nicht geklappt hat. Die Funktion funktioniert nur in voller Länge.

Das (Dein) Problem ist der Term SPALTE(1:1)
Schließlich versucht er, 2^14 aka 16.384 Einzellettern zu extrahieren.
Da steigt Formeln → Formelauswertung schon mal aus … 
Wenn Du dies mit eben genanntem Hilfsmittel überprüfen willst, nimm statt SPALTE(1:1) besser SPALTE(A42:F42)
Die 42 zeigen Dir, dass die Zeile unerheblich ist.

Bei Deinem aktuellen Excel nimmt man statt 
TEIL(A1;SPALTE(1:1);1)
sehr viel besser
TEIL(A1;SEQUENZ(LÄNGE(A1));1)
Alles klar? 

Gruß Ralf

[Flotter Feger]

Hallo,

ganz nebenbei ... die oben genannte Formel muss für alle normalen Menschen ... also die, die weder 365, noch Office 2021 haben, mit Strg+Shift+Enter abgeschlossen werden. Es handelt sich nämlich dabei um eine Matrixformel.

Außerdem würde ich das über eine UDF lösen ... natürlich wahlweise mit Ausgabe der Stelle der ersten Zahl im Text ... oder der Ziffernfolge selbst.
Inklusive Leerstellen zwischen den Ziffern ... soll bei Telefonnummern schon mal vorkommen.
 

Code:

Function FINDEZAHL(rng As Range, Optional fText As Boolean = True) As String
Dim Lng As Long
Dim erg As Variant
Dim str As String
Dim i As Long, j As Long

str = rng.Text
For Lng = 1 To Len(str)
    If IsNumeric(Mid(str, Lng, 1)) Then
        If Mid(str, Lng - 1, 1) = " " Then
            erg = erg & " " & Mid(str, Lng, 1)
            i = i + 2
        Else
            erg = LTrim(erg & Mid(str, Lng, 1))
            i = i + 1
        End If
    Else
        If i = 0 Then j = j + 1
    End If
Next
If fText Then
    FINDEZAHL = erg
Else
    FINDEZAHL = j + 1
End If
End Function

SuchString A1 lautet "abc1 23"
Formel: =FINDEZAHL(A1) ergibt "1 23"
Formel: =FINDEZAHL(A1;0) ergibt 4

Sabina

[snipebear]

Moin!

Ich hatte bereits versucht mir die Ergebnisse der einzelnen Funktionen anzuzeigen, was leider nicht geklappt hat. Die Funktion funktioniert nur in voller Länge.

Das (Dein) Problem ist der Term SPALTE(1:1)
Schließlich versucht er, 2^14 aka 16.384 Einzellettern zu extrahieren.
Da steigt Formeln → Formelauswertung schon mal aus … 
Wenn Du dies mit eben genanntem Hilfsmittel überprüfen willst, nimm statt SPALTE(1:1) besser SPALTE(A42:F42)
Die 42 zeigen Dir, dass die Zeile unerheblich ist.

Bei Deinem aktuellen Excel nimmt man statt 
TEIL(A1;SPALTE(1:1);1)
sehr viel besser
TEIL(A1;SEQUENZ(LÄNGE(A1));1)
Alles klar? 

Gruß Ralf

Hi Ralf,

mit Sequenz ist es deutlich einleuchtender!  

Vielen Dank

[snipebear]

Hallo,

ganz nebenbei ... die oben genannte Formel muss für alle normalen Menschen ... also die, die weder 365, noch Office 2021 haben, mit Strg+Shift+Enter abgeschlossen werden. Es handelt sich nämlich dabei um eine Matrixformel.

Außerdem würde ich das über eine UDF lösen ... natürlich wahlweise mit Ausgabe der Stelle der ersten Zahl im Text ... oder der Ziffernfolge selbst.
Inklusive Leerstellen zwischen den Ziffern ... soll bei Telefonnummern schon mal vorkommen.
 

Code:

Function FINDEZAHL(rng As Range, Optional fText As Boolean = True) As String
Dim Lng As Long
Dim erg As Variant
Dim str As String
Dim i As Long, j As Long

str = rng.Text
For Lng = 1 To Len(str)
    If IsNumeric(Mid(str, Lng, 1)) Then
        If Mid(str, Lng - 1, 1) = " " Then
            erg = erg & " " & Mid(str, Lng, 1)
            i = i + 2
        Else
            erg = LTrim(erg & Mid(str, Lng, 1))
            i = i + 1
        End If
    Else
        If i = 0 Then j = j + 1
    End If
Next
If fText Then
    FINDEZAHL = erg
Else
    FINDEZAHL = j + 1
End If
End Function

SuchString A1 lautet "abc1 23"
Formel: =FINDEZAHL(A1) ergibt "1 23"
Formel: =FINDEZAHL(A1;0) ergibt 4

Sabina

Hi Sabina,

vielen Dank für die Lösung. In meinem Fall ist es wichtig, dass das Ganze mit einer Excel-Formel im Vordergrund gelöst wird. 
Trotzdem danke für die Antwort.

VG

[lupo1]

Zur nicht beantworteten Frage, wie die Rückwärts-VERWEIS-Funktion funktioniert (übrigens mit 2 statt 1):

herber.de/excelformeln/pages/Letzte_benutzte_Zelle_in_einer_Zeile_oder_Spalte_Frank_Kabel_in_memoriam.html

[RPP63]

Moin Lupo!
Falscher Thread? 
Der TE sucht die Stelle der ersten Ziffer in einem Text (mittels VERGLEICH() der einzelnen Lettern).
Dein Link findet die letzte benutzte Zelle (mittels VERWEIS() einer Fehlerausgabe).

Gruß Ralf

[lupo1]

Hab mich versehen.