CODEWITHSUNDEEP
stringvbatextnumbersextract
Sas
Sas

Reputation: 1

Extract numbers within text VBA/Excel

Looking for some suggestions to tweak/enhance the formula I have created, to extract the number from a string.

Have the below sample text in a Cell A1:

Based on the invnum:-1234567 The calculation is based on 123.33*3.00

Wrote the below formula in B1

=VALUE(LEFT(MID(A1,FIND("invnum:-",A1)+7,LEN((A1))),7)

the Result given is -1234567

However, the length of the reference number on my source file is variable, Looking to extract only the number following the word invnum:-

Looking to include this formula in a macro, so trying to keep it simple any ideas/suggestions please?

Upvotes: 0

Views: 679

Answers (2)

PeterT
PeterT

Reputation: 8557

Let's get crazy and step through this...

Start with the intermediate indexes you need to find, such as the start, end, and length of the numeric string. Once you have those, just carefully reassemble your formula:

enter image description here

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152605

Try this Function:

Function findNumber(inPtStr As String) As Double
    Dim strArr() As String
    Dim i As Long
    inPtStr = Replace(inPtStr, ":", " ")
    strArr = Split(inPtStr)
    For i = LBound(strArr) To UBound(strArr)
        If IsNumeric(strArr(i)) Then
            findNumber = --strArr(i)
            Exit Function
        End If
    Next i
End Function

Then you can call it from a regular sub.

If you want a formula then:

=--TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),FIND("invnum:",SUBSTITUTE(A1," ",REPT(" ",99)))+7,99))

Upvotes: 0

Related Questions