Presume RegEx?

Hi, I'm trying to use RegEx to match and expose cells with more than one dots (full stop) in the them as this throws off one of our data processes when multiple dots exist. I presume RegEx is probably the best way to do this, but open to ideas and thanks for your help!

example: Agreed - (667.796.00)

Regex

Parse

Expression

Data Investigation

Accepted answers

AkisM

Hi Adam,

Yes, it should go into the formula tool. Try this amendment instead:

Regex_countmatches([text],"\.?\d+\.\d+\.\d+") > 0 OR Regex_countmatches([text],"\.\d+\.?\d+\.\d+") OR Regex_countmatches([text],"\.\d+\.\d+\.?\d+")

I am not sure if you can paste all 3 expressions in the same formula. Might need 3 formulas, 1 for each regex countmatch.

If the desired effect is to simply mark which cells that have more than 1 dot, then running it through the formula should work fine.If you wanna remove dots as well, I would use the regex replace tool (though it can still be done through the formula).

The more variations of extra-dot sample data you can provide, the more accurate the regex we will be able to provide will be.

All comments

MarqueeCrew

One solution might be:

Regex_countmatches([text],"\d+\.\d+\.\d+") > 0

Cheers,

Mark

AdamSmith

Thanks for the response. I presume this goes into a formula tool rather than the Regex parse tool?

Running it through the Formula tool has not had the desired effect unfortunately.

That code found and marked this as a -1 result: Agreed - (1070.00) - 03.01.17

But didn't find this: Agreed - (.796.00)

Would appreciate any further ideas, thanks.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335