When cleaning up messy data in Microsoft Excel, you often need to swap out specific characters within a cell. If you want to change every instance of the word “Red” to “Blue,” you can use the familiar Find & Replace tool (Ctrl+H) or the SUBSTITUTE function.
However, what if the text you want to remove isn’t a specific word, but rather a specific location? For example, what if you need to remove the first three digits of a phone number, regardless of what those digits are? This is where the REPLACE function becomes essential.
Understanding the Syntax
The REPLACE function swaps out a string of characters based entirely on their physical position within the text, not on what the characters actually say.
Syntax: =REPLACE(old_text, start_num, num_chars, new_text)
- old_text: The cell containing the messy data you want to fix.
- start_num: The exact position (counting from left to right) where you want the replacement to begin. (The first letter is 1, the second is 2, etc.)
- num_chars: How many characters you want to delete/replace, starting from that position.
- new_text: The new text you want to insert into that empty space. (Must be wrapped in quote marks).
Example 1: Updating Product Codes
Imagine you have a list of product IDs in Column A. They look like this: PRD-2023-99X. The company has updated the year to 2024, and you need to update thousands of these codes.
You know the year “2023” always starts at the 5th character, and it is exactly 4 characters long.
- Click on an empty cell next to the first product code (e.g., cell B2).
- Type the formula:
=REPLACE(A2, 5, 4, "2024") - Press Enter.
Excel will look at cell A2, count over 5 characters, delete the next 4 characters, and immediately drop “2024” into the gap. The result will be PRD-2024-99X. You can then drag this formula down to fix the entire column instantly.
Example 2: Masking Sensitive Data
The REPLACE function is fantastic for security, such as masking credit card numbers or Social Security Numbers before emailing a spreadsheet.
If you have a 9-digit Social Security Number in cell A2 (e.g., 123456789) and you want to replace the first 5 digits with asterisks so it looks like *****6789, you would use this formula:
=REPLACE(A2, 1, 5, "*****")
This tells Excel: Look at A2, start at character number 1, delete 5 characters, and put five asterisks in their place.
How to Simply Delete Text
You can also use REPLACE to completely remove characters without substituting anything back in.
If you have a phone number formatted as (555) 123-4567 and you want to strip out the area code and the space, you want to delete the first 6 characters.
You would write: =REPLACE(A2, 1, 6, "")
By using empty quote marks ("") as your new_text, Excel deletes the characters and inserts absolutely nothing, leaving you with just 123-4567.