removing spaces and non-numeric characters 1

[sedgely]

I have a table containing phone numbers, these are entered manually and therefore are in different formats what i need to do is remove all spaces and non-numeric characters, does anyone know a neat way of doing this?

Cheers
Craig

 

[Bowers74]

Use the SUBSTITUTE() function:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","&quot,"-","&quot,":","&quot,"*","&quot,"_","&quot,"(","&quot,&quot","&quot

This will remove all of the spaces and non-numeric characters in cell A1.

I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Sylv4n]

Or there is a IsNumeric

So something like

FOR X = 1 to Len(Me.txtPhoneNumber.Value)
IF ISNUMERIC Mid(Me.txtPhoneNumber.Value,X,1) THEN
'Some Code to remove the offending Character
END IF
NEXT

alternativley Use a Input Mask and set it to accept Numbers

 

[SkipVought]

Hi,

Taking Sylv4n's code one step further...

sPhone = ""
FOR X = 1 to Len(Me.txtPhoneNumber.Value)
  sByte = Mid(Me.txtPhoneNumber.Value,X,1)
  Select Case sByte
     Case is "0" to "9"
       sPhone = sPhone & sByte
  End Select
NEXT

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Sylv4n]

Good solution I must admit I could not think of the logig to remove the character, but moving it to another variable - Genius!

 

[strongm]

or you could add a reference to the Microsoft VBScript Regular Expressions library, and use the following:
[tt]
Private Function CleanPhoneNumber(strSource as String) As String
With New RegExp
.Global = True
.Pattern = "([\D+])"
CleanPhoneNumber = .Replace(strSource, "&quot
End With
End Function