Excel Tips: Changing or Replacing Cell Names Chronologically

Sometimes, you may want to change or replace the names of multiple cells in a worksheet based on a certain pattern or order. For example, you may have a column of cells with the same name, such as “transfer”, and you want to rename them as “transfer 1”, “transfer 2”, “transfer 3”, and so on. Or, you may have a column of cells with different names, such as “A1”, “B2”, “C3”, and you want to rename them as “A”, “B”, “C”, and so on.

There are different ways to achieve this task in Microsoft Excel, depending on your specific situation and preference. In this article, we will introduce three methods that you can use to change or replace multiple cell name in Microsoft Excel chronologically:

• Method 1: Using the SUBSTITUTE function
• Method 2: Using the XLOOKUP function
• Method 3: Using VBA code

Method 1: Using the SUBSTITUTE function

The SUBSTITUTE function is a built-in function in Excel that allows you to replace a specific text within a text string with another text. You can use this function to change or replace multiple cell name in Excel by nesting multiple SUBSTITUTE functions within one another.

The syntax of the SUBSTITUTE function is:

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

Where:

• text is the text string that contains the text you want to replace.
• old_text is the text that you want to replace within the text string.
• new_text is the text that you want to replace the old text with.
• [instance_num] is an optional argument that specifies which occurrence of the old text you want to replace. If omitted, all occurrences of the old text will be replaced.

To use the SUBSTITUTE function to change or replace multiple cell name in Excel chronologically, you need to follow these steps:

1. Prepare a list of the old names and the new names that you want to use for the cells. For example, if you want to rename the cells in column B chronologically same as column A but without losing the blank cells, you can prepare a list like this:

2. Enter the following formula in the first cell of the column where you want to display the new names. In this example, we enter the formula in cell C2:

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

Where:

• B2 is the cell that contains the old name that you want to change.
• D2 and E2 are the cells that contain the old name and the new name for the first replacement, respectively.
• D3 and E3 are the cells that contain the old name and the new name for the second replacement, respectively.
• D4 and E4 are the cells that contain the old name and the new name for the third replacement, respectively.

3. Drag the fill handle of the cell C2 down to fill the rest of the cells in the column with the same formula. The formula will automatically adjust the cell references and replace the old names with the new names chronologically.

The result will look like this:

Method 2: Using the XLOOKUP function

The XLOOKUP function is a new function in Excel 365 that allows you to look up a value in a range and return a corresponding value from another range. You can use this function to change or replace multiple cell name in Excel by matching the old names with the new names in a lookup table.

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 that you want to look up in the lookup array.
• lookup_array is the range of cells that contains the values that you want to match with the lookup value.
• return_array is the range of cells that contains the values that you want to return based on the lookup value.
• [if_not_found] is an optional argument that specifies what to return if the lookup value is not found in the lookup array. If omitted, the function will return #N/A error.
• [match_mode] is an optional argument that specifies how to match the lookup value with the values in the lookup array. If omitted, the function will perform an exact match. The possible values are:
  • 0 or omitted: Exact match. The function will look for a value that is exactly equal to the lookup value.
  • -1: Exact match or next smaller. The function will look for a value that is exactly equal to or smaller than the lookup value, and return the closest match.
  • 1: Exact match or next larger. The function will look for a value that is exactly equal to or larger than the lookup value, and return the closest match.
  • 2: Wildcard match. The function will look for a value that matches the lookup value with wildcard characters, such as ? for any single character and * for any sequence of characters.
• [search_mode] is an optional argument that specifies the direction and order of the search. If omitted, the function will perform a binary search from the first to the last item in the lookup array. The possible values are:
  • 1 or omitted: Binary search in ascending order. The function will assume that the lookup array is sorted in ascending order and perform a binary search from the first to the last item.
  • -1: Binary search in descending order. The function will assume that the lookup array is sorted in descending order and perform a binary search from the last to the first item.
  • 2: Linear search from first to last. The function will perform a linear search from the first to the last item in the lookup array, regardless of the order.
  • -2: Linear search from last to first. The function will perform a linear search from the last to the first item in the lookup array, regardless of the order.

To use the XLOOKUP function to change or replace multiple cell name in Excel chronologically, you need to follow these steps:

1. Prepare a lookup table that contains the old names and the new names that you want to use for the cells. For example, if you want to rename the cells in column B chronologically same as column A but without losing the blank cells, you can prepare a lookup table like this:

2. Enter the following formula in the first cell of the column where you want to display the new names. In this example, we enter the formula in cell C2:

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

Where:

• B2 is the cell that contains the old name that you want to change.
• D2:D4 is the range of cells that contains the old names in the lookup table.
• E2:E4 is the range of cells that contains the new names in the lookup table.
• B2 is the value that you want to return if the old name is not found in the lookup table. In this case, we want to return the same value as the old name.

3. Drag the fill handle of the cell C2 down to fill the rest of the cells in the column with the same formula. The formula will automatically adjust the cell references and replace the old names with the new names chronologically.

The result will look like this:

Method 3: Using VBA code

VBA (Visual Basic for Applications) is a programming language that allows you to create macros and custom functions in Excel. You can use VBA code to change or replace multiple cell name in Excel by looping through the cells and applying the desired logic.

To use VBA code to change or replace multiple cell name in Excel chronologically, you need to follow these steps:

1. Press Alt + F11 to open the Visual Basic Editor window.

2. In the Project Explorer pane, right-click on the workbook name and select Insert > Module. This will create a new module where you can write your VBA code.
3. In the code window, copy and paste the following code:

Sub RenameCells()
    'Declare variables
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim oldName As String
    Dim newName As String
    Dim counter As Long
    
    'Set the worksheet and the range to work with
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set rng = ws.Range("B2:B10")
    
    'Initialize the old name and the counter
    oldName = "transfer"
    counter = 1
    
    'Loop through each cell in the range
    For Each cell In rng
        'Check if the cell is not blank
        If cell.Value <> "" Then
            'Check if the cell contains the old name
            If cell.Value = oldName Then
                'Create the new name by adding the counter
                newName = oldName & " " & counter
                'Replace the cell value with the new name
                cell.Value = newName
                'Increment the counter
                counter = counter + 1
            End If
        End If
    Next cell
End Sub

4. Save and close the Visual Basic Editor window.
5. Go back to the worksheet and select any cell.
6. Press Alt + F8 to open the Macro dialog box.
7. Select the macro name “RenameCells” and click Run. The macro will run and change or replace the cell names in column B chronologically.

The result will look like this: