Function Problems

How to Replace Only the First Two Digits of a Phone Number in Excel

Camila Milosovich Sep 28, 2026 869 views

Question details

The user needs to replace only the first two digits of a phone number (such as a country or area code) without affecting identical digits that may appear later in the number string.

How to Replace Only the First Two Digits of a Phone Number in Excel

Product: Excel
Device & OS: not provided
Scenario: Updating phone number prefixes in a dataset where standard replacement tools accidentally alter numbers inside the main phone sequence.
Observed behavior: Using the standard SUBSTITUTE function incorrectly replaces every matching occurrence of the digits throughout the entire phone number instead of just the leading characters.

Before you start

Before applying any formulas, ensure your phone numbers are clean of inconsistent formatting. Remove any special characters like asterisks, hyphens, or prefixes like [TEL] using the Find and Replace tool so the formula can accurately target the first two digits.

Solution 1Recommended

Use LEFT, RIGHT, and VLOOKUP Functions

This method isolates the first two digits for replacement via a lookup table, then reattaches the remaining original digits.

By combining text extraction functions with VLOOKUP, you can explicitly target the beginning of the string. The LEFT function grabs the prefix, VLOOKUP finds the new corresponding prefix, and RIGHT retrieves the rest of the phone number.

Create a Lookup Table

Set up a reference table in your worksheet (for example, in cells D2:E10). Place the old two-digit codes in column D and the new replacement codes in column E.

Enter the Formula

Select an empty cell next to your first phone number (e.g., cell B2) and enter the following formula: =VLOOKUP(LEFT(A2,2),$D$2:$E$10,2,0)&RIGHT(A2,LEN(A2)-2).

Apply Formula to Column

Press Enter to calculate the first result. Then, click the small square at the bottom-right corner of cell B2 and drag it down to fill the formula for the rest of your phone numbers.

Formula Breakdown: LEFT(A2,2) extracts the first 2 digits, VLOOKUP fetches the new code from your table, and RIGHT(A2,LEN(A2)-2) appends all characters except the first two.

Solution 2

Clean Special Characters Using Find and Replace

Remove text prefixes or symbols that interfere with extracting the first two digits.

Advanced Data Processing

Easily Manage Formulas and Clean Data with WPS Spreadsheet

WPS Spreadsheet provides powerful and highly compatible tools for managing complex datasets. It perfectly supports advanced nested formulas like VLOOKUP, LEFT, and RIGHT to help you process phone numbers seamlessly.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the phone numbers.
2. Clean your data: Use the built-in Find and Replace tool (Ctrl+H) to strip away any unwanted characters like hyphens or asterisks.
3. Apply the replacement formula: Type the combined VLOOKUP, LEFT, and RIGHT formula into the adjacent cell to generate the updated phone numbers.
4. Drag to fill: Double-click the fill handle on the cell to instantly apply the formula to all rows in your dataset.

100% compatible with Microsoft Excel formulas and text functionsIntuitive Find and Replace features for quick data cleaningLightweight architecture ensures fast calculation even on large datasetsFree to use with a familiar, user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I just use the SUBSTITUTE function to change the phone numbers?

The SUBSTITUTE function looks for the specified text string and replaces every instance of it within the cell. If the two digits you want to replace at the beginning also appear later in the phone number, those later instances will also be incorrectly replaced.

How do I change the first three digits instead of the first two?

You can easily adapt the formula by changing the numerical values. Adjust LEFT(A2,2) to LEFT(A2,3) to extract three digits, and change the subtraction in the RIGHT function from LEN(A2)-2 to LEN(A2)-3.

Why is my VLOOKUP returning an #N/A error?

This usually happens because the data types do not match. The LEFT function extracts text, so even if it looks like a number, it behaves like a text string. Ensure the values in your lookup table's first column are also formatted as text, or wrap the LEFT function in a VALUE() function if the table uses numbers.

Excel & Data Problems