Results 1 to 2 of 2
  1. #1
    brianw
    Guest

    Search and replace

    I would appreciate help in creating a macro in Excel 97 to achieve the following in a spreadsheet with a large number of entries:

    Search for AU$xx.00 and replace with just xx, where xx can be anything from 5 to 1000

    TIA

    Brian Wade

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Search and replace

    Do you really need a macro ?

    Try and make sure that AU$ is not part of the formatting (currency). If it is not, and is part of the cell value, then why not just do a search and replace on the whole sheet ? You just need to search for AU$ and leave the Replace with box blank.

    The following macro will remove the AU$ if is part of the formatting :-

    Sub RemoveCurrency()
    Cells.Select
    Selection.NumberFormat = "#,##0_);(#,##0)"
    Range("A1").Select
    End Sub

    If it is not part of the formatting, try the following :-

    Sub RemoveCurrencyText()
    Cells.Select
    Selection.Replace What:="AU$", Replacement:="", LookAt:=xlPart
    Selection.NumberFormat = "#,##0_);(#,##0)"
    Range("A1").Select
    End Sub

    If you do not want the operation to be performed on the entire worksheet, remove Cells.Select and select the cells required before you run the macro.

    Andrew C

Posting Permissions

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