How to remove leading zeros in Excel

[akuccputsedut]

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

=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>

 

[akuccputsedut]

Yeah.. it's work that way...

Thanks ya... appreciate that...

 

[Airwolf]

Try the fixed function:-

=Fixed(a1,0,1) the first parameter is the number of decimal places to display the second is 1= no commas, 0= with commas.

so if A1 contains 000007869, =fixed(A1,0,1) will produce 7869
=fixed(A1,0,0) will produce 7,869

 

[Sean Yeoh]

With regards to this issue, I copied the leading 'invisible character'... did a Find all 'invisible character' and replaced all with 'space'.
That seemed to have solved the problem and this is after about an hour of trying every other suggestion on here

Hope this helps..

 

[Rick Rothstein]

If your "numbers" are all in Column A, then select all of Column A, press ALT+de to bring up the Text-To-Columns dialog box and, as soon as it appears, press the Finish button. That should get rid of your leading zeroes, but the cell format will still be Text so, with the column still selected, change the cell format to "General".

 

[chuckchuckit]

Sean, Yes that leading character or embedded one sometimes is not numeric or text when testing for it. Your "Find all" is a good suggestion especially if it is used in various locations. Takes tenacity to get things to work sometimes...