Extracting Numbers From A String

[k9nny]

Hello everyone,

I would like to be able to extract the quantity from this string. In this case it is 100.00, but the quantity is rarely in exactly the same position, but it is always followed by ‘EA’ and ‘0%’. Is it possible to extract a number based on the characters that follow?

10 60092025 0303110106 ELBOW 25MM X 90 DEG 100.00 EA 0% £0.48 £48.00

Thanks, Kenny.

 

[Sektor]

In your example 100.00 is NOT between "EA" and "0%".

 

[anandvarma]

try this formula

MID(a1,FIND("EA",a1)-7,6)

 

[Sektor]

UDF
Set reference: Tools -> References -> Microsoft VBScript Regular Expressions 5.5

Function Quantity(Str As String) As String

    ' Set reference: Tools -> References -> Microsoft VBScript Regular Expressions 5.5
    
    Dim re As New RegExp
    Dim s As String
    
    With re
        .Global = False
        .IgnoreCase = True
        .Pattern = "DEG\s+(.+)\s+EA"
    End With
    
    Quantity = re.Execute(Str)(0).SubMatches(0)

End Function

 

[k9nny]

Thanks all, anandvarma's formula worked out great. Kenny.

 

[anandvarma]

THANK Mr. Mike Grirvin, what ever little i know in excel is learned from Mike's "EXCELISFUN" Youtube channel