making a phone number to be just 10 numbers

[childothe1980s]

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


"childothe1980s" <childothe1980s@discussions.microsoft.com> wrote
in message
news:080285B5-5658-4AC8-B0CC-17DFA2DE962E@microsoft.com...
> 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

[Ron Coderre]

Try something like this:

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

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

XL2002, WinXP


"childothe1980s" wrote:

> 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

[GS]

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

[childothe1980s]

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

"Ron Coderre" wrote:

> Try something like this:
>
> For a phone number in A1
> B1: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "childothe1980s" wrote:
>
> > 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

[GS]

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


"GS" wrote:

> 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