+- Office-Fragen.de (https://office-fragen.de)
+-- Forum: Microsoft Office (https://office-fragen.de/forum-1.html)
+--- Forum: Excel (https://office-fragen.de/forum-2.html)
+--- Thema: Position der ersten Zahl in einer Zelle (/thread-22949.html)
Position der ersten Zahl in einer Zelle - snipebear - 27.09.2021
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
RE: Position der ersten Zahl in einer Zelle - RPP63 - 27.09.2021
Moin!
Zitat: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
RE: Position der ersten Zahl in einer Zelle - Flotter Feger - 27.09.2021
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 FunctionFormel: =FINDEZAHL(A1) ergibt "1 23"
Formel: =FINDEZAHL(A1;0) ergibt 4
Sabina
RE: Position der ersten Zahl in einer Zelle - snipebear - 28.09.2021
(27.09.2021, 17:08)RPP63 schrieb: Moin!
Zitat: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
RE: Position der ersten Zahl in einer Zelle - snipebear - 28.09.2021
(27.09.2021, 22:21)Flotter Feger schrieb: Hallo,Hi Sabina,
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.
SuchString A1 lautet "abc1 23"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
Formel: =FINDEZAHL(A1) ergibt "1 23"
Formel: =FINDEZAHL(A1;0) ergibt 4
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
RE: Position der ersten Zahl in einer Zelle - lupo1 - 30.09.2021
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
RE: Position der ersten Zahl in einer Zelle - RPP63 - 01.10.2021
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
RE: Position der ersten Zahl in einer Zelle - lupo1 - 16.10.2021
Hab mich versehen.