Written by Allen Wyatt (last updated January 17, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
One of the very useful tools provided in Excel is Find and Replace, which allows you to locate and change information stored in cells. One place that Find and Replace won't work, however, is with information stored in headers or footers for your worksheets.
The only way to handle the finding and replacing of information in a header or footer is to use a macro. It is a rather trivial task to access what is stored in the various parts of the header and footer, check them for what you want to find, and then replace it with some new text. The following macro provides an example.
Sub FnR_HF() Dim sWhat As String, sReplacment As String Const csTITLE As String = "Find and Replace" sWhat = InputBox("Replace what", csTITLE) If Len(sWhat) = 0 Then Exit Sub sReplacment = InputBox("With what", csTITLE) With ActiveSheet.PageSetup ' Substitute Header/Footer values .LeftHeader = Application.WorksheetFunction.Substitute( _ .LeftHeader, sWhat, sReplacment) .CenterHeader = Application.WorksheetFunction.Substitute( _ .CenterHeader, sWhat, sReplacment) .RightHeader = Application.WorksheetFunction.Substitute( _ .RightHeader, sWhat, sReplacment) .LeftFooter = Application.WorksheetFunction.Substitute( _ .LeftFooter, sWhat, sReplacment) .CenterFooter = Application.WorksheetFunction.Substitute( _ .CenterFooter, sWhat, sReplacment) .RightFooter = Application.WorksheetFunction.Substitute( _ .RightFooter, sWhat, sReplacment) End With End Sub
Note how the macro does the replacements in all three parts of the header and all three parts of the footer.
If you prefer to not use your own macro, or if you want a more full-featured Find and Replace for Excel, you might consider the free FlexFind add-in from Excel MVP Jan Karel Pieterse:
http://www.jkp-ads.com/officemarketplaceff-en.asp
This add in searches regularly, but also searches in lots of other areas including headers and footers.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3388) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Find and Replace in Headers.
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!
The find and replace used in Excel is less powerful than its counterpart in Word, so it is not able to do some of the ...
Discover MoreWildcard characters can be used within the Find and Replace tool, but what if you want to actually search for those ...
Discover MoreWhen you search for information in a worksheet, you expect Excel to return results that make sense. If you don't get a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments