How to Trim Country Codes from Phone Numbers in Excel

Cleaning international phone numbers in Excel is a tedious bottleneck, especially when inconsistent country prefixes disrupt CRM uploads. While enterprise data initiatives often secure dedicated IT funding sources for automated middleware, smaller teams must resolve these format discrepancies internally. Mastering a targeted Excel formula grants immediate operational efficiency, bypassing costly external software.

As a crucial stipulation, your database must have consistent local digit lengths-for instance, 10 digits for North American lines. In this scenario, applying =RIGHT(A2, 10) to a number like +12025550143 successfully isolates the core subscriber number.

Below, we will outline advanced formula variations to dynamically manage diverse international prefixes and special characters.

Introduction

Managing phone number databases is a common administrative challenge. When exporting contacts from CRMs like HubSpot, Salesforce, or various e-commerce platforms, phone numbers often arrive with international country codes (such as +1 for the USA, +44 for the UK, or +61 for Australia).

While country codes are vital for international dialing, they can complicate local marketing campaigns, SMS delivery systems, and internal reporting. Standardizing these numbers by stripping the country codes is a frequent task for data analysts. This comprehensive guide details several Excel formulas to trim country codes from phone numbers, ranging from simple fixed-length solutions to complex, dynamic scenarios.

Step 1: Clean Your Data First

Before applying any formula to strip country codes, you must clean your phone number data. Raw datasets often contain a mix of spaces, hyphens, parentheses, and plus signs. For example, +1 (555) 123-4567, +44-20-7946-0192, and +61 2 9876 5432 contain non-numeric formatting characters that can break logical formulas.

To strip these characters and standardize your numbers to digits only, you can nest multiple SUBSTITUTE functions. Assuming your raw phone number is in cell A2, use the following formula in cell B2:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "+", ""), " ", ""), "-", ""), "(", ""), ")", "")

This nested formula performs the following tasks in order:

• Removes the plus sign (+)
• Removes all spaces
• Removes hyphens (-)
• Removes opening parentheses (()
• Removes closing parentheses ())

With a clean string of numbers (e.g., 15551234567 or 442079460192), trimming the prefix country code becomes significantly easier.

Method 1: Trimming Fixed-Length Country Codes

If your dataset contains phone numbers from a single known country, the country code length will be constant. For instance, if you are cleaning North American Numbering Plan (NANP) numbers where the country code is always a single digit (1), you can easily strip it.

Using the RIGHT and LEN Functions

To strip the first character from a text string, you can determine the total length of the string using LEN, subtract the length of the country code, and extract that remaining number of characters from the right using RIGHT.

If your cleaned phone number is in cell B2 and you want to remove the first digit (the 1):

=RIGHT(B2, LEN(B2) - 1)

Using the REPLACE Function

An alternative and cleaner formula is the REPLACE function. This function allows you to replace a specific starting portion of a text string with nothing ("").

=REPLACE(B2, 1, 1, "")

How it works: This formula looks at cell B2, starts at character position 1, covers a length of 1 character, and replaces it with an empty string.

Method 2: Handling Variable-Length Country Codes with Delimiters

If your data spans multiple countries, country codes will vary in length (e.g., +1 for Canada, +44 for the United Kingdom, +966 for Saudi Arabia). If your raw numbers use consistent separators like spaces or hyphens to isolate the country code (e.g., +44 7911 123456), you can locate this separator to trim the code.

Using MID and FIND

By finding the position of the first space or hyphen, you can instruct Excel to return only the text that follows it. To find the country code separator and extract the rest of the string, use this formula:

=MID(A2, FIND(" ", A2) + 1, LEN(A2))

How it works:

• FIND(" ", A2) locates the character index of the first space.
• We add +1 to move the starting point to the character immediately following the space.
• MID extracts the text starting from that position up to the full length of the string (LEN(A2)).

If your dataset uses hyphens instead of spaces, simply swap the space character with a hyphen in the formula:

=MID(A2, FIND("-", A2) + 1, LEN(A2))

Method 3: Dynamic Trimming Without Delimiters (Advanced Lookup)

The most complex scenario involves phone numbers from multiple countries that are completely run together without any spaces, dashes, or separators (e.g., 442079460192 and 15551234567 in the same column).

To resolve this, you need a reference list of valid country codes. Create a separate table or range (e.g., D2:D100) containing your target country codes.

Pro Tip: Sort your reference country codes by character length in descending order (e.g., list 1242, 1246 before 1). This prevents Excel from matching the shorter 1 when the actual code is a longer, multi-digit regional code starting with 1.

Excel 365 & Excel 2021 Solution: Using LET and XLOOKUP

For modern Excel users, the LET function simplifies complex logic by declaring variables. Assuming your cleaned numbers are in column B and your sorted list of country codes is in range $E$2:$E$10:

=LET(
    phone, B2, 
    matched_code, XLOOKUP(TRUE, LEFT(phone, LEN($E$2:$E$10)) = "" & $E$2:$E$10, $E$2:$E$10, "", 0), 
    IF(matched_code = "", phone, REPLACE(phone, 1, LEN(matched_code), ""))
)

How this dynamic formula works:

1. The formula defines phone as the value in B2.
2. It uses XLOOKUP to check if the left side of the phone number matches any of the country codes in the range $E$2:$E$10. By mapping LEN($E$2:$E$10), it dynamically extracts the exact matching number of digits from the left of the phone number.
3. If a match is found, it uses REPLACE to strip the matched prefix. If no match is found, it returns the original phone number untouched.

Method 4: Restoring Local Truncation (Adding Leading Zeros)

In many countries outside of North America (such as the UK, Europe, and Australia), local phone numbers are dialed with a leading zero (0). However, international directory structures replace this leading zero with the country code.

For example, a UK phone number dialed locally as 07911 123456 is written internationally as +44 7911 123456. When you strip the +44, you are left with 7911123456. To make this number usable for local operations, you must append the leading zero back onto the front of the string.

Formula to Add a Leading Zero

Using the REPLACE or MID methods discussed above, you can prefix the output with a "0" string. Assuming a UK number with a 2-character country code (44) after cleanup:

="0" & REPLACE(B2, 1, 2, "")

If you are using a lookup-based system to dynamically determine if a zero should be added, you can utilize an IF statement:

=IF(LEFT(B2, 2) = "44", "0" & REPLACE(B2, 1, 2, ""), B2)

Comparison of Methods

To help you choose the best formula for your dataset, review the differences below:

Conclusion

Standardizing phone numbers doesn't require hours of manual editing. By leveraging Excel's text manipulation functions, you can automate the process of cleaning and formatting international datasets. For simple, single-country files, basic REPLACE formulas do the trick. For complex, global customer databases, establishing a reference list of country codes and deploying dynamic lookup formulas will keep your database clean, standard, and ready for your marketing campaigns.