Fluffy
You could also use a macro.
Public Sub StripAll_But_NumText()
Dim rConsts As Range
Dim rCell As Range
Dim i As Long
Dim sChar As String
Dim sTemp As String
On Error Resume Next
Set rConsts = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConsts Is Nothing Then
For Each rCell In rConsts
With rCell
For i = 1 To Len(.text)
sChar = Mid(.text, i, 1)
If sChar Like "[0-9a-zA-Z]" Then _
sTemp = sTemp & sChar
Next i
.Value = sTemp
End With
sTemp = ""
Next rCell
End If
End Sub
Select the range of cells then run the macro.
Then Format as SpecialPhone Number
Gord Dibben Excel MVP
On Thu, 17 Mar 2005 16:01:02 -0800, Fluffy from Wisconsin
wrote:
"Dave Peterson" wrote:
Just kind of an unfortuate line break:
=TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(
SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-","")),"(000) 000-0000")
Jason Morin wrote:
Try this, Fluffy:
=TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE
(SUBSTITUTE(A1,".",""),"/",""),"(",""),")",""),"-
","")),"(000) 000-0000")
HTH
Jason
Atlanta, GA
-----Original Message-----
Hi All,
I have a listing of names, addresses, etc. in an Excel
document. The
telephone numbers have been entered in several different
formats:
123/456-1234, (123)345-1234, 123-455-1234. Is there a
way to format the
column so that these numbers will all be displayed in
the same format? If
so, please explain how. Thanks,
Fluffy from Wisconsin
Reading Teacher
.
--
Dave Peterson
I tried the format you gave me, but all I get is "Microsoft Office Excel cannot use the number format you typed." When I click on the "help" for more informaiton about custom number formats, I just get a blank box. Any other ideas? Thanks again, Judy
|