Add Country Code 1 Before Phone Numbers in WPS Spreadsheets
Question details
Add country code 1 before existing phone numbers stored as either text or numeric values.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user wants phone numbers to include a leading country code, with or without a space, while preserving the existing phone number format.
- Observed behavior
- Phone numbers may be stored as left-aligned text or as right-aligned numeric values with phone-number formatting.
Check whether the phone numbers are text or numbers before editing them. Text values usually align left by default, while numeric values usually align right; widen the column if you need to inspect the full value.
Use a formula to add country code 1 to text phone numbers
Use this method when phone numbers are stored as text and you need to create updated values that include the country code.
A formula combines the country code with the existing cell content. After confirming the results, you can copy and paste the formula results as values if you need to replace the original phone number column.
Click the column header next to the phone number column, then choose Home > Insert to create space for the updated phone numbers.
Select the first results cell, such as B4, and enter ="1 "&A4 to add 1 followed by a space before the phone number in A4. Use ="1"&A4 if you do not want a space.
Press Enter, then drag the fill handle from the lower-right corner of the formula cell down through the remaining rows.
Select the formula results, press Ctrl+C, then use Home > Paste > Paste Values to keep the displayed phone numbers without formulas.
Display country code 1 with a custom number format
Use this method when phone numbers are numeric values and you only need country code 1 to appear in the cell display.
Add and Format Phone Number Country Codes in WPS Spreadsheets
WPS Spreadsheets lets you prepend country codes with formulas and display numeric phone numbers with custom formats. You can quickly fill formulas down a list and retain a familiar spreadsheet workflow when working with contact data.
- 1. Open the phone number worksheet: Launch WPS Spreadsheets and open the workbook containing your phone number column.
- 2. Identify the value type: Click a phone number cell and check whether it behaves as text or as a numeric value with formatting.
- 3. Add a formula for text values: In an adjacent cell, enter ="1 "&A4 and press Enter, replacing A4 with the first phone number cell.
- 4. Apply a custom format for numeric values: Select numeric phone numbers, press Ctrl+1, choose Custom, and enter "1 "(000) 000-0000.
- 5. Review the final display: Widen the column and verify that every phone number shows the intended country code and spacing.
Frequently Asked Questions
How can I add 1 before every phone number without adding a space?
In an adjacent cell, use a formula such as ="1"&A4. Press Enter and drag the fill handle down to apply it to the rest of the list.
Will a custom phone number format change the actual cell value?
No. A custom format only changes the cell display. The underlying numeric value remains the same, so use a concatenation formula if the country code must become part of the stored value.
Why does the formula remove the formatting from my phone number?
Concatenating a numeric cell returns text and uses its underlying digits rather than its display format. Format the number first with TEXT, such as ="1 "&TEXT(A4,"(000) 000-0000"), or use a custom number format instead.
How do I replace the original phone numbers with the formula results?
Select the cells containing the formulas, press Ctrl+C, then choose Home > Paste > Paste Values over the original phone number cells.
What custom format should I use for a 10-digit U.S. phone number with country code 1?
Select the cells, press Ctrl+1, choose Number > Custom, and enter "1 "(000) 000-0000 in the Type field.
