How to remove leading zeros from a number in a cell Solved

Question

Hello,

I have a column where some numbers start with two zeros, others with three zeros or one zero.

Example:
A1 00616550
A2 00085970
A3 31250366
A4 06156700

I would like to remove the leading zeros from the numbers. How should I do this?

Thank you very much for getting back to me as soon as possible.
Marie Chantal

Configuration: Windows 7 / Internet Explorer 9.0

eriiic · Accepted Answer

Hello,

If you don’t have a cell format that enforces this display, it’s text.
To convert to numeric:
- copy an empty cell
- select the range (column)
- special paste 'add'

Eric

--
By continually trying, you eventually succeed.
So the more it fails, the more chances you have that it will work. (Shadok motto)
In addition to saying thank you (yes, it’s a thing!!!), remember to mark it as resolved. Thanks

Gyrus · Answer

Hello,  What is the format of these cells (Text, Number, Custom, ...)?  Best regards,

Marie Chantal · Answer

It's in number format.

Marie Chantal · Answer

My column is A
My numbers are

001231567000
003456780000
034567890000
001234567000

My cell format is standard. I would like to remove the leading zeros from my numbers. How can I do this, please?

Thank you very much

Marie Chantal · Answer

I found the solution I made a macro as follows

Macro to remove leading zeros from a number in a cell (number format)

Sub macro1()
Dim o As Range, s$
For Each o In Selection
s = o.Text
For i = 1 To 3
If Left(s, 1) = "0" Then s = Right(s, Len(s) - 1)
Next
o = s
Next
End Sub

Marie Chant · Answer

Thank you Ériic,  I tried again and it worked. I pasted a blank cell and used special paste to add, and it works very well  thank you very much it’s very kind of you

Vaucluse · Answer

Good evening
your cell is in text format, change them to standard or number format
if the 0s do not disappear, do this:
copy an empty cell
select the entire field to process (with or without 0 doesn't matter)
right-click special paste and check "add"

best regards

--
To err is human, to persist is diabolical

How to remove leading zeros from a number in a cell

7 answers

Police site...

Display issue on caliber rmd574bt

Μtorrent on 2 disks

Pin gmail to the taskbar

How to plug a usb cord into a tv

Full screen mode

La banque postale in the taskbar

Canon pixma printer not detected via livebox 7

G502 x mouse does not charge on powerplay

Display the import window when opening a .csv