9 Ways to Remove Leading Zeros in Microsoft Excel

Do you want to learn how to remove leading zeros in Excel? This quick Excel tutorial contains some of the best techniques you can use.

Microsoft Excel removes leading zeroes when you’re entering your data in the Number or General format. However, often you work with datasets imported from third-party software or databases where you might encounter the issue of leading zeroes before non-zero values.

Whether you want to make your dataset consistent, improve its readability, and reduce Excel file size for extremely large workbooks with tons of redundant zeroes, I’ve got you covered. Find below different methods to get rid of leading zeroes in Excel effortlessly.

📒 Read More: 9 Ways To Add Leading Zeros In Excel

Remove Leading Zeros Using Convert to Number

Example dataset
Example dataset

In the above dataset, sales data have been imported from a third-party business database. The sales values show as text strings with leading zeroes. However, this isn’t quite readable as sales figures but random numbers.

By default, since Excel 2013, you should see the formula error-checking icon on all the cells containing such numerical values.

Highlight target dataset
Highlight target dataset

To get rid of these leading zeroes, highlight all the cells in the target column.

Convert to Number
Convert to Number

Click on the yellow warning sign on the first cell of the batch.

This should open a context menu with several options. There, click on the Convert to Number selection.

Reformatted to Number format
Reformatted to Number format

Excel shall reformat the highlighted dataset to numbers and remove the leading zeroes.

Currency formatting
Currency formatting

Now, you can click on the Currency option in the Number drop-down menu of the Home tab to appropriately reformat the dataset as $ values.

Multiply by 1 to Remove Leading Zeros in Excel

Another quick way to convert text values to numbers and remove leading zeroes is by multiplying the whole dataset with 1 in Excel.

However, to multiply the dataset with one, you don’t need to create any helping column in the worksheet. Instead, you can use the Paste Special dialog to quickly multiply and convert the dataset.

Enter numerical 1
Enter numerical 1

Firstly, enter numerical 1 in number format in any blank cell of the worksheet.

Now, copy the numerical 1 from its cell by pressing Ctrl + C.

Paste Special Multiply
Paste Special Multiply

Go to the target column that contains the text strings with leading zeroes. Highlight the whole dataset and press Ctrl + Alt + V to bring up the Paste Special dialog box.

On the Paste Special dialog, click on the Multiply option below the Operation section and hit the OK button.

Removed leading zeroes in Excel using multiplication
Removed leading zeroes in Excel using multiplication

You shall now see that Excel has transformed the input dataset into numbers minus the leading zeroes.

Remove Leading Zeros Using Find and Replace

The Find and Replace tool can also help you to remove leading zeroes in Excel. However, this method is only applicable to those datasets that only contain any numbers of leading zeroes but there are all non-zero numbers after leading zeroes.

For example, Customer/Account Numbers, Product Codes/SKUs, Serial Numbers, etc., datasets usually contain only leading zeroes, no zeroes within the non-zero space.

Replace All in Find and Replace
Replace All in Find and Replace

After establishing the fact that there aren’t any zeroes inside the non-zero part of the dataset, press Ctrl + H to bring up the Find and Replace dialog.

Removed leading zeroes using Find and Replace
Removed leading zeroes using Find and Replace

In the Find what field, type *0. Keep the Replace with field empty. Now, hit the Replace All button.

Excel shall remove the leading zeroes and keep the dataset as text strings.

Refrain from using this method if the non-zero part of the dataset also contains zeroes.

Remove Leading Zeros Using Text to Columns

Text to Columns enables you to remove leading zeroes from text strings. Also, you can modify its functionalities to get number formatting or text formatting for the outputs.

Convert to currencies
Convert to currencies

For example, if the input dataset represents a monetary value, highlight the dataset and switch to the Currency format from the Number drop-down menu in the Home tab.

Open Text to Columns
Open Text to Columns

Now, go to the Data tab and click on the Text to Columns command button inside the Data Tools block.

Finish Step 3 with General
Finish Step 3 with General

You shall now see the Convert Text to Columns wizard. Click Next on Step 1, Step 2, and Step 3.

Make sure that the Column data format is default, which is General. Now, click on the Finish button.

Removed leading zeroes with Text to Columns
Removed leading zeroes with Text to Columns

Doing this shall remove all the leading zeroes and convert the text strings into numbers with Currency formatting. You shall see the $ sign as well. Also, the values shall have up to two decimal places.

Choose Text formatting
Choose Text formatting

If the input dataset isn’t a monetary value and you don’t want to change the formatting to number form text, simply highlight the dataset in your worksheet and change it to Text formatting from the Number format drop-down menu in the Home tab.

Removed leading zeroes in Text format
Removed leading zeroes in Text format

Repeat the steps mentioned earlier for the Text to Columns tool. Excel shall remove leading zeroes but won’t change the source formatting.

Remove Leading Zeros Using the VALUE Function

Since the VALUE function transforms all text strings representing a numerical value to a number, you can use it to remove leading zeroes from your dataset. Excel has built-in programming to remove leading zeroes from values in number formatting, so the method works without any hiccups.

Enter VALUE formula
Enter VALUE formula

First, navigate to the target dataset and highlight an empty cell right next to the first cell of the source dataset.

In the empty cell, enter the following formula. You must modify the cell reference according to your own dataset.

=VALUE(C2)
Using VALUE function
Using VALUE function

Hit Enter to convert the input to a number in the selected cell.

Using fill handle for VALUE function
Using fill handle for VALUE function

Now, drag the fill handle from the first cell down the column until relevant data exists in the adjacent cell of the source dataset.

You should get values without leading zeroes. Be mindful that the output dataset is in number formatting.

Now, if you only need the converted values and not the underlying formulas, copy the new column that you just created except for the column header cell.

Paste Special Values command
Paste Special Values command

Then, paste the copied content into the source dataset column by pressing Ctrl + Alt + V. In the Paste Special dialog that appears, choose Values, and hit the OK button.

Deleted leading zeroes using VALUE function
Deleted leading zeroes using the VALUE function

Now, you can delete the formula column you created earlier.

Remove Leading Zeros Using the TEXT Function

TEXT formula with currency
TEXT formula with currency

Suppose, you want to remove zeroes from your input dataset. Also, since the dataset represents sales figures you’d like to convert the output values into currency format. In this scenario, you can use the following TEXT formula:

=TEXT(C2,"$#,##0.00")

In the above formula, you only need to change the reference cell C2.

Deleted leading zeroes with currencies
Deleted leading zeroes with currencies

You must enter the formula in the first cell adjacent to the source dataset. Then, use the fill handle to apply the same formula to the rest of the cells.

TEXT formula without currency
TEXT formula without currency

Alternatively, if you only wish to remove the leading zeroes and keep the input dataset in text formatting, you can use the following TEXT formula:

TEXT(C2,"0")
Deleted leading zeroes without currencies
Deleted leading zeroes without currencies

By using this TEXT function, you preserve the text formatting of the input dataset without getting those annoying formula error-checking icons in all of the cells.

Remove Leading Zeros in Excel Using Power Query

When importing a large dataset with leading zeroes from an external database, you can use the Power Query tool of Excel to cleanse the data of unnecessary zeroes. You can also follow this method for your worksheet datasets as well.

From Table Range
From Table Range

In the current exercise, I’ll be using a dataset with leading zeroes present in my worksheet. To do so, I’ll highlight the input dataset in my worksheet and click on the From Table/Range command in the Get & Transform Data block of the Data tab in the Excel ribbon menu.

From SQL Server Database
From SQL Server Database

However, if you’re interested in importing data from a database, you can go to the Data tab on an empty worksheet.

Click on the Get Data command inside the Get & Transform Data commands block to open the data importing context menu.

Hover your mouse cursor over the From Database menu option and choose the external database source from the overflow context menu. For example, you may choose From Database > From SQL Server Database.

Input dataset in Power Query
Input dataset in Power Query

Whether you import an internal dataset from the worksheet or an external dataset from a database, you shall now find the dataset inside the Power Query tool for further data manipulation.

You should see that Power Query has automatically converted the input dataset to number formatting after removing the leading zeroes.

Currency formatting in Power Query
Currency formatting in Power Query

Now, if your dataset represents monetary values and you’d like to display it accordingly, click on the Sales column in Power Query and go to the Transform tab.

Click on the Data Type option inside the Any Column block and choose Currency in the context menu that shows up.

Converted to currency after removing leading zeroes
Converted to currency after removing leading zeroes

Power Query shall convert the values into currencies.

Data Type Text for input dataset
Data Type Text for input dataset

Alternatively, if you’d like to keep the input dataset in text formatting after removing the leading zeroes, choose Text in the Data Type drop-down of the Transform tab in Power Query.

Now that you’ve transformed the input data column, it’s time to export the dataset from Power Query to an Excel worksheet.

Close and load to
Close and load to

Click the File tab and choose the Close & Load To option from the context menu.

Import data to Excel
Import data to Excel

The Import Data dialog shall show up on the source worksheet. There, choose the Existing worksheet option and highlight a cell range as a destination that doesn’t overlap the source dataset.

Click OK to complete the data export process.

Remove old table
Remove old table

Now, delete the old table of your worksheet.

Convert to range
Convert to range

To convert the imported data set to normal ranges, right-click on the table and choose Convert To Range from the Table menu in the context menu.

Removed leading zeroes using Power Query
Removed leading zeroes using Power Query

That’s it! You’ve successfully removed leading zeroes using Power Query.

Remove Leading Zeros in Excel Using Excel VBA

If you’re an experienced Excel user or willing to explore VBA programming in Excel, you can use a VBA macro to remove leading zeroes.

The following script lets you choose the source dataset, select the destination cell range, and finally choose the output data formatting. All the actions you can do visually using the mouse cursor without writing any code.

VBA script to remove leading zeroes
VBA script to remove leading zeroes
Sub RemoveLeadingZeroes()
    Dim rngInput As Range
    Dim rngOutput As Range
    Dim cell As Range
    Dim formatType As String

    ' Prompt user to select input range
    On Error Resume Next
    Set rngInput = Application.InputBox("Select the range with leading zeroes", Type:=8)
    On Error GoTo 0

    ' Prompt user to select output range
    On Error Resume Next
    Set rngOutput = Application.InputBox("Select the destination range", Type:=8)
    On Error GoTo 0

    ' Prompt user to select formatting type
    formatType = InputBox("Select the output data formatting: " & vbCrLf & _
                          "1. Keep as text strings" & vbCrLf & _
                          "2. Convert to number" & vbCrLf & _
                          "3. Convert to currency")

    ' Validate formatType
    If formatType <> "1" And formatType <> "2" And formatType <> "3" Then
        MsgBox "Invalid format type. Please enter 1, 2, or 3."
        Exit Sub
    End If

    ' Process each cell in the input range
    For Each cell In rngInput
        Select Case formatType
            Case "1"
                rngOutput.Value = "'" & Trim(Str(cell.Value))
            Case "2"
                rngOutput.Value = Val(Trim(Str(cell.Value)))
            Case "3"
                rngOutput.Value = Format(Val(Trim(Str(cell.Value))), "$#,##0.00")
        End Select
        Set rngOutput = rngOutput.Offset(1, 0)
    Next cell
End Sub

From selecting the input dataset to choosing the output dataset’s formatting, for every step, you’ll see an input box with detailed instructions. You only need to select the required cell ranges and options using mouse or keyboard inputs.

To use this script to develop a macro, follow this article:

📒 Read More: How To Use The VBA Code You Find Online

Remove Leading Zeros in Excel Using Office Scripts

If you wish to automate the leading zero removal process in Excel for the web you can use Office Scripts. However, the feature is also available in Excel for the Microsoft 365 desktop app.

Using Office Scripts in Excel
Using Office Scripts in Excel

Go to the Automate tab and click on the New Script button. In the Code Editor that opens, copy and paste the following Office Script:

function main(workbook: ExcelScript.Workbook) {
    // Get the current worksheet
    let worksheet = workbook.getActiveWorksheet();
    
    // Get the selected range
    let range = workbook.getSelectedRange();
    
    // Get the values in the range
    let values = range.getValues();
    
    // Iterate over each cell in the range
    for (let i = 0; i < values.length; i++) {
        for (let j = 0; j < values[i].length; j++) {
            // Remove leading zeroes
            values[i][j] = values[i][j].replace(/^0+/, '');
        }
    }
    
    // Set the new values in the range
    range.setValues(values);
}

Click the Save script button.

Now, highlight the cell range for the input dataset and click on the Run button in the Code Editor console.

Excel shall replace the highlighted dataset with currency formatting and remove the leading zeroes.

Remove leading zeroes but keep text formatting
Remove leading zeroes but keep text formatting

If you wish to keep the output dataset in text formatting, use the following script instead:

function main(workbook: ExcelScript.Workbook) {
    // Get the current worksheet
    let worksheet = workbook.getActiveWorksheet();

    // Get the selected range
    let range = workbook.getSelectedRange();

    // Get the values in the range
    let values = range.getValues();

    // Iterate over each cell in the range
    for (let i = 0; i < values.length; i++) {
        for (let j = 0; j < values[i].length; j++) {
            // Remove leading zeroes
            values[i][j] = values[i][j].replace(/^0+/, '');
        }
    }

    // Set the new values in the range
    range.setValues(values);

    // Set the number format to text
    range.setNumberFormat('@');
}

Conclusions

Now you know how to remove leading zeroes in Excel in various scenarios. You might find all the methods mentioned above suitable in different situations. For example, Power Query is fitting when you’re importing an external dataset and want to cleanse it off leading zeroes.

Besides manual methods, I’ve also explained automated techniques involving Excel VBA and Office Scripts should you need to fully automate the process.

Among all the methods explained so far, which one did you like the most? Do you know a better technique than the ones mentioned here? Do write a few lines in the comment box to share your thoughts or feedback.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

1 Comment

  1. Meni Porat

    Use these 5(!) additional methods to remove a leading zero from a number:

    =MOD(A5,10^(LEN(A1)-1))

    =REPLACE(A1,1,1,””)

    =MID(A1,2,LEN(A1)-1)

    =SUBSTITUTE(A1,”0″,””,1)

    =TEXTJOIN(“”,,MID(A1,SEQUENCE(LEN(A1)-1,,2),1))

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃