Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Having trouble creating macro to delete dashes from phone nu

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    ~Anne Troy

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    New Member
    Join Date
    Aug 2011
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Having trouble creating macro to delete dashes from phone nu

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •