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 October 17, 2015)
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.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
If you have some numbers stored in cells that are formatted as text, you may get some surprises when you try to use those ...Discover More
When creating custom formats, you can employ a wide range of codes to define your formatting pattern. This tip focuses on ...Discover More
When dealing with files containing comma-separated values, you want to make sure that what gets imported into Excel ...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.