Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Find and Replace in Headers.
Written by Allen Wyatt (last updated May 11, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 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.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
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:
https://jkp-ads.com/excel-flexfind.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, 2021, 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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
Searching for information in an Excel worksheet generally goes very smoothly. There can be times, however, when the ...
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 MoreThe Find and Replace capabilities of Excel allow you to easily locate all the cells in a worksheet that contain specific ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-06-07 20:18:19
J. Woolley
My Excel Toolbox includes the following function to return a worksheet's page setup properties, including headers and footers:
=ListPageSetup()
Expect 2 columns and 38, 44, or 50 rows depending on the presence of different first page and/or even page headers and footers.
See https://sites.google.com/view/MyExcelToolbox/
2024-05-24 12:23:25
J. Woolley
Excel's WorksheetFunction SUBSTITUTE is case-sensitive. Here is a version of the Tip's macro that permits the Find text to be case-insensitive. And it uses StrPtr(...) to recognize the difference between null replacement text and the Cancel button.
Sub FnR_HF2()
Const csTITLE As String = "Find/Replace Header/Footer"
Dim sWhat As String, sWith As String, msg As String, typ As Integer
msg = "Find what in header and footer:"
sWhat = InputBox(msg, csTITLE)
If sWhat = "" Then Exit Sub
typ = IIf(MsgBox("Match case?", vbYesNo, csTITLE) = vbYes, _
vbBinaryCompare, vbTextCompare)
msg = "Find (" & IIf(typ = vbBinaryCompare, "match", "ignore") _
& " case):" & vbLf & sWhat & vbLf & vbLf & "Replace with:"
sWith = InputBox(msg, csTITLE)
If StrPtr(sWith) = 0 Then Exit Sub 'user clicked Cancel
With ActiveSheet.PageSetup
.LeftHeader = Replace(.LeftHeader, sWhat, sWith, , , typ)
.CenterHeader = Replace(.CenterHeader, sWhat, sWith, , , typ)
.RightHeader = Replace(.RightHeader, sWhat, sWith, , , typ)
.LeftFooter = Replace(.LeftFooter, sWhat, sWith, , , typ)
.CenterFooter = Replace(.CenterFooter, sWhat, sWith, , , typ)
.RightFooter = Replace(.RightFooter, sWhat, sWith, , , typ)
End With
End Sub
Notice both versions ignore different first page and even page headers and footers.
2024-05-11 12:19:25
J. Woolley
Although having nothing to do with headers or footers, the ReplaceShapeText macro in My Excel Toolbox will find and offer to replace specified text in each shape on the active sheet or on all sheets of the active workbook. It includes logic to search grouped shapes plus options to match alphabetic case, whole words, and hidden shapes (such as Comments). Text formatting is preserved. This macro does not apply to chart labels but does apply to text boxes or other shapes added to charts. A log of the results can be copied to the clipboard then pasted into a worksheet or other document.
See https://sites.google.com/view/MyExcelToolbox/
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 © 2025 Sharon Parq Associates, Inc.
Comments