Having trouble creating macro to delete dashes from phone nu

stoesman

New Member
Joined
Mar 25, 2002
Messages
2
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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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
 
Upvote 0
Thank you Dreamboat. That did the trick.
I was using keystrokes instead of the find/replace function.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top