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>

 

[Robert Mika]

Hi akuccputsedut
and welcome to the board.
Look here:

http://www.mrexcel.com/forum/showthread.php?t=59042

 

[akuccputsedut]

Thanks Robert Mika.. I have read that thread.. not one of them resolved my problem...

maybe have any idea... thanks..

 

[chuckchuckit]

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!

 

[Robert Mika]

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.

 

[akuccputsedut]

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

 

[chuckchuckit]

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?

 

[chuckchuckit]

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.

 

[akuccputsedut]

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

 

[chuckchuckit]

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?