Phone # to number

howruaz9
howruaz9 used Ask the Experts™
on
Hi experts,

I have a phone# field formatting like (111) 123-4567, now I'm asked to provide number without area code and Hyphen, that means I should get 1234567. How do I get it?

Thank you very much in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
Set a Reference to the reference shown below.  The RegExp will cover pretty much all cases of characters in the number.


Put the Function below in a regular VBA module, then use the Function to convert or display the number only.


Public Function mPhNumDigitsOnly(sPhNum)

    If Nz(sPhNum, "") = "" Then Exit Function
    Dim oDigitsOnly As New RegExp
    With oDigitsOnly
        .Pattern = "x\d+\s*$|\D"
        .Global = True
    End With
    mPhNumDigitsOnly = Right(oDigitsOnly.Replace(sPhNum, ""), 10)

End Function


1
Top Expert 2016
Commented:
use the replace function in a query

NewPhone:replace(replace(replace(replace([phone#],"(",""),")",""),"-","")," ","")

Author

Commented:
Hi MX & cap,

Thank you very much to both of you, both solutions are working perfectly well.

All the best,
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
@DatabaseMX

That pattern looks so familiar  ; )
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Yep ... thanks to you. And I had a comment crediting you. But I see what happened.  I have the code in two different places and grabbed the slightly older version ...
ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
Heheheh...   Can't beat code reuse!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
:-)

Author

Commented:
Thanks to you too, kaufmed. It's nice that the code is so popular and helping many people like me.
Couple of my old friends like MX (if you don't mind to be called old friend, MX) that have been paying attention to my questions for many years are always acting quick, I appreciate their effort.

Thanks all again!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
no problem.  kaufmed must have a filter set for 'phone number', lol

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial