Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. 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, Excel in Microsoft 365, and 2021
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, Excel in Microsoft 365, and 2021. 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, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Find and Replace in Headers.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Excel provides some great tools for finding information in a worksheet or a workbook. Changing the default settings used ...
Discover MoreTired of the Find and Replace dialog box blocking the view of your worksheet when you are searching for information? Do ...
Discover MoreFinding and replacing information in a worksheet is easy. Finding and replacing in other objects (such as text boxes or ...
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 © 2024 Sharon Parq Associates, Inc.
Comments