Convert Parenthesized Phone Numbers to Digits with a Leading 1 in WPS Spreadsheets
Question details
The user needs to convert formatted phone numbers such as (916) 123-4567 into plain 11-digit strings with a leading 1, for example 19161234567, in WPS Spreadsheets.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Cleaning or preparing a list of phone numbers for dialing, import, or consistent storage.
- Observed behavior
- The phone numbers currently contain parentheses, spaces, and hyphens and need to become digits only with a leading 1.
Open your workbook in WPS Spreadsheets and confirm the phone numbers are in a single column, such as column A. If you plan to replace the original numbers, have a helper column available for the formula results.
Use Nested SUBSTITUTE Formulas to Clean Phone Numbers and Add a Leading 1
Use this solution when you need one reusable formula to strip parentheses, spaces, and hyphens and prefix every phone number with 1.
The formula works by nesting SUBSTITUTE functions to remove each unwanted character, then concatenates "1" to the cleaned result.
In WPS Spreadsheets, click the first helper cell next to your phone number, such as B1.
Type ="1"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ","") and press Enter.
Drag the fill handle from B1 down through the remaining phone number rows, or double-click the fill handle to fill the adjacent range.
Select the formula results, press Ctrl+C, select the original phone number cells, and choose Paste Special > Values to paste the converted numbers.
Convert Phone Number Formatting Directly in WPS Spreadsheets
WPS Spreadsheets can perform this phone number cleanup in one formula without extra tools. Enter the SUBSTITUTE formula, fill it down, and paste values back over the original list while keeping Microsoft Excel format compatibility.
- 1. Open WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the workbook that contains the phone numbers.
- 2. Enter the formula in a helper column: Click the first blank cell next to your phone number and enter ="1"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")," ","").
- 3. Copy the formula down: Press Enter, then drag the fill handle down the column to apply the formula to all phone number records.
- 4. Paste values if replacing originals: Copy the converted results, select the original phone number cells, right-click, and choose Paste Special > Values to keep the plain digits.
Frequently Asked Questions
What does this formula do to a phone number like (916) 123-4567?
It removes the parentheses, space, and hyphen with nested SUBSTITUTE functions and adds 1 to the beginning, returning 19161234567.
How do I replace the original phone numbers with the converted values?
Copy the helper column results, select the original phone number cells, right-click, and choose Paste Special > Values. You can then delete the helper column if it is no longer needed.
Will this formula work if the phone number already starts with 1?
It will still add a leading 1, so a number like 1 (916) 123-4567 would become 119161234567. Remove the "1"& part if your data already contains the country code.
How can I convert phone numbers with periods, slashes, or other characters?
Add another SUBSTITUTE function for each additional character, or modify the formula to target those specific characters before adding the leading 1.
