How to Remove the 34 Prefix from Numbers in Excel
Question details
The user needs a way to selectively remove a specific leading number prefix (such as "34") from a column of numbers, while leaving values without this prefix unchanged.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up a dataset containing phone numbers, ID codes, or other formatted data where some entries include an unwanted country code or area prefix.
- Observed behavior
- The goal is to automatically strip the "34" prefix from the start of cell values while keeping the rest of the string intact and ignoring cells that do not begin with "34".
Check if your final numbers require leading zeros. If they do, you will need to output the formula result as text rather than a standard numerical value.
Use an IF Formula with LEFT and RIGHT Functions
This formula dynamically checks if a cell starts with "34" and extracts the remaining characters, safely ignoring cells without the prefix.
By combining the IF, LEFT, RIGHT, and LEN functions, you can isolate the first two characters to check for the prefix. If the prefix exists, the formula trims it off; if not, it returns the original value.
Click on a blank cell in a new column adjacent to your first data entry (for example, cell B1).
Type the following formula: =VALUE(IF(LEFT(A1,2)="34",RIGHT(A1,LEN(A1)-2),A1)) and press Enter.
Select cell B1 again, hover over the bottom-right corner until the cursor turns into a cross, and drag the fill handle down to apply the formula to the rest of your data.

Use Flash Fill for Pattern Extraction
For newer versions of Excel, Flash Fill can automatically recognize your intent and extract the numbers without using complex formulas.
Clean and Format Your Data Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful built-in functions, including IF, LEFT, RIGHT, and Flash Fill, making it incredibly easy to clean, reformat, and analyze your data seamlessly.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing the numbers you need to clean.
- 2. Select a target cell: Click on the adjacent blank cell in a new column where you want the cleaned numbers to appear.
- 3. Input the extraction formula: Type =VALUE(IF(LEFT(A1,2)="34",RIGHT(A1,LEN(A1)-2),A1)) into the formula bar and press Enter.
- 4. Fill the column: Double-click the fill handle in the bottom-right corner of the cell to automatically apply the formula down the entire column.

Frequently Asked Questions
Why does the VALUE function remove my leading zeros?
The VALUE function is designed to convert text strings into standard numerical values. Because standard integers do not start with zero, Excel automatically drops them. To keep leading zeros, omit the VALUE function so the result remains formatted as text.
Can I use Find and Replace to remove the prefix instead?
It is not recommended for this specific task. Using Find and Replace will replace every instance of '34' anywhere within the cell, not just the prefix at the beginning of the string, which can corrupt your data.
How do I delete the original column after extracting the numbers?
First, select your new column containing the formulas and copy it (Ctrl + C). Then, right-click the same selection, choose 'Paste as Values', and press Enter. This replaces the formulas with static text, allowing you to safely delete the original column.




