How to Remove Hyphens from Social Security Numbers in Excel
Question details
The user needs to remove hyphens from Social Security Numbers (SSNs) or similarly formatted text strings to leave only the raw digits.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up and standardizing data formatting by stripping out hyphen characters from standardized numeric strings.
- Observed behavior
- The data contains hyphens that need to be completely deleted from the cells to create a continuous string of numbers.
Ensure that the cells containing your Social Security Numbers are formatted as 'Text' before you remove the hyphens, as converting them to standard numbers might cause Excel to accidentally delete any leading zeros.
Use Find and Replace to Remove Hyphens
The quickest and most efficient way to remove hyphens from multiple cells at once is by using Excel's built-in Find and Replace tool.
This method alters the data in your current cells directly. If you need to keep a copy of the original hyphenated numbers, make sure to duplicate your column before proceeding.
Click and drag to highlight the range of cells containing the Social Security Numbers you want to modify.
Press the 'Ctrl + H' keyboard shortcut to immediately open the Find and Replace dialog box on your screen.
Type a single hyphen (-) in the 'Find what' field, leave the 'Replace with' field completely empty, and click the 'Replace All' button.
Use the SUBSTITUTE Function
If you want to keep the original data intact and display the unhyphenated numbers in a separate column, use the SUBSTITUTE formula.
Easily Manage and Format Data with WPS Spreadsheet
WPS Spreadsheet provides powerful data processing tools, including Find and Replace and advanced text formulas, completely free. It is a highly compatible and lightweight alternative to Microsoft Excel.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing the Social Security Number data.
- 2. Select your data range: Highlight the specific column or cells where you want to remove hyphens.
- 3. Use Find and Replace: Press Ctrl+H, enter a hyphen (-) in 'Find what', leave 'Replace with' empty, and click Replace All.
Frequently Asked Questions
Will removing hyphens from SSNs delete leading zeros?
If your cells are formatted as General or Number, the application might drop the leading zeros when the hyphens are removed because it treats the result as a standard math number. To prevent this, format the column as Text before performing the Find and Replace operation.
Can I remove both hyphens and spaces at the same time?
The Find and Replace dialog only processes one specific character string at a time. To remove both, you will need to run the tool twice: once for the hyphen, and once for the space. Alternatively, you can use a nested formula: =SUBSTITUTE(SUBSTITUTE(A2, "-", ""), " ", "").
How do I format the numbers back to an SSN format later?
If you have a continuous 9-digit number and want it displayed as an SSN again, select the cells, press Ctrl+1 to open Format Cells, go to the Special category, and select Social Security Number.
Does the Find and Replace shortcut work in WPS Spreadsheet?
Yes, the Ctrl+H shortcut and the Find and Replace dialog function identically in WPS Spreadsheet, allowing you to quickly clean up formatted values just like you would in Microsoft Excel.
