How do I remove the leading 0's from a number?

Question

I want to remove all the leading 0's from the numbers in this column, without affecting the rows that start with a letter.

I think you may have forgotten to include in your question what you have tried and what aspect of it isn't working. — Ambie, Jul 27 '16 at 15:06

score 4 · Accepted Answer · answered Jul 27 '16 at 15:07

For a non-VBA solution, you can highlight the entire row and go to Data>Text-To-Columns and then hit "Finish". Numbers will be converted to numeric format and strings will remain untouched.

For a VBA solution, you could iterate through the cells:

 For each rngCell in Sheet1.Columns("A").Cells

 Next rngCell

And within that iteration, use isnumeric() to test before replacing

 If isnumeric(rngCell.value) Then rngCell.value = rngCell.value + 0

Or something along those lines. You could also record that text-to-columns bit and just sub out your column's range in the VBA. Depending on how much control you need over your data transformation.

... and of course the number format shouldn't be something like this: `rngCell.NumberFormat = "0000000000"` ;) — Ralph, Jul 27 '16 at 15:20

score 4 · Answer 2 · answered Jul 27 '16 at 15:29

4

Another Non VBA Solution... (Simplest of all)

Highlight the data
You will see an Exclamation Mark next to your data as shown in screenshot. Click on it
Click on Convert To number and you are done :)

Result

answered Jul 27 '16 at 15:29

Siddharth Rout

147,039
17
206
250

score 3 · Answer 3 · answered Jul 27 '16 at 15:06

3

you can use this formula. my formula source data starts from B3.

=REPLACE(B3,1,FIND(LEFT(SUBSTITUTE(B3,0,"")),B3)-1,"")

answered Jul 27 '16 at 15:06

Rosetta

2,665
1
13
29

i forgot to check OP tag.. wasted my time – Rosetta Jul 27 '16 at 15:11

user3421658 · Answer 4 · 2016-07-27T15:31:07.410

1

Try to convert the cell to a number and then back to string - if it fails it already was a string. vba convert string to int if string is a number

Edit:

Try to solve it with an regex:

With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = False
    .Pattern = "^0*"
End With

If regEx.Test(strInput) Then
    strInput = regEx.Replace(strInput, "")
End If

Source: How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

edited Jul 27 '16 at 15:31

answered Jul 27 '16 at 15:08

user3421658

31
5

This won't fulfill the requirement exactly. Some start with zeros but still have chars in them. – Mike Cheel Jul 27 '16 at 15:14
I just saw that. then your could use the regex `^0*` and replace with an empty string. – user3421658 Jul 27 '16 at 15:26

thentt · Answer 5 · 2016-08-02T14:37:43.097

0

You could do it with a formular with or without VBA:

=IF(ISNUMBER(Value(A1));Value(A1);A1)

edited Aug 02 '16 at 14:37

answered Jul 27 '16 at 15:19

thentt

16
4

Yes, of course i have tested it, works perfectly. Didn't it work for you? – thentt Jul 28 '16 at 10:45
I am working on Excel 2016. As per my understanding The ISNUMERIC function can only be used in VBA code in Microsoft Excel whereas you have mentioned that one can do it with a formula with or without VBA. Another Worksheetfunction ISNUMBER works fine for me in the formula. – skkakkar Jul 28 '16 at 13:33
Yeah, you are right. Thank you. I had to test it in an excel with an different language package. Lost in Translation. – thentt Aug 02 '16 at 14:38

How do I remove the leading 0's from a number?

5 Answers5