The data you're dealing with may have values with leading zeros because the data was created or grouped by someone else based on the selected layout, or perhaps your own layout was selected and now it's time to change it.
By default, Excel ignores leading zeros when entered as a number and enters the number without the leading zeros. This implies that the leading zeros were due to a leading apostrophe, a user-defined number format, or the result of a formula.
Well now the zeroes are here and we have to deal with them. Removing a fixed number of leading zeroes isn't a problem, but removing a variable number of leading zeros isn't a problem either! You'll find that's not the case with today's tutorial on how to remove leading zeros in Excel.
Our techniques for removing leading zeros from numeric values involve the use of the error checking option, the VALUE function,text to columnsmiVba, multiply the column by 1 or add 0 to it, and use a formula specially designed to handle non-numeric values.
Convert text to numbers
if you see onenumbers with leading zerosin excel it may have been entered as a text string because with numbers in general excel will ignore the entered zeros and display the number before the leading zeros. Therefore, leading zeroes remain only as part of a numeric value with custom number and text formats. A small example is this:
Here we have a group of numbers with leading zeros that have been entered with a leading apostrophe. The apostrophe converts the number to text (some punctuation marks convert numbers to text), as we can see that the numbers are left justified instead of right justified as they are by default. Another very obvious indicator isNumber saved as texterror displayed in each cell.
The theory is to convert the cell format to a number format to ignore leading zeroes and display numbers without them. Let's see how this works.
- Select the cells with the errors.
- Click the exclamation point error icon and selectconvert to numberslist option.
This will convert the selected cells to numbers, which will inherently ignore leading zeroes and thus remove them:
Another option would be to remove the apostrophe, which will also have the same effect; this will leave leading zeros without punctuation, which will then be ignored.
Using the VALUE function
The VALUE function converts a numeric text string to a number. Isn't this feature created specifically for this guide? This makes the function easy to use and understand as it was created for the specific purpose of converting text to numbers. As explained in the previous section, converting numeric text to numbers will remove any leading zeros. Let's see the formula:
The VALUE function only needs a single argument to convert numeric text to a number. The function was provided with the numeric text cell reference. VALUE converts the text to a number, and the number ignores leading zeroes, effectively removing them and returning the remaining numbers.
The example shows that this method of removing leading zeros using the VALUE function applies to numbers stored as apostrophized text and also to user-defined number formats.
Multiply the column by 1 or add 0
Multiplying the values of the leading zeros by 1 or adding 0 to them removes the leading zeros. The mechanics behind this exposes the number with leading zeros to a calculation that will not change its original value. The answer to the calculation will result in a number with no leading zeros. Smart, right?
However, keep in mind that it is only possible to perform a calculation with numbers. For example, this method will not work for mixed strings. 000A010, but it works just as well for custom number formats and numbers saved as text.
There are a few ways to multiply the column by 1, which we briefly describe at the end of this section. Just below is our method for using a formula. Here is the actual formula:
The formula is a simple multiplication of cell B3 by the number 1. The calculation is basic:
Now of course Excel will not return 0000501 as a response. The basic calculation is 501 multiplied by 1 and the returned answer is 501, starting with zeros, bye.
The same principles apply when adding zeros to numbers with leading zeros. Both calculations, multiplying by 1 and adding 0, preserve the original value of the number.
Other ways to multiply the column by 1 (also applies to adding 0) include adding a column by 1 or multiplying the column by a cell with 1 as the value or usingcollar especial.
To multiply the column by 1, add a column of ones (for example, enter 1 in C3 and copy it to C14) and multiply the two values; the number with leading zeros and 1. The formula to use will be B3*C3.
To multiply the column by a cell, e.g. enter 1 in any empty cell. E2. Then the column is multiplied by E2. The formula will be B3*$E$2.
By the waycollar especialis explained in the next section.
Let's agree that B3*1 is the faster and better way of the first two methods; neither a new column nor a new cell is required.
Add 0 to column using Paste Special
Usercollar especialfunction, we can add 0 to the column to remove the leading zeros. Zero can be added... without even writing zero! The secret is to copy an empty cell and add it to the column by inserting special.
Basically an empty cell has no value and therefore its value is 0. Medcollar especial, the value can be added to the column. Adding zero to the column introduces numbers with leading zeros into the calculation. The calculation requires numbers, and then the column is converted to numbers, and now we know that when it is converted to numbers, the leading zeros disappear.
Here are the steps to use.collar especialto add zero to the column to remove leading zeros:
- Copy any empty cells in the spreadsheet.
- Select the cells in the column with leading zeros.
- Right click on the selection and selectcollar especialin the menu to opencollar especialdialog box. or pressCtrl + Alt + Vchaves.
- Nocollar especialdialog box you must selectto addRadio button. Then clickBuenoCommand button.
The value copied from the empty cell, ie. 0, it will be added to the selected cells, removing the leading zeros:
Again, in the same way we can useMultiplyoperation icollar especialcopy 1 from a cell and paste it especially in numbers with leading zeros to multiply the column by 1.
Another equally quick method of removing leading zeros would be to add the column of leading zeros to an empty column. If column E is considered an empty column, the formula to add the two columns would be:
Use text in columns
Astext to columnsfunction, a single column of text can be split into multiple columns based on specified conditions. To remove the leading zeros, we will useIn generalA data format that, among other conversions, converts numeric values to numbers. If the values in our example are converted to numbers, the leading zeros are removed. These are the steps to use Exceltext to columnsto remove leading zeros:
- Select the range of numbers with leading zeros.
- YeahDatatab and selecttext to columns
Notext to columnsguide you should selectdelimitedsetup button and then press the buttonNext
- uncheck allboundaries.then selectNextbutton.
- leave itcolumn data formatasIn general. Define the first destination cell for the new column numbers inDestinyField. We define C3 as destination cell.
- If you want to replace the original values, set the first cell of your data as the destination cell. For our example it would be B3.
- click notSalidabutton when finished.
The new values without the leading zeros will be inserted from the defined destination cell:
Another way to applygeneral numberThe format for numeric values is to apply it usingVba. But that's not all we do today. we will assignmacro VBAfor a user-created button on the active worksheet. Let's start by addingmacrocode for our spreadsheet withVbato remove leading zeros:
- NoThe Developer tab, select the Visual Basic icon in the code group to open Visual Basic. or pressAlt+F11chaves.
- ohvisual basicsThe editor will have opened:
- click notInserttab and selectmodulethrow onemodule
- Nomodulewindow, copy and pastemacrocode below:
About Error Resume Next
xTítuloId= "Remove leading zeros"
definingbreak_work=Application.InputBox("Range", xTitleId, Work_Range.Address, Tipo:=8)
job_range.FormatNumber= "In general"
This code will be used to convert numeric values toIn generalnumeric format. ohIn generalformatting will cause leading zeros to be ignored and removed.
The code can be executed immediately in the module window by pressing the buttonF5key. The code can also be run on worksheet tabs, but let's add a button to the macro so that running the code is just a click away.
- lukvisual basics
- Yeahdeveloperaba >control Sgroup >Insertbutton >Button(the firstform control).
- NoAssign macroA dialog box appears so that a macro can be assigned to the button.
- select the appropriatemacroto remove leading zeros, select and thenBueno
- The cursor changes to a cross so the button is added in the preferred size.
- Click and drag the cross to form the button in the area and size you want.
- Change the button name (optional).
- To run the code from the button, select the cells with the numeric values and click the added buttonremove leading zeros
- Confirm your selection in the dialog box that appears and clickBueno.
Using the button performsmacroadded code usingvisual basicseditor. ohIn generalFormatting will be applied and leading zeros will be removed from the selected range:
Remove leading zeros from non-numeric values
All of the methods in this guide for removing leading zeros have focused on converting numeric values to numbers. If the data consists of mixed values, e.g. product codes such as 000A010, you cannot convert them to numbers to remove leading zeros. To remove leading zeros from non-numeric values, we need to use a formula, and the one we use is:
This is what this formula does. ISLANDREPLACE functionreplaces the 0s in B3 with empty text (as indicated by the double quotes ""). The result of SUBSTITUTE is A1. From this text string isLEFT functionreturns 1 character from the left, which in this case is A. SoFIND functionreturns the initial position of A in B3. A is the fourth character in B3 that produces the result of the FIND 4 function.
ohLEN functionreturns the number of characters in B3, ie 7, and the formula subtracts 4 from 7; We are left with 3. Finally, the RIGHT function returns 3+1 characters to the right of the value in B3. Finally, A010 returns.
That's all we had to say about how to remove leading zeros in Excel. We've removed leading zeros from numeric values and mixed values, and we're sure you'll be able to find a solution easily. While you get rid of those zeroes, we'll be right back with more troubleshooting and instructions for handling things the Excel way.
Choose "Format Cells" and select the "Custom" category. In the "Type" textbox, enter the format code "#". This will tell Excel to display the value in the cell without any leading zeros. Click "OK" to apply the formatting to your cells.How do I automatically remove leading zeros in Excel? ›
Choose "Format Cells" and select the "Custom" category. In the "Type" textbox, enter the format code "#". This will tell Excel to display the value in the cell without any leading zeros. Click "OK" to apply the formatting to your cells.How do you remove all leading zeros from a string? ›
Use the inbuilt replaceAll() method of the String class which accepts two parameters, a Regular Expression, and a Replacement String. To remove the leading zeros, pass a Regex as the first parameter and empty string as the second parameter. This method replaces the matched value with the given string.How do I remove leading blanks in Excel? ›
Use the TRIM function to remove leading spaces in Excel
The range in this example is C2:C6. The Excel TRIM function will automatically eliminate all of the cell's leading and trailing spaces. Once you have cleaned the cells, you can Copy/Paste Special Values to replace the original cells if desired.
LTRIM(LTRIM(RTRIM(columnname)),0) - Use this when you want to trim the space from leading and trailing (inner ltrim and rtrim function), and then ltrim to remove the leading zero. LTRIM(columnname,0) - This would only remove the leading zero for the data contained in a particular source field.What function removes leading and trailing blanks in Excel? ›
You use the TRIM function in Excel removes extra spaces from text. It deletes all leading, trailing and in-between spaces except for a single space character between words.How do I remove leading zeros from alphanumeric in Excel? ›
Remove leading zeros by changing the cell format
If leading zeroes are shown in cells with a custom format, then change the cell format back to default General, or apply another number format that does not display preceding zeros.
To trim a string and remove any whitespace, whether leading or trailing, use the strip() method. When the strip() method has no argument, it removes both leading and trailing whitespace from a string. So, if you have whitespace at the start or end of a word or phrase, strip() alone, by default, will remove it.How do you remove leading zeros from an array? ›
Given an array of N numbers, the task is to remove all leading zeros from the array. Approach: Mark the first non-zero number's index in the given array. Store the numbers from that index to the end in a different array. Print the array once all numbers have been stored in a different container.
You can simply add condition for this block "0" from first character using --> event. target. value. Here is the following code for block "0" from first character.How do you remove leading character from string? ›
Use the TRIM() function with the LEADING keyword to remove characters at the beginning of a string. TRIM() allows you to remove specific character(s) or space(s) from the beginning, end, or both ends of a string.What is the formula for leading zeros in Excel? ›
Again, open the Excel document where you want to add the leading zeros. 2. Select the cell to modify and use the following formula: =TEXT(A2, 00000). You can include or remove zeros according to the amount you want.How do I get Excel to automatically add leading zeros? ›
Thankfully, there is an easy way to instantly force Excel to add leading zeroes. All you need to do is append an apostrophe (') at the beginning of every number. This will force Excel to display the number as it is.