troubleshooting Question

Regular Expression to extract decimal number from text string

Avatar of dsimcox
dsimcox asked on
Microsoft ExcelRegular Expressions
5 Comments1 Solution5894 ViewsLast Modified:
I'm having trouble getting the right regular expression to use in an .Execute or .Replace RegExp Method to extract a decimal number.

Here's what I've come up with that does not work:
Public Function MyNumber(txt)
Dim RegEx As Object

Set RegEx = CreateObject("vbscript.regexp")
With RegEx
    .Global = True
    .Pattern = "[^\d]+[^\.]?[^\d]+"
End With

MyNumber = RegEx.Replace(txt, "")

Set RegEx = Nothing

End Function

I want to extract the decimal number from a string like this:  "$13.4M"  or "74.5 FTE"

My pattern is: "[^\d]+[^\.]?[^\d]+"

[^\d]+   -  Not a digit (multiple instances)
[^\.]?    -  Not a decimal (only one instance)
[^\d]+   -  Not a digit (multiple instances)

This function is returning the entire string when I test it. What am I doing wrong?

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros