Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validation (Excel 2002)

    Hey Gang,

    I have a large SS that people all over the company put in thier information...i.e. Phone Numbers and such. Problem is I don't want all the () and - that they use willy nilly. Is there a Macro that I can run that will delete everything from a cell besides the number?

    Thanks,

    IrishDan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation (Excel 2002)

    You could use Data | Validation, with Allow set to Whole Number, and an appropriate minimum and maximum value.

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation (Excel 2002)

    Thanks Hans,

    But this doesnt remove the ()'s and the -'s.

    IrishDan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation (Excel 2002)

    If you set the style in the Error Alert tab of Data | Validation to Stop, Excel shouldn't allow the user to enter a value containing parentheses or dashes.

  5. #5
    New Lounger
    Join Date
    Jan 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation (Excel 2002)

    Hans,

    Thank you so much for your response. My problem is not allowing them to add items but to fix the data as it comes back in. Each office (Over 50) has a copy of this SS. They update their own and forward it to me. I tehn have to go in and manually remove the ()'s and -'s. I want a macro or something that will remove the ()'s and the -'s automatically.

    IrishDan

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation (Excel 2002)

    Try this:
    <code>
    Sub FixPhoneNumbers()
    Dim oCell As Range
    For Each oCell In Selection.SpecialCells(xlCellTypeConstants)
    oCell.Value = "'" & Replace(Replace(Replace(Replace(oCell.Value, _
    " ", ""), "-", ""), "(", ""), ")", "")
    Next oCell
    End Sub
    </code>
    This code will act on all cells in the selection containing a constant value (i.e. not a formula).

Posting Permissions

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