How to Add a Hyphen After Numbers in Excel Cells
Question details
The user needs a way to append a hyphen to the end of numeric values in cells, leaving blank cells empty, so that the hyphens are retained when concatenating multiple cells.
- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Preparing and combining numerical cell values into a single text string for scheduling and tracking purposes.
- Observed behavior
- When adding a hyphen via Custom Number Formatting, the visual hyphen is lost upon concatenating the cells into a single string.
Identify the target column containing your numerical data and ensure you have an empty adjacent helper column where you can input the formula without overwriting existing data.
Use the IF and ISNUMBER Formula
This is the most reliable method because it converts the number and hyphen into an actual text string, ensuring the hyphen is preserved when combining cells.
While custom number formatting changes how a number looks, it does not change its underlying value. Because concatenation relies on the actual underlying values, visual formatting is stripped away during the process. Using a formula permanently attaches the hyphen to the value as text.
Click on an empty cell adjacent to your first numeric value. For example, if your first number is in cell A1, click on cell B1.
Type the formula =IF(ISNUMBER(A1),A1&"-","") into the formula bar and press Enter. This checks if A1 is a number; if it is, it appends a hyphen. If A1 is blank or contains text, it returns an empty string.
Click on cell B1 again, hover your cursor over the bottom-right corner until it turns into a plus icon (the fill handle), and double-click or drag it down to apply the formula to the rest of the column.
Use the new helper column for your scheduling tracking. In your destination cell, use the concatenate operator, such as =B1&C1, and the hyphens will now successfully appear.
Easily Manage Formulas and Concatenation in WPS Office
WPS Spreadsheet provides powerful formula calculation and text formatting features, making it effortless to add hyphens, combine text, and manage complex schedules.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your scheduling data.
- 2. Insert the text formula: Select a blank helper cell and type =IF(ISNUMBER(A1),A1&"-",""), then press Enter.
- 3. Fill down the data: Drag the fill handle to apply the formula to all relevant cells in your dataset.
- 4. Combine the cells: Use =B1&C1 or the CONCAT function in your target cell to merge the data while keeping the hyphen intact.
Frequently Asked Questions
Why do my added hyphens disappear when merging or concatenating cells?
If you added hyphens using Custom Number Formatting, Excel only alters the visual display. Concatenation and cell merging rely on the cell's underlying value, ignoring visual styles. To preserve hyphens during these actions, you must use a text formula like A1&"-".
Can I add a hyphen before a number instead of after?
Yes. You can modify the formula to place the hyphen at the beginning of the string. Change the formula to =IF(ISNUMBER(A1),"-"&A1,"") to prepend the hyphen instead of appending it.
Will this formula add hyphens to text cells as well?
No. The ISNUMBER part of the formula strictly checks if the cell contains a numeric value. If the cell contains text or is completely blank, the formula skips it and returns an empty string.
