If you inherit a massive Excel spreadsheet containing thousands of product codes or employee IDs, you may discover a recurring formatting error. For example, perhaps every single product code in the system incorrectly contains a hyphen (e.g., PRD-994) when it should actually contain an underscore (e.g., PRD_994). Instead of manually retyping thousands of cells, you can use the SUBSTITUTE() function to surgically target and swap specific characters.
How the SUBSTITUTE Function Works
The SUBSTITUTE() function searches a target cell for a specific string of text, and mathematically replaces it with a brand new string of text. It is incredibly precise and, unlike the generic “Find and Replace” tool, it allows you to keep the original broken data safely intact in one column while generating the corrected data in an adjacent column.
The syntax looks like this: =SUBSTITUTE(text, old_text, new_text)
Step-by-Step Instructions
- Open your Excel spreadsheet.
- Suppose cell A1 contains the broken product code: PRD-994.
- Click on an empty cell next to it (like B1) where you want the corrected code to appear.
- Type the following formula:
=SUBSTITUTE(A1, "-", "_") - Press Enter.
Cell B1 will instantly display the corrected string: PRD_994. You can then use the fill handle (the small green square at the bottom right corner of cell B1) to drag the formula down the entire column, instantly fixing thousands of product codes in less than a second.
How to Remove Characters Entirely
The SUBSTITUTE() function is not just for swapping characters; it is also the perfect tool for completely deleting annoying characters from a dataset.
For example, if you have a column of phone numbers formatted as (555) 123-4567, and your database requires raw numbers without parentheses, you can use SUBSTITUTE to replace the parenthesis with “nothing.”
To delete the open parenthesis, you simply provide a pair of double-quotes with absolutely nothing between them as the new_text argument.
=SUBSTITUTE(A1, "(", "")
The output will instantly strip the character away, leaving you with clean, raw data.