Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Facebook Twitter Instagram
Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Tech News Today
Home»Software»Excel»How to Remove Leading Zeros in Excel

How to Remove Leading Zeros in Excel

Asmi DhakalBy Asmi DhakalNovember 1, 2022
remove leading zeros in excel

There is no gray area regarding leading zeros; you either want them or not at all. When leading zeros aren’t desired, they can make your data look disorganized and difficult to comprehend. With the multiple features Excel offers, you could’ve accidentally configured the settings to display the leading zeros in Excel.

In this article, we will guide you in getting rid of the leading zeros in your Excel spreadsheet using six different methods. Additionally, we will also delve into why your data has leading zeros so you can avoid it the next time.

Table of Contents

  • Why Does My Data Have Leading Zeros?
  • How to Remove Leading Zeros in Excel?
    • Use Error Wizard
    • Use Simple Calculations
    • Change Cell Formatting
    • Use Paste Special
    • Text to Column Tool
    • Using Formula

Why Does My Data Have Leading Zeros?

By default, Excel automatically gets rid of the leading zeros. This is because adding zeros before a number doesn’t give a different value. For example, 009 and 9 have the same meaning, making the two zeros before 9 redundant. However, if your value is registered as text, Excel will not remove the leading zeros for you.
Here are the possible reasons why your data may have leading zeros in the Excel spreadsheet:
  • Cell formatted as text 
  • Apostrophe added before the number
  • Custom cell formatting

How to Remove Leading Zeros in Excel?

The solutions to removing leading zeros in Excel depend on how you got them in the first place. Before you move on with the fixes, we suggest you go through the section before this and apply the solutions accordingly.

Use Error Wizard

You could use the error wizard to convert your data to a number if your cell was formatted as text or an apostrophe was used before your number. The error wizard is extremely handy when you’re new to Excel, as it identifies a potential error in data entry. You can convert either one or multiple values into numbers using the wizard. 

Follow these steps to convert your data to number from text using the error wizard:

  1. Open your worksheet.
  2. Select the cell with the leading zeros.
  3. Choose Convert to Number.
    Convert to Number

Use Simple Calculations

As we’ve already mentioned prior to this, Excel will not automatically remove leading zeros if your value is in the text format. A simple workaround for this issue is performing simple mathematical calculations to remove the leading zeros.

Refer to the following steps to convert your value from text to number format through calculations:

  1. Open your spreadsheet.
  2. On a new cell, enter either one of these calculations:
  • =(cell) * 1Remove Leading Zeros in Excel through multiplication
    • =(cell) + 0Remove Leading Zeros in Excel through addition
  1. Hit Enter.
  2. To convert the remaining cells, drag your cursor from the bottom-right corner of the screen to use flash fill.
    Flash Fill in Excel

Change Cell Formatting

If you’ve applied special cell formatting using the Format Cells utility, the error wizard will not register your data as a potential error. In this case, you’ll have to change the cell format from the Home tab. 

To see if any custom formatting has been applied to your cell, select the cell and view the format type in the Number section of the Home tab. If you see the cell formatted as Special, follow these steps to remove the leading zeros:

  1. Open your Excel file.
  2. Select the cells with the leading zeros.
  3. On the Home tab, drop the Special menu down in the Number section.
    Excel Cell Format
  4. Choose General.
    Change Cell Format Excel

Use Paste Special

If you’re copying a large number of data to your spreadsheet with leading zeros, you could use the paste special feature to remove all the leading zeros. The Paste Special can be a useful tool when you don’t know if the data you wish to paste is either formatted as text or if any special formatting is applied to it.

Refer to the following steps to use the Paste Special too to remove the leading zeros from your data set:

  1. Open your worksheet.
  2. Select the data with the leading zeros and hit Ctrl + C to copy the value. 
  3. Right-click on a new cell, then choose Paste Special from the list of options.
    Paste Special in Excel
  4. On the dialog box, select the box next to Add in the Operation section.
    Paste Special Add Operation
  5. Click OK.

Note: Make sure there are enough cells empty to fit your value from the cell you used paste special to avoid the #SPILL! error

Text to Column Tool

Although this may sound unconventional, you can also use the Text to Column tool to remove the leading zeros from your number. While using the tool, you will also get the option to change your data format. Using this, you can change the formatting to General to remove the leading zeros from your data.

Here are the steps you can use to remove the leading zeros from the Text to Column Tool from Excel:

  1. Launch Excel to open your file.
  2. Select your data set, then head to the Data tab from the menu bar.
  3. Choose Text to Column.
    Text to Columns Tool Excel
  4. In the dialog box, select Delimited, then click Next.
    Text to Columns Excel 1
  5. Under Delimiters, make sure all boxes are unchecked, then select Next.
    Text to Columns Excel 2
  6. Under Column data format, select General.
  7. Set the location you wish to enter the new data set.
  8. Select Finish.
    Text to Columns Excel 3

Using Formula

You can use the VALUE function to convert a single text value with leading zeros into a number without the leading zeros. Although you can only use this for one data at a time, you can use flash fill to later convert the other values and remove their leading zeros. If you wish to use the VALUE function to remove the leading zeros from your data set, follow these steps:

  1. Open your spreadsheet.
  2. In an empty cell, enter the formula in this format:=VALUE(cell)VALUE Function Excel
  1. Hit Enter on your keyboard.
  2. Place your cursor on the bottom right corner of the cell, then drag it to use flash fill.
    Flash Fill Excel

Note: When you use flash fill, make sure there isn’t any element in the fill range to avoid #SPILL! error

how-to
Asmi Dhakal

Asmi Dhakal is a Tech Writer specializing in Microsoft Office-based programs at TechNewsToday. She aims to use her experience to help others learn their way around its programs. Asmi’s interest in Office products grew when she first entered the marketing industry. While she initially struggled to understand their application, she eventually understood her way around them. In her articles, she exhibits a strong understanding of related issues and attempts to push only relevant fixes. Over time, she has grown a particular fondness for MS Excel and spends her free time learning Visual Basic Analysis (VBA). She’s always on the lookout for productivity tools that can get the job done quicker in Excel. Her approach to related problems is very humane, helping new users not only fix the issue but learn more about its nature. Asmi also heavily indulges in personal writing. She is a disciplined journal writer and has maintained a regular journal for 8 years! When her schedule allows, she works to rehabilitate street animals.

Related Posts

check mark in excel

How to Put Check Mark in Excel

January 20, 2023
best excel shortcuts

Best 52 Excel Shortcuts Only Experts Know

January 19, 2023
How to Find and edit Hyperlinks on Excel

How to Find and Edit Hyperlinks on Excel

January 23, 2023
how to find unique values in excel

How to Find Unique Values in Excel

January 15, 2023
how to find links in excel

How to Find and Edit External Links in Excel

January 13, 2023
reference isn't valid excel

Reference Isn’t Valid Excel Error – 6 Ways to Fix it

January 6, 2023
Add A Comment

Leave A Reply Cancel Reply

Latest Posts
how long does a cmos battery last

How Long Does a CMOS Battery Last

January 25, 2023
thunderbolt vs usb c

Thunderbolt Vs USB C: What’s the Difference

January 25, 2023
how to find hidden folder in laptop

How to Find Hidden Folder in Laptop

January 24, 2023
You may also like
watch-twitch-on-roku-tv

How to Watch Twitch on Roku TV

January 25, 2023
hp mouse not working

HP Mouse Not Working? Here’s How to Fix It

January 25, 2023
install-kodi-on-firestick

How to Install Kodi on Firestick (Step-By-Step Guide)

January 25, 2023
Recommended
Cookie Clicker Garden Guide

Cookie Clicker Garden Guide to Unlocking Every Seed

September 26, 2021
monitor no signal

Computer Turns On But Monitor Says No Signal (9 Ways To Fix)

November 10, 2022
Facebook Twitter Pinterest
  • Home
  • About Us
  • Our Team
  • Editorial Guidelines
  • Privacy Policy
  • Affiliate Disclosure
© 2023 TechNewsToday.

Type above and press Enter to search. Press Esc to cancel.