2

I am using RegexExtract to extract a numberic value from text. I used the following regex [0-9]+(\.[0-9][0-9]?)? from the below link but I found it is only extracting two values after the decimal place. I need also the number in front of it. Not sure why it isn't working. I've tried different variations of it as well.

Simple regular expression for a decimal with a precision of 2

The text I am pulling from isn't always the same length and is about 100 characters along. In the text is the following in which I need to pull the numerical value only (x.xx): Gmax = 2.3428

ThisWorkbook.Worksheets(Warn.MachBox.Value & "_C1").Range("B" & Duncan2 + 1).Value = RegexExtract(ThisWorkbook.Worksheets(Warn.MachBox.Value & "_C1").Range("C" & Duncan2 + 1).Value, "Gmax = [0-9]+(\.[0-9][0-9]?)?")

The current way I have it and the different variations I've tried are only returning 0.34 and excluding the value before the decimal, if there is one. I need 2.34.

Any suggestions are greatly appreciated. Thanks!

Edit: Forgot to add the RegexExtract function info. This was found at the below link: How to extract text within a string of text

Community
  • 1
  • 1
jmeddy
  • 109
  • 1
  • 5
  • 16

2 Answers2

6

I would use:

(\d+)(?:\.(\d{1,2}))?

Explanation:

(           # First matching group
 \d         # A digit (shorthand for [0-9])
   +        # Any number of times
)
(?:         # A non-matching group
   \.       # A literal dot
   (        # Second matching group
    \d      # A digit
      {1,2} # 1 or 2 times (shorthand for \d\d? or [0-9][0-9]?)
   )
)?          # The decimal part is optional

Note that this will not give you the dot as part of the matching group, which will probably be easier in postprocessing. If not, you can remove the non-matching group and put the dot inside the second matching group.

If this does not give the results you want, that is most likely an issue with the way you are calling the regex library.

  • I tried (\d+)(?:\.(\d{1,2}))? and it returned 2, 34. I tried your other suggestion but not sure I modified it correctly (\d+)(\.\d{1,2})? and it returned 2, .34. – jmeddy Dec 19 '16 at 22:44
  • @jmeddy yes, that is correct and it confirms what I wrote. You can choose if you want to match the dot or not. –  Dec 19 '16 at 22:45
  • Ok, thanks Camil. Any idea why there is now a comma and space between the 2 and .34? Thanks for the help, I am new to using regex. – jmeddy Dec 19 '16 at 22:48
  • @jmeddy that is probably how Excel works. Can you try to remove all matching groups, but not the non-matching one? –  Dec 19 '16 at 22:49
  • Hi Camil, I used (\d+)(\.\d{1,2})? and found there was a ", " separator in my regexExtract function, which I failed to mention in the problem description. After changing the separator and using your suggestion, it worked!! Thanks again! – jmeddy Dec 19 '16 at 23:37
  • @jmeddy no problem, happy to help! –  Dec 20 '16 at 08:26
0
^-?\d+(\.\d{1,2})?$

This regex is for negative and positive numbers. If you want match this regex in a text you must remove ^$.

The {1,2} option is for the precision of your decimal. For your problem you can use this option : {2}

Mattasse
  • 1,023
  • 1
  • 11
  • 18
  • Hi Mattasse, thanks for the response. This is providing the same result for me and not including the value before the decimal.. Seems like it should work but for some it isn't. Maybe it is something else I am doing wrong here... – jmeddy Dec 19 '16 at 22:54