How to Remove Hyphens from Excel Numbers Without Losing Leading Zeros in WPS Office
Question details
Remove hyphens from values such as 001-1111-11111 while preserving all leading zeros across an entire Excel column.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user has a column of hyphenated values that include leading zeros and wants to clean the entire column without turning the values into numbers.
- Observed behavior
- Manual deletion or number formatting can cause Excel to interpret the values as numbers and drop the leading zeros, such as changing 001-1111-11111 into 111111111.
Make sure the hyphenated values are in a column and identify an empty neighboring column for the formula results. If you need to replace the original data, plan to copy the results and use Paste Special > Values.
Use the SUBSTITUTE formula to remove hyphens and keep leading zeros
This is the recommended non-destructive method for cleaning an entire Excel column while keeping the resulting values as text.
The SUBSTITUTE function removes every hyphen from the source text. Because the formula returns text, Excel does not re-evaluate the cleaned value as a number, so leading zeros remain intact.
Select the empty cell next to the first value; for example, select E2 if your original values start in D2.
Type =SUBSTITUTE(D2,"-","") and press Enter. Replace D2 with the actual first cell that contains the hyphenated value.
Use the fill handle at the lower-right corner of the formula cell and drag down to the last data row, or double-click the fill handle to fill automatically.
Select the formula results, press Ctrl+C, right-click the original data range, choose Paste Special > Values, and press Enter.
Remove Hyphens from Excel Numbers and Keep Leading Zeros with WPS Office
Use the SUBSTITUTE formula in WPS Office Excel to strip hyphens from an entire column while keeping values as text, so leading zeros are preserved. Then paste the cleaned results as values to finalize the data.
- 1. Open the workbook in WPS Office Excel: Launch WPS Office, open the Excel file containing the hyphenated values, and select the cell where the cleaned value should appear.
- 2. Apply the SUBSTITUTE formula: Enter =SUBSTITUTE(D2,"-","") in the target cell, using the first source cell in your data, and press Enter.
- 3. Fill and paste values: Drag the fill handle down the column, copy the results with Ctrl+C, then right-click the original cells and choose Paste Special > Values.
Frequently Asked Questions
Why does Excel remove leading zeros when I delete hyphens manually?
If a value such as 001-1111-11111 becomes 001111111111, Excel may treat it as a number and drop the leading zeros. Using =SUBSTITUTE(D2,"-","") returns text, which preserves the zeros.
Can I remove hyphens from the entire column without typing a formula in each row?
Yes. Enter the SUBSTITUTE formula in the first row, then double-click or drag the fill handle down the column to apply it to all remaining rows.
How do I replace the original hyphenated data with the cleaned values?
Copy the formula results with Ctrl+C, select the original data range, right-click, choose Paste Special > Values, and press Enter.
Does this method work if the values are numbers with custom formatting?
It is most reliable when the original values are text. If the hyphen is only a display format rather than part of the stored value, format the cells as text or import the data as text before using SUBSTITUTE.
Will SUBSTITUTE remove all hyphens in a cell?
Yes. SUBSTITUTE with "-" as the old_text removes every hyphen in the cell unless you specify a particular instance number.
