Anyone up for a serving of RegEx?

[acent]

Greetings.

I'm working with a proprietary database over an odbc connection. What I need to do is a series of find and replaces.

For instance the phone number are of formats (XXX) XXX-XXXX or XXX-XXX-XXXX or something else. what I want is to transform this into XXXXXXXXX. Running the REPLACE function a number of times could do this, but I was thinking a regular might be "betta stuff".

Anyone attempted this? If so, how might I be able to do this?

P.S. I didn't design the DB to hold phone numbers as text, so don't blame me.

"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws

 

[HarleyQuinn]

If you've just got the one phone number in the field (not multiple numbers or any other information) a simple regular expression to replace non numeric characters should do the trick. Something like:

Function FormatPhoneNo(strInput As String) As String
Dim re As Object
Dim strResult

Set re = CreateObject("VBScript.RegExp")

With re
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "[^0-9]"
    FormatPhoneNo = .Replace(strInput, "")
End With

Set re = Nothing
End Function

This replaces anything that's not a digit between 0 and 9 with "".

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[HarleyQuinn]

As an aside, I'm always up for a serving of RegEx!



HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post