Function Problems

How to Remove the 34 Prefix from Numbers in Excel

Khadija KhanKhadija Khan Nov 2, 2026 870 views

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.

How to Remove the 34 Prefix from Multiple Numbers in Excel
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".
Before you start

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.

Solution 1Recommended

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.

1
Select an empty cell

Click on a blank cell in a new column adjacent to your first data entry (for example, cell B1).

2
Enter the formula

Type the following formula: =VALUE(IF(LEFT(A1,2)="34",RIGHT(A1,LEN(A1)-2),A1)) and press Enter.

3
Apply to the entire column

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 an IF Formula with LEFT and RIGHT Functions
Retaining Leading Zeros: If your numbers contain leading zeros that you want to keep (e.g., "01234"), remove the VALUE wrapper from the formula: =IF(LEFT(A1,2)="34",RIGHT(A1,LEN(A1)-2),A1). This ensures the output remains formatted as text.

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. 1. Open your data file: Launch WPS Spreadsheet and open the document containing the numbers you need to clean.
  2. 2. Select a target cell: Click on the adjacent blank cell in a new column where you want the cleaned numbers to appear.
  3. 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. 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.
100% compatible with Microsoft Excel formulas and formatsAdvanced data cleaning tools like Flash Fill built right inFree, lightweight, and fast-loading office suiteFamiliar user interface with zero learning curve
microsoft office alternative - wps office

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.