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.....

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 EditReplace. For example:

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

Biff

"bob engler" wrote in message
...
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.....

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

"bob engler" wrote:

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.....

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.)

bob engler wrote:

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.....

--

Dave Peterson