delete "-" in cells

[bob engler]

I am trying to rid all the pucuations "(,),-" from a column of phone
numbers. I
have used the replace function with evrything OK except "-". It says it
finds
no entries with a "-". I tried just - and then '- but it never finds it.
What should
I use to find them?

Thanks.....

 

[Biff]

Hi!

Try this:

Get the ascii character code:

A1 = 123-4567

=CODE(MID(A1,4,1))

The code for a standard hyphen is 45. Use the code number returned by the
formula in Edit>Replace. For example:

Edit>Replace
Find what: hold down the ALT key and use the *numeric keypad* and type 045
(use the code number returned by the formula)

Biff

 

[Guest]

Try copying just that symbol from one of the phone numbers, and pasting that
into the "Find What" space in the Replace function.

 

[Dave Peterson]

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

Since you do see a box, then you can either fix it via a helper cell or a macro:

=substitute(a1,char(13),"")
or
=substitute(a1,char(13)," ")

Replace 13 with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(10), Chr(13)) '<--What showed up in CellView?

myGoodChars = Array(" ","") '<--what's the new character, "" for nothing?

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You could get all the characters you find by changing this:

myBadChars = Array("(", ")", "-", Chr(####))
myGoodChars = Array("", "", "", "")

make sure you have the same number of elements.

And change this line:

ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
to
Selection.Cells.Replace What:=myBadChars(iCtr), _

(Just select the range you want to fix before you run the macro.)