Formula Errors

How to Add an Underscore and Replace Hyphens in Excel Formulas

Nimra Malik Sep 28, 2026 870 views

Question details

The user needs an Excel formula to simultaneously add an underscore to the beginning of a cell's text and replace all existing hyphens with underscores, while avoiding #NAME? errors.

Excel Formula to Add an Underscore and Replace Hyphens

Product: Excel
Device & OS: not provided
Scenario: Formatting text strings by changing separator characters and appending a specific prefix.
Observed behavior: Attempting to modify text data using formulas sometimes triggers a #NAME? error due to syntax or spelling mistakes in the function name.

Before you start

Verify your system's regional settings to ensure you are using the correct list separator (comma or semicolon) for your formulas, and check that your source data does not contain unintended spaces around the hyphens.

Solution 1Recommended

Use the SUBSTITUTE Function with Text Concatenation

Combine the SUBSTITUTE function with an ampersand (&) to dynamically add a prefix and replace specific characters in a single formula.

The SUBSTITUTE function is designed to replace specific text within a string. By joining it with a text string using the ampersand operator, you can simultaneously prepend new characters to your data.

Select an empty cell

Click on an empty cell adjacent to your data where you want the new formatted text to appear.

Enter the SUBSTITUTE formula

Type the formula ="_"&SUBSTITUTE(A1,"-","_") into the formula bar, assuming your original text is in cell A1.

Apply the formula

Press Enter to execute the formula. The text will now have a leading underscore, and all hyphens will be changed to underscores.

Batch apply to the column

Click the small square at the bottom-right corner of the cell (fill handle) and drag it down to apply the formula to the rest of the column.

Use the SUBSTITUTE Function with Text Concatenation

Troubleshooting #NAME? Errors: If you encounter a #NAME? error, carefully check that the function is spelled exactly as 'SUBSTITUTE'. Also, ensure that all text strings and characters (like "-" and "_") are enclosed in double quotation marks.

Advanced Spreadsheet Editor

Master Data Formatting with WPS Spreadsheet

WPS Spreadsheet provides robust support for text manipulation functions like SUBSTITUTE, ensuring seamless formula execution and error-free data formatting.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing the text you need to format.
2. Input the text function: Select the target cell and input the formula ="_"&SUBSTITUTE(A1,"-","_").
3. Drag to fill: Press Enter, then use the drag-fill handle to copy the formatting rule across all necessary rows.

100% compatible with Microsoft Excel formulas and data formatsBuilt-in error checking to instantly identify syntax issues like #NAME?Lightweight application with high performance for handling large datasetsFamiliar interface for quick and easy formula application

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a #NAME? error when using the SUBSTITUTE function?

A #NAME? error typically occurs if the function name (SUBSTITUTE) is misspelled or if you forgot to place quotation marks around the text arguments. Excel treats unquoted text as an undefined named range.

Can I replace multiple different characters in one formula?

Yes, you can nest multiple SUBSTITUTE functions together. For example, =SUBSTITUTE(SUBSTITUTE(A1, "-", "_"), " ", "") will replace all hyphens with underscores and completely remove any spaces in the same cell.

Is the SUBSTITUTE function case-sensitive?

Yes, the SUBSTITUTE function is case-sensitive. If you are replacing alphabetical characters instead of symbols like hyphens, you must match the exact upper or lower case of the target text.

Excel & Data Problems