Stripping Format

[kschomer]

On a telephone (text) field. Is there an easier way to remove the formating. I currently use a series of update queries using left(telephone)13, left(phone,3) left (6)right (4) ext. It takes about 5 queries to get rid of the (brackets) and xxx-hyphen. I am sure there must be a function which would strip away the format and only recognize the text.

 

[LarryDeLaruelle]

Try this. Create a new function with this content:

Public Function PhoneParse(strIn As String) As String

PhoneParse = Mid(strIn, 2, 3)
PhoneParse = PhoneParse & Mid(strIn, 7, 3)
PhoneParse = PhoneParse & Mid(strIn, 11, 4)

End Function

In a field cell in you query use this:

Phone: IIf([PhoneNum] Is Null,"",PhoneParse([phonenum]))

Change "PhoneNum" to the field name for your phone number field.

Should do the trick for you.
Larry De Laruelle
larry1de@yahoo.com

 

[MichaelRed]

Hmmmmmmmmmmmmmmmmmmm,

In the spirit of alternatives I offer the following:

Public Function basOnlyNumChrs(strIn As String) As String

    'Usage
    'Debug.Print basOnlyNumChrs("(603) - 555.1212")
    '6035551212

    Dim strTemp As String
    Dim MyChr As String * 1
    Dim MyUcChr As String * 1
    Dim Idx As Integer

    For Idx = 1 To Len(strIn)
    
        MyChr = Mid(strIn, Idx, 1)
        MyUcChr = UCase(MyChr)

        If (MyUcChr >= &quot;0&quot; And MyUcChr <= &quot;9&quot;) Then
            strTemp = strTemp & MyChr
        End If

    Next Idx

    basOnlyNumChrs = strTemp

End Function

Just because it is SO simplistic (and therefore probably not useful?), I am also offering the complimentary function

Public Function basOnlyAlph(strIn As String) As String


    'Usage
    'Print basOnlyAlph("this dOg haS 399.4217 FleAs")
    'thisdOghaSFleAs


    Dim strTemp As String
    Dim MyChr As String * 1
    Dim MyUcChr As String * 1
    Dim Idx As Integer

    For Idx = 1 To Len(strIn)
    
        MyChr = Mid(strIn, Idx, 1)
        MyUcChr = UCase(MyChr)

        If (MyUcChr >= &quot;A&quot; And MyUcChr <= &quot;Z&quot;) Then
            strTemp = strTemp & MyChr
        End If

    Next Idx

    basOnlyAlph = strTemp

End Function

P.S. Larry, I THINK kschomer was more or kless looking for a soloution which accepted a variety of input formats.

PPS, kschomer, uses Larrys' thing about putting the function in an update query.



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[raskew]

Or, alternatively, if brevity is an issue, you could copy/paste this function to a module in Northwind:

Function removeit(thestuff As String) As String
'*******************************************
'Name: removeit (Function)
'Purpose: Remove all non-numeric characters from a string
'*******************************************
Dim strhold As String, intLen As Integer, n As Integer
strHold = RTrim(thestuff)
intLen = Len(strHold)
strHold = ""
For n = 1 To intLen
strHold = strHold & IIf(IsNumeric(Mid(thestuff, n, 1)), Mid(thestuff, n, 1), "&quot
Next n
removeit = strHold
End Function


…and test it using this query to view the reformatted phone numbers in (table) Customers:

SELECT CompanyName, Phone, removeit([phone]) AS widget
FROM Customers;

 

[LarryDeLaruelle]

Michael: I think I like your's better since it has more a more general application.

Raskew: Isn't the use of the Immediate If less efficient than a regular If, Then, Else statement? Not that it matters much on most systems/applications.

That's what I like about this stuff. Give three programmers the same problem and you'll probably get six different solutions.
Larry De Laruelle
larry1de@yahoo.com

 

[MichaelRed]

reaskew,

Nice use of the IIF and the isnumeric. I have never really gotten into the IIF thing.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[ComputorMike]

Am I missing something to this question?
If you need a value you can val(MyPhoneNumber(PhoneNumber) and get 1234567890 out of (123) 456-7890

Public Function MyPhoneNumber(sPhoneNumber As String) As String
Dim I As Integer
Dim sNumber As String

For I = 1 To Len(sPhoneNumber)
If IsNumeric(Mid(sPhoneNumber, I, 1)) Then sNumber = sNumber & Mid(sPhoneNumber, I, 1)
Next I

MyPhoneNumber = sNumber
End Function

 

[dgriffin]

Don't forget the simplest of all approaches...

Set the FORMAT property of the field in your forms and store only numbers in the table.