Eine Zahl in einem String finden und ausgeben

Heute habe ich mich mit einem Thema befasst, welches ich schon lange auf dem Notizzettel stehen hatte, aber nie angegangen war.

Und zwar arbeite ich beruflich nicht mit Linux und Open-Source, sondern mit Windows und den Microsoft-Office-Produkten - vornehmlich mit Excel.

Die Problemstellung war, dass in einer Spalte in vielen Zellen jeweils ein Text enthalten war, der eine Zahl beinhaltete. Diese Zahl war im Bereich von einstellig bis sechsstellig und an verschiedenen Positionen innerhalb des Textes.

Ja, wenn man einer Formelorgie nicht abgeneigt ist, dann kann man das Extrahieren der Zahl über verschachtelte Excelformeln erledigen. Das war mir immer schon zu kompliziert, da vieles sehr viel leichter mit einem Code in VBA zu erledigen ist.

Und somit habe ich mich heute aufgemacht, dieser Aufgabe mit einem VBA-Code auf den Leib zu rücken und mir und anderen eine Freude zum bevorstehenden Wochenende zu bereiten.

Die Beispieldaten sind Phantasiedaten. Daher kann ich sie zeigen, da sie nichts über mich oder andere Personen aussagen. Nachfolgend ein Screener der Ausgangslage:

Um den Artikel nicht zu lang werden zu lassen, sei hier direkt gesagt, dass die erste Version des kleinen Tools funktionierte, aber nicht alles abdeckte, und auch die zweite hatte ein Problem, da ich den Datentyp für die Zahl zu klein angenommen hatte (Integer vs. Single bzw. Long).

Aber als die Zelle (A18) mit der Zahl an der Reihe war, brach das Tool mit einer Fehlermeldung ab. Und zwar sucht das Tool nach Text, um darin eine Zahl zu finden. Entsprechend sind die Variablen deklariert. Wenn in einer Zahl, die in der Variablen für einen String steht, nach einer Zahl gesucht wird, kann es nur zu Kuddelmuddel kommen. Und so war es denn auch.

Daher musste vorab geprüft werden, ob der Wert der aktuellen Zelle ein String oder eine Zahl ist. Sobald die Prüfung erledigt ist, wird bei einer Zahl diese direkt weiterverarbeitet. Ist der Zellinhalt als Text, also also String, identifiziert, so setzt Regular Expresion (REGEX) ein.

Im nachfolgenden Quellcode des VBA-Tools habe ich die markanten Stellen, die für mich wichtig waren und sind, mit Kommentaren belegt. Das Tool besteht aus einer Main-Sub und aus einer Funktion. Die Funktion regelt die Abarbeitung der Regular Expression.

Das ist das Hauptpramm main:

Sub main()
'
' Deklaration der Variablen
Dim ausgabe As Long
Dim intI As Long
Dim myText As String
'
ausgabe = 0
'
' Startpunkt der nachfolgenden Do-While-Scheife
Range("A12").Select
'
Do While ActiveCell.Value <> ""
'
ausgabe = 0
'
    ' If-Abfrage, ob der aktuelle Zellinhalt ein numerischer Wert ist,
    ' der dann sofort in die Nachbarzelle geschrieben wird.
    If IsNumeric(ActiveCell.Value) Then
        ActiveCell.Offset(0, 1).FormulaR1C1 = ActiveCell.Value
    ' Ist der Wert der aktuellen Zelle ein String, so setzt REGEX ein
    Else
        ' Der Wert der aktuellen Zelle, der ein String ist, wird in die
        ' Variable übernommen.
        myText = ActiveCell.Value
            ' Es erfolgt eine Prüfung, ob in dem String eine Zahl enthalten
            ' ist, nachdem die Funktion durchlaufen wurde.
            ' Wenn eine Zahl enthalten ist, dann wird diese in der Nachbarzelle
            ' ausgegeben.
            If IsNumeric(numberFinden(myText)) Then
                ausgabe = numberFinden(myText)
                ActiveCell.Offset(0, 1).FormulaR1C1 = ausgabe
            ' Ist in dem String keine Zahl, so wird dies in der Nachbarzelle
            ' auch eingetragen.
            Else
                ActiveCell.Offset(0, 1).FormulaR1C1 = "keine Zahl"
            End If
    End If
    '
    Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
'
Loop
'
Range("A12").Select
'
End Sub

Das ist die Funktion numberFinden:

Function numberFinden(txt As String) As String
'
' Deklaration der Variablen
Dim re As New RegExp
'
' Festlegung des Suchmusters für REGEX, auh Pattern genannt
re.Pattern = "(\d+)"
'
' weitere Deklaration von Variablen, die hier stehen müssen - nicht oben,
' denn sonst erkennt das Tool diese Variablen nicht
Dim mtcColl As MatchCollection
Set mtcColl = re.Execute(txt)
'
' If-Abfrage, ob übehaupt einen Zahlenwert gefunden wurde.
' Wenn nicht, dann wird 'n/a' in die Nachbarcelle geschrieben.
If mtcColl.Count = 0 Then
   numberFinden = "n/a"
   Exit Function
    End If
'
Dim mtc As Match
Set mtc = mtcColl(0)
'
' Rückgabewert der Funktion wird übergeben.
numberFinden = mtc.SubMatches(0)
'
End Function

Nachdem das Tool gelaufen ist, könnte das Ergebnis z.B. so aussehen:

Es ist nicht zu verhehlen, und meinerseits auch nicht gewünscht, dass ich mich natürlich bei der Lösung des Problems habe inspirieren lassen und sehr viel Hilfe aus diesem Artikel VBA regular expressions: parentheses in Anspruch genommen.

Mein Code kann gerne weiterverwendet und auch verändert werden. Haftung für etwaige Schäden, die durch die Nutzung des Codes entstehen, kann ich nicht übernehmen und lehne das auch kategorisch ab.

Mit diesem Code habe ich endlich einen Weg und eine Grundlage gefunden, wie ich mittels REGEX verfahren kann, wenn ich etwas suche. Die Handhabung von REGEX ist nicht trivial und erfordert einiges an Übung. Wenn man weiß, wie REGEX einzusetzen ist, dann sieht das schlichte Pattern (\d+) wirklich vernichtend einfach aus. Aber es hat große Wirkung.

Jopii, am 10.12.2021

BTW:
Damit konnte ich heute auch jemand anderem einen glücklichen Moment und sehr viel weniger Tipparbeit verschaffen.