Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Replacing Cell Formats.
by Allen Wyatt
(last updated June 18, 2020)
David needs to find and change every occurrence of a specific cell format in a multi-worksheet workbook. For example, he may need to find all cells that are formatted as Currency and change that format to General. He wonders how to accomplish the task.
The best way to go about this task depends on the version of Excel you are using. If you are using Excel 2003 you can simply use Excel's Find and Replace tool to make the change. Follow these steps:
Figure 1. The Replace tab of the Find and Replace dialog box.
Figure 2. The Number tab of the Find Format dialog box.
If you are using an older version of Excel, then the Find and Replace tool doesn't allow you to search or replace formatting. Instead, you must use a macro to do the changes. Here's an example of a macro that simply goes through all the used cells in the workbook and sets all the formats to General.
Sub FormatGeneral() Dim iSht As Integer Dim rng As Range For iSht = 1 To Sheets.Count Set rng = Worksheets(iSht).UsedRange With rng .NumberFormat = "General" End With Next End Sub
If you wanted to get a bit more selective in which formats were replaced, then you'll need to check the existing format of the cells as you go through them. For instance, the following macro checks for any cells formatted as Currency and then changes only those cells to a General format.
Sub CurrencyToGeneral() Dim iSht As Integer Dim rng As Range Dim c As Range For iSht = 1 To Sheets.Count For Each c In Worksheets(iSht).UsedRange.Cells If c.NumberFormat = "$#,##0.00" Then c.NumberFormat = "General" End If Next c Next End Sub
If you want to make the macro even more flexible, you can have it ask you to click on a cell that uses the format you want to find and then click on a cell that uses the format you want to change those cells to.
Public Sub UpdateFormats() Dim rFind As Range Dim rReplace As Range Dim rNextCell As Range Dim sNewFormat As String Dim sOldFormat As String Dim ws As Worksheet On Error Resume Next ' Determine the old format Do Set rFind = Application.InputBox( _ prompt:="Select a cell that uses the format " & _ "for which you want to search", _ Type:=8) If rFind Is Nothing Then If MsgBox("Do you want to quit?", vbYesNo) = vbYes Then Exit Sub ElseIf InStr(1, rFind.Address, ":", vbTextCompare) > 0 Then MsgBox "Please select only one cell." Set rFind = Nothing End If End If Loop Until Not rFind Is Nothing sOldFormat = rFind.NumberFormat ' Determine the new format Do Set rReplace = Application.InputBox( _ prompt:="Select a cell using the new format", _ Type:=8) If rReplace Is Nothing Then If MsgBox("Do you want to quit?", vbYesNo) = vbYes Then Exit Sub ElseIf InStr(1, rReplace.Address, ":", vbTextCompare) > 0 Then MsgBox "Please select only one cell." Set rReplace = Nothing End If End If Loop Until Not rReplace Is Nothing sNewFormat = rReplace.NumberFormat ' Do the replacing For Each ws In ActiveWorkbook.Worksheets For Each rNextCell In ws.UsedRange If rNextCell.NumberFormat = sOldFormat Then rNextCell.NumberFormat = sNewFormat End If Next rNextCell Next ws MsgBox "The selected format has been changed." End Sub
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9865) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Replacing Cell Formats.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
You may want Excel to format your dates using a pattern it doesn't normally use—such as using periods instead of ...Discover More
Want to adjust the font size used in a cell or range of cells? It's easy to do by using the shortcut described in this tip.Discover More
Want to draw attention to some information in a particular cell? Make the cell flash, on and off. Here's how you can ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.