Two step.

1. EditReplace

What: [?]

With: nothing

Replace all.

2. Add this UDF to a workbook general module.

Function RemAlphas(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
RemAlphas = RemAlphas & Mid(sStr, i, 1)
End If
Next i
Else
RemAlphas = sStr
End If
End Function

=RemAlphas(cellref)*1 copy down to return 5555551234

FormatCellsSpecialPhone Number


Gord Dibben MS Excel MVP

On Fri, 6 Nov 2009 11:21:01 -0800, tonuab
wrote:

Is it possible to use wildcard characters in the replace string and have them
be treated as wildcards instead of text.

I have a large spreadsheet that contains a column of phone numbers. These
phone numbers have all been entered by different people and I want to
normalize the way they are presented.

Some have been entered as [1] 555-555-1234, and I'd like to find all
instances like this and replace it with (555) 555-1234. I can use wildcards
to find all of these instances (find "[1] ???-"), but I can't replace using
the wildcard character (replace "(???)" doesn't work.

I also have instances of both (555) 555-1234 and (555)555-1234. I'd like to
search on all the items that have no space after the parentheses and insert a
space.

Can anyone help with this?