Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    search/replace 2 decimal places (excel xp)

    I have a file that has some numbers with 2 decimal places and a % sign and some with whole numbers. I want to search the whole file and change all the numbers to whole numbers but retain the % sign if it was there already. Can i do this using find/replace or does it have to be a macro? Thanks for the help

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: search/replace 2 decimal places (excel xp)

    How about this?

    <pre>Option Explicit
    Sub ChangeFormat()
    Dim rCell As Range
    Dim sFormatOld As String
    Dim sFormatNew As String
    sFormatOld = "0.00%"
    sFormatNew = "0%"
    For Each rCell In ActiveSheet.UsedRange
    If rCell.NumberFormat = sFormatOld Then _
    rCell.NumberFormat = sFormatNew
    Next
    Set rCell = Nothing
    End Sub</pre>



    Steve

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: search/replace 2 decimal places (excel xp)

    yep, that will do it. Thanks so much

Posting Permissions

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