Use of Wildcard characters with replace

tonuab · External Since: Nov 06, 2009 Posts: 2

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?

Gord Dibben · External Since: Feb 23, 2004 Posts: 9178

Two step.

1. Edit>Replace

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

Format>Cells>Special>Phone Number

Gord Dibben MS Excel MVP

On Fri, 6 Nov 2009 11:21:01 -0800, tonuab <tonuab.RemoveThis@discussions.microsoft.com>
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?

tonuab · External Since: Nov 06, 2009 Posts: 2

After much research, I have determined that it is just not possible to
combine wildcard characters with replace. They are very useful for searching,
but they don't help with the situation I detailed above.

For anyone that might be interested in how I resolved my issue, I basically
removed all non-numeric characters from my data, so that each of my phone
number cells was formatted as 5555551234. Then, I converted the text to
tables, using fixed width columns of 3 characters, 3 characters and four
characters. Then, in a fourth column, I entered the formula
=CONCATENATE("(",A1,") ",A2,"-",A3). I then copied and pasted the values of
this column io ntthe original column to replace it with the updated
formatting.

It was a bit of pain because I had to copy the formula into each cell in a
very long column (over 17,000 fields) but copying and pasting groups of cells
made this a very quick fix.

I'm sure there is a much neater way to do this programmatically, but my
skill level is very limited in those areas.