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

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.

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

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.

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

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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)

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

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.

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.

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

Remove Leading Zeros Using the TEXT Function

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.

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.

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")

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.

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.

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.

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.

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.

Power Query shall convert the values into currencies.

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.

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

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.

Now, delete the old table of your worksheet.

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.

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.

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.

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.

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.