Extract numbers from text

%3CLINGO-SUB%20id%3D%22lingo-sub-2837317%22%20slang%3D%22en-US%22%3EExtract%20numbers%20from%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2837317%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Eve2468_0-1634032861824.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F316694i9FCB826C2E309BB1%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Eve2468_0-1634032861824.png%22%20alt%3D%22Eve2468_0-1634032861824.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHere%20I%20got%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BA5%2BB1%2BC11%2BD2%2BE5%3CBR%20%2F%3EI%20want%20to%20extract%20each%20number%20after%20text%20a%20b%20c.%20and%20separate%20it%20to%20each%20cell%20like%20C3%20to%20C7%3CBR%20%2F%3EBut%20I%20don't%20know%20which%20function%20to%20use%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2837317%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2837701%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20numbers%20from%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2837701%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1182967%22%20target%3D%22_blank%22%3E%40Eve2468%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECopy%20the%20following%20custom%20function%20into%20a%20module%20in%20the%20Visual%20Basic%20Editor%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EFunction%20SplitText(s%20As%20String)%0A%20%20%20%20Dim%20v%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Dim%20j%20As%20Long%0A%20%20%20%20v%20%3D%20Split(s%2C%20%22%2B%22)%0A%20%20%20%20ReDim%20a(UBound(v)%2C%201)%0A%20%20%20%20For%20i%20%3D%200%20To%20UBound(v)%0A%20%20%20%20%20%20%20%20For%20j%20%3D%201%20To%20Len(v(i))%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20IsNumeric(Mid(v(i)%2C%20j%2C%201))%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Exit%20For%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20Next%20j%0A%20%20%20%20%20%20%20%20a(i%2C%200)%20%3D%20Left(v(i)%2C%20j%20-%201)%0A%20%20%20%20%20%20%20%20a(i%2C%201)%20%3D%20Val(Mid(v(i)%2C%20j))%0A%20%20%20%20Next%20i%0A%20%20%20%20SplitText%20%3D%20a%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIf%20you%20have%20Microsoft%20365%20or%20Office%202021%3A%3C%2FP%3E%0A%3CP%3ESelect%20B3.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%20%3DSplitText(B2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20an%20older%20version%3A%3C%2FP%3E%0A%3CP%3ESelect%20B3%3AC7.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%20%3DSplitText(%24B%242)%20and%20confirm%20it%20with%20Ctrl%2BShift%2BEnter.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Eve2468_0-1634032861824.png

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

1 Reply

@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.