Forum Discussion

Eve2468's avatar
Eve2468
Copper Contributor
Oct 12, 2021

Extract numbers from text

Here I got     A5+B1+C11+D2+E5
I want to extract each number after text a b c. and separate it to each cell like C3 to C7
But I don't know which function to use

excel
Formulas and Functions

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Oct 12, 2021

    Eve2468 

    Copy the following custom function into a module in the Visual Basic Editor:

    Function SplitText(s As String)
    Dim v
    Dim i As Long
    Dim j As Long
    v = Split(s, "+")
    ReDim a(UBound(v), 1)
    For i = 0 To UBound(v)
        For j = 1 To Len(v(i))
            If IsNumeric(Mid(v(i), j, 1)) Then
                Exit For
            End If
        Next j
        a(i, 0) = Left(v(i), j - 1)
        a(i, 1) = Val(Mid(v(i), j))
    Next i
    SplitText = a
End Function

    If you have Microsoft 365 or Office 2021:

    Select B3.

    Enter the formula =SplitText(B2)

     

    If you have an older version:

    Select B3:C7.

    Enter the formula =SplitText($B$2) and confirm it with Ctrl+Shift+Enter.

Resources