Find and Replace in Header and Footer in Excel

If you want to replace text in Excel, you can go to Home >> Editing >> Find & Select >> Replace (Ctrl + H).

It’s not very complicated. The problem is that it doesn’t look for text in headers and footers, so there is no way to replace it using this method. If you want to replace the text there, you have to use the VBA code.

Insert header or footer

Let’s insert three headers to Excel (Insert >> Text >> Header & Footer).

Now, you have three headers inserted (left, center and right). You can click each of them and enter text.

If you scroll your page down, you will notice that there are also three footers there. Here, you can enter text in a similar way.

Replace headers or footers in the current sheet

The following code is going to replace text in the center header on the current worksheet. “Center header” is going to be “Center header 1”.

This is what it looks like in Excel.

In a similar way, you can do it for a footer.

I showed you how you can change the text for the center header. You can also do it for LeftHeader and RightHeader, or all of them at once.

Replace headers in all sheets

(Change code, the previous one worked for all headers)

So far, our code worked for a single worksheet

Adding InputBox – the easy way

In the previous example, we added two strings inside our code. The disadvantage of this is that we have to change it each time if we want to use different strings.

The other way to do our script is to add an input box.

Each time you start a program, you will be asked for two strings. Each string for each input box.

The disadvantage of this method is that it’s not the most elegant way. The next example shows how to do it using a single form.

Adding InputBox – the bit harder way

In order to create this form, press Alt + F11 to open the VBA editor. Choose Insert >> UserForm.

Change the name to frmReplaceHeader and the caption to Replace Header.

From a Toolbox (View >> Toolbox) insert:

Two labels: lblFindWhat and lblReplaceWith.

Two textboxes: tbxFindWhat and tbxReplaceWith.

One command button: btnReplaceAll.

Change captions and place them in the same way as you can see in the image below.

Double-click the “Replace All” button to generate the private procedure. The code you type there will be executed each time you click the “Replace All” button.

Code explanation:

In this code, in the Replace function, there is Me.tbxFindWhat.Value and Me.tbxReplaceWith.Value. Me refers to user form, then it takes value from two textboxes and the rest of the code works the same way as before.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba