How to remove leading zeros in Excel

Thanks:  0
Likes:  0

1. ## How to remove leading zeros in Excel

I have a lot of record that contain number like shown below:
 0000082181 0000005465 0000028997

I want to remove all 0 in front of this number in excel. Can anyone here
help me.. I use excel 2007..

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

2. ## Re: How to remove leading zeros in Excel

Hi akuccputsedut
and welcome to the board.
Look here:

3. ## Re: How to remove leading zeros in Excel

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

maybe have any idea... thanks..

4. ## Re: How to remove leading zeros in Excel

The example Robert gave should work in post #4 in his link:

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!

5. ## Re: How to remove leading zeros in Excel

I copied your numbers from here straight to my spreadsheet and they came without zeros.

6. ## Re: How to remove leading zeros in Excel

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

Originally Posted by Robert Mika
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...

7. ## Re: How to remove leading zeros in Excel

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?

8. ## Re: How to remove leading zeros in Excel

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.

9. ## Re: How to remove leading zeros in Excel

Originally Posted by 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?
the data was import from database...

10. ## Re: How to remove leading zeros in Excel

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?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•