Having trouble creating macro to delete dashes from phone nu

[stoesman]

All,

I'm having problems recording a macro that will edit a cell that contains a phone number and delete the dashes from th phone number. Turning xxx-xxx-xxxx into xxxxxxxxxx.

The macro I recorded doesn't work. It takes whatever cell I recorded the macro in and simply inserts that number over the working number!

Any ideas?

Thanks.

[Anne Troy]

Generally...

Select the column of phone numbers. Start recording your macro. Hit Find/Replace (ctrl-h). Make sure it's not looking in *entire cells*. Put a hyphen in the Find What box. Don't put anything in the Replace With box. Hit Replace all. Stop recording.

[wi_guy]

I may be going off on a tangent, but here's a formula to clean up that data....assuming the phone # is in cell A1 .... =LEFT(A1,3)&MID(A1,5,3)&MID(A1,9,4). Hope that helps

[stoesman]

Thank you Dreamboat. That did the trick.
I was using keystrokes instead of the find/replace function.

[Cosmos75]

Try this macro. Change as needed

'Assuming you numbers start in A2
Range("A2").Select

'Finds last row with data in it starting from A65536 (Last row in Excel)
Range(Selection, Range("A65536").End(xlUp)).Select

'Counts number of row from A2 to found last row
RowCounter = Selection.Count

'Remove - from Cas Number
For i = 2 To RowCounter + 1
Range("A" & i).Select
ActiveCell.Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Next i

EDIT: mention that this is a macro

[ This Message was edited by: cosmos75 on 2002-04-16 14:38 ]

[llizanoa]

Hi
I'm Using this macro for social security numbers. But it deletes the lead zero on the ones that start with zero.

How can I avoid it?

Thanks