How to remove leading zeros in Excel

akuccputsedut

New Member
Joined
May 6, 2011
Messages
5
I have a lot of record that contain number like shown below:
<table border="0" cellpadding="0" cellspacing="0" width="187"><col style="width: 140pt;" width="187"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 140pt;" height="20" width="187">0000082181
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">0000005465
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">0000028997
</td> </tr> </tbody></table>
I want to remove all 0 in front of this number in excel. Can anyone here
help me.. I use excel 2007..

I already try using formula


Code:
=IF(     LEFT(A1) = "0" ,     RIGHT(A1, LEN(A1)-5),     A1)

but it only remove 5 character in front what about the number that have 6 '0'......
Please help..

Thanks in advance.

</pre>
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Thanks Robert Mika.. I have read that thread.. not one of them resolved my problem...

maybe have any idea... thanks..
 
Upvote 0
The example Robert gave should work in post #4 in his link:
http://www.mrexcel.com/forum/showthread.php?t=59042

It does appear that they are text instead of numeric. So perhaps try this:

In cell A1 copy in one of your numbers like 0000082181
In cell B1 just enter in the number 1
In cell C1 type in the formula =A1*B1 and push enter

Your cell C1 should now be the number 82181 without the leading 0's

You could then format columns B and C to do as many as you have in column A.

And welcome to the board!
 
Last edited:
Upvote 0
Try to copy those numbers to Notepad and then back to your spreadsheet.
I copied your numbers from here straight to my spreadsheet and they came without zeros.
 
Upvote 0
Thanks chuckchuckit I already test the #4 but it's not work..no effect

when I tried your solution step by step.. it giving me error "a value used in the formula is of the wrong data type"

I used TEXT for my data and GENERAL for other column...

appreciate ur help... thanks..


Try to copy those numbers to Notepad and then back to your spreadsheet.
I copied your numbers from here straight to my spreadsheet and they came without zeros.

I have more than 80k records.. so it quite difficult to copy all in notepad and copy back in spreadsheet...
 
Last edited:
Upvote 0
It should work no problem with General formatting. But try Numeric formatting for columns B and C and see if that might do it?

Also am wondering where you are getting your numbers with the leading zero's from? Copying, importing etc?
 
Upvote 0
I once ran into something like this where I was not able to use any data at all from a source. And after a lot of experimenting I found that they had hidden a leading character that you somehow could not see.

So once I erased the leading character, then the data was usable. I will try to look up that code I used to do that.
 
Upvote 0
It should work no problem with General formatting. But try Numeric formatting for columns B and C and see if that might do it?

Also am wondering where you are getting your numbers with the leading zero's from? Copying, importing etc?

the data was import from database...
 
Upvote 0
There may be different ways to import that data. Try some different settings. Perhaps first see what the import setting is that you are currently using. It seems it may be text. Try to change it to numeric importing if you can. That might fix it right there?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top