making a phone number to be just 10 numbers

[Guest]

Hello:

If you have a phone number in an Excel field appearing as, say,
(555)555-5555 how can you format it to read one set of numbers like
5555555555?

Thanks!

childofthe1980s

 

[Chip Pearson]

You can't do it with just formatting. You have to do a series of
Replace statements to get rid of the unwanted characters. E.g.,

Dim S As String
S = "(505) 555-1234"
S = Replace(S, "(", "")
S = Replace(S, ")", "")
S = Replace(S, " ", "")
S = Replace(S, "-", "")
Debug.Print S


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


in message
news:[email protected]...

 

[Guest]

Try something like this:

For a phone number in A1
B1: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Here's a modified VB function that strips all non-numeric characters from a
string. (It's been modified to use the International(xlDecimalSeparator)
constant) You would need to determine the length of input after filtering,
and notify the user if it's not valid.

Function FilterNumber(Text As String, TrimZeros As Boolean) As String
' Filters out formatting characters in a number and trims any trailing
decimal zeros
' Requires the FilterString function
' Arguments: Text The string being filtered
' TrimZeros True to remove trailing decimal zeros
' Returns: String containing valid numeric characters.

Const sSource As String = "FilterNumber()"

Dim decSep As String, i As Long, sResult As String

' Retreive the decimal separator symbol
decSep = Application.International(xlDecimalSeparator) 'Format$(0.1, ".")
' Filter out formatting characters
sResult = FilterString(Text, decSep & "-0123456789")
' If there's a decimal part, trim any trailing decimal zeros
If TrimZeros And InStr(Text, decSep) > 0 Then
For i = Len(sResult) To 1 Step -1
Select Case Mid$(sResult, i, 1)
Case decSep
sResult = Left$(sResult, i - 1)
Exit For
Case "0"
sResult = Left$(sResult, i - 1)
Case Else
Exit For
End Select
Next
End If
FilterNumber = sResult
End Function

HTH
Regards,
Garry

 

[Guest]

THAT WAS PERFECT, RON!!!!!!!!!! THANK YOU SO MUCH--THAT'S JUST WHAT I
NEEDED!!!!!!!!!

 

[Guest]

Sorry..! Here's the FilterString function I forgot to include:


Function FilterString(Text As String, ValidChars As String) As String
' Filters out all unwanted characters in a string.
' Arguments: Text The string being filtered
' validChars The characters to keep
' Returns: String containing only the valid characters.

Const sSource As String = "FilterString()"

Dim i As Long, sResult As String

For i = 1 To Len(Text)
If InStr(ValidChars, Mid$(Text, i, 1)) Then sResult = sResult &
Mid$(Text, i, 1)
Next
FilterString = sResult
End Function

Regards,
Garry