How to change or replace multiple cell name in Microsoft Excel chronologically?

In Microsoft Excel, you can use defined names to refer to cells, ranges, formulas, or constants. Defined names can make your formulas easier to read and understand, as well as reduce errors and simplify maintenance.

You can create, edit, or delete defined names using the Name Manager dialog box, which you can access from the Formulas tab on the ribbon.

Sometimes, you may want to change or replace multiple cell names in a worksheet at once, for example, to make them more descriptive or consistent. There are a few ways to do this, depending on your needs and preferences.

Procedures

Method 1: Use the SUBSTITUTE function

One way to change or replace multiple cell names in Excel is to use the SUBSTITUTE function, which replaces a specific text within a text string with another text.

The syntax of the SUBSTITUTE function is:

SUBSTITUTE(text, old_text, new_text, [instance_num])

Where:

• text is the text or cell reference that contains the text you want to replace.
• old_text is the text you want to replace.
• new_text is the text you want to replace old_text with.
• instance_num is an optional argument that specifies which occurrence of old_text you want to replace. If omitted, all occurrences are replaced.

To change or replace multiple cell names with the SUBSTITUTE function, you can write a few individual functions to replace each old name with a new one, and then nest those functions one into another, so that each subsequent SUBSTITUTE uses the output of the previous SUBSTITUTE to look for the next name.

For example, suppose you have a list of locations in column A, and you want to replace the abbreviated country names (such as FR, UK, and USA) with full names. You can enter the old names in D2:D4 and the new names in E2:E4, and then use this formula in B2:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, D2, E2), D3, E3), D4, E4)

This formula will replace the first occurrence of “FR” in A2 with “France”, then the first occurrence of “UK” with “United Kingdom”, and then the first occurrence of “USA” with “United States”. You can copy the formula down to the other cells in column B to get the desired results.

The advantage of this method is that it is easy to implement and supported in all Excel versions. The drawback is that it is best to be used for a limited number of find/replace values, as nested functions can become difficult to manage.

Method 2: Use the XLOOKUP function

Another way to change or replace multiple cell names in Excel is to use the XLOOKUP function, which returns a value from a range or array based on an exact or approximate match.

The syntax of the XLOOKUP function is:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Where:

• lookup_value is the value you want to look up.
• lookup_array is the range or array that contains the values you want to match.
• return_array is the range or array that contains the values you want to return.
• if_not_found is an optional argument that specifies what to return if no match is found. If omitted, #N/A is returned.
• match_mode is an optional argument that specifies the type of match to perform. If omitted, an exact match is performed.
• search_mode is an optional argument that specifies the direction of the search. If omitted, a search from first to last is performed.

To change or replace multiple cell names with the XLOOKUP function, you can enter the old names in one column and the new names in another column, and then use this formula in a third column:

=XLOOKUP(A2, D2:D4, E2:E4, A2)

This formula will look up the value in A2 in the range D2:D4, and return the corresponding value from the range E2:E4. If no match is found, it will return the original value in A2. You can copy the formula down to the other cells in the third column to get the desired results.

The advantage of this method is that it is simpler and more flexible than the SUBSTITUTE function, as it can handle any number of find/replace values and does not require nesting. The drawback is that it is only available in Excel 365 and Excel for Microsoft 365.

Method 3: Use VBA code

A third way to change or replace multiple cell names in Excel is to use VBA code, which is a programming language that allows you to automate tasks and customize Excel’s functionality.

To change or replace multiple cell names with VBA code, you can use the Name object, which represents a defined name for a range, constant, formula, or table.

For example, suppose you have a worksheet with some named ranges, and you want to replace the word “comp” with “cons” in all the names. You can use this VBA code to do that:

Sub RangeRename()
    Dim N As Name
    For Each N In ActiveWorkbook.Names
        N.Name = WorksheetFunction.Substitute(N.Name, "comp", "cons")
    Next N
End Sub

This code will loop through all the names in the active workbook, and use the SUBSTITUTE function to replace “comp” with “cons” in each name.

The advantage of this method is that it is more powerful and versatile than the formula methods, as it can handle complex scenarios and logic. The drawback is that it requires some programming skills and knowledge of VBA.

Explanation

To help you understand the procedures better, here is a comprehensive explanation of each method, including the basics and a detailed example with real numbers.

Method 1: Use the SUBSTITUTE function

The SUBSTITUTE function is a text function that replaces a specific text within a text string with another text. It can be used to change or replace multiple cell names in Excel by writing a few individual functions to replace each old name with a new one, and then nesting those functions one into another.

The basic steps to use the SUBSTITUTE function are:

• Enter the old names and the new names in two separate columns, such as column D and column E.
• In another column, such as column B, enter the formula =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, D2, E2), D3, E3), D4, E4), where A2 is the cell that contains the original name, D2:E4 is the range that contains the old names and the new names, and D2, E2, D3, E3, D4, and E4 are the individual cells that contain the old name and the new name pairs.
• Copy the formula down to the other cells in the column.

The formula works by replacing the first occurrence of the old name in the cell with the new name, and then using the output of that function as the input for the next function, and so on, until all the old names are replaced with the new names.

For example, suppose you have a list of locations in column A, and you want to replace the abbreviated country names (such as FR, UK, and USA) with full names. You can enter the old names in D2:D4 and the new names in E2:E4, and then use this formula in B2:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, D2, E2), D3, E3), D4, E4)

This formula will do the following:

• Replace the first occurrence of “FR” in A2 with “France”, and return “Paris, France”.
• Replace the first occurrence of “UK” in “Paris, France” with “United Kingdom”, and return “Paris, France”.
• Replace the first occurrence of “USA” in “Paris, France” with “United States”, and return “Paris, France”.

The final result is “Paris, France”, which is the desired output.

You can copy the formula down to the other cells in column B to get the desired results for the other locations.

Here is an Excel table that shows the example:

Method 2: Use the XLOOKUP function

The XLOOKUP function is a lookup and reference function that returns a value from a range or array based on an exact or approximate match. It can be used to change or replace multiple cell names in Excel by entering the old names and the new names in two separate columns, and then using the function to look up the old name in the cell and return the corresponding new name.

The basic steps to use the XLOOKUP function are:

• Enter the old names and the new names in two separate columns, such as column D and column E.
• In another column, such as column B, enter the formula `=XLOOKUP(A2, D

Method 3: Use VBA code

VBA is a programming language that allows you to automate tasks and customize Excel’s functionality. It can be used to change or replace multiple cell names in Excel by using the Name object, which represents a defined name for a range, constant, formula, or table.

The basic steps to use VBA code are:

• Press Alt + F11 to open the Visual Basic Editor.
• Insert a new module from the Insert menu.
• Write a Sub procedure that loops through all the names in the active workbook, and uses the Substitute function to replace the old name with the new name in each name.
• Run the Sub procedure from the Run menu or by pressing F5.

The code works by accessing the Names collection, which contains all the defined names in the workbook, and using the Name property to get or set the name of each Name object. The Substitute function is used to replace the old name with the new name in the name string.

For example, suppose you have a worksheet with some named ranges, and you want to replace the word “comp” with “cons” in all the names. You can use this VBA code to do that:

Sub RangeRename()
    Dim N As Name
    For Each N In ActiveWorkbook.Names
        N.Name = WorksheetFunction.Substitute(N.Name, "comp", "cons")
    Next N
End Sub

This code will do the following:

• Declare a variable N of type Name.
• Loop through each Name object in the Names collection of the active workbook.
• Use the Name property to get the current name of each Name object, and use the Substitute function to replace “comp” with “cons” in the name string.
• Use the Name property to set the new name of each Name object.

The final result is that all the names that contain “comp” will be changed to “cons”.

Here is an Excel table that shows the example: