Regex: any 10 digit number preceded by "#" or "# "

I have a field that contains text and an account number embedded in the text. for example

blah bla blah testing ACCT # 3402058931 \bla \ABC\203499\ blah blah

The problem is the string that precedes the account number and is supposed to be the identifier, sometimes contains typos:
it varies: "ACCT # ", "ACCOUNT#", "AC#" (with or without spaces). so to parse it out I'm thinking to match any 10 digit number that is preceded by either "# " or "#", not sure I want to include all of the possible variations.
So far I have (\d{10}), which will match any 10 digit number (this should work 95% of the time, but it would be good if I could improve it.

Regex

Accepted answers

MarqueeCrew

Regex_replace([field],"(.*#|.*#\s)(\d{10}).*",'$2')

Group 1 is
Any characters followed by a # or
Any chapters followed by a # followed by a space.

Group 2 is
10 digits

Followed by anything

The replacement is group 2

Cheers,
Mark

All comments

MarqueeCrew

Regex_replace([field],".*(\d{10}).*",'$1')

That should get it.

PabloDanielovich

Thanks Marquee Crew
could you explain that expression? how does that make sure the account number is preceded by "#" or "# "?
is the $1 basically saying that we're replacing the entire field value with just the first matched group?

MarqueeCrew

PabloDanielovich

Awesome. Thank you

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703