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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
When working with very large numbers in a worksheet, you may want the numbers to appear in a shortened notation, with an ...Discover More
When you get a bunch of raw data into Excel from an external source, it isn't going to be formatted to your liking. The ...Discover More
Excel is continually trying to figure out what type of data is being stored in a cell. If it can interpret a value as a ...Discover More
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.