Remove leading zeroes

[jplank]

I have a column of numbers:

000,093,425
000,391,156.42
000,124,231,113.02
000,001,201.01

I want to remove all leading zeroes and convert the data to:

93,425
391,156.42
124,231,113.02
1,201.01

Is there a simple way to do this?

 

[Joe4]

Welcome to the Board!

First, make sure that the leading zeroes are really there, and not just formatted to look that way.
Just pick a value with leading zeroes, and look at the formula bar. If the leading zeroes are not displayed there, all you need to do is change your format on the whole column to Number with a 1000 separator (comma).

If you see the leading zeroes there, then first change your format on the whole column to Number with a 1000 separator (comma).
Then highlight the whole column and go to the Data menu, select Text to Columns, and click Finish.
This should convert all your entries to Numbers like you want.

 

[hurgadion]

Hi,
let Your Data be in A1:A4, then try to run a Code of the form:

Sub yyy()
Dim i&, a As String

For i = 1 To 4
  a = Cells(i, 1).Value
  a = Replace(a, 0, "")
  Do Until Left(a, 1) <> ","
  a = Right(a, Len(a) - 1)
  Loop
  Cells(i, 2).Value = a
Next i
End Sub

Best regards.

 

[kate middleton]

=IF( LEFT(A1) = "0", RIGHT(A1, LEN(A1)-5), A1)
Here the leading zeros are different for different case so you need to change the length like Len(A1)-4 for 000,391,156.42

 

[jplank]

The zeroes are showing up in the formula bar. I followed your steps. I changed the format by going to More Number Formats and selecting Number with the 1,000 separator. Then I selected Text to columns, didn't change any of the default settings and clicked Finish. The zeroes are still there.

 

[Joe4]

I just tested it and see what you mean. If it is a Text entry (so those leading zeroes are actually in there), the Text to Columns method will not work because of the physical presence of the commas. But there is a way that is even easier still.

First, format the column as Number with the Thousands separator.
Then simply do a "Find and Replace" on that column replacing a single comma (,) with nothing.
It will convert all entries to numbers, thereby dropping the leading zeroes.

 

[hurgadion]

=IF( LEFT(A1) = "0", RIGHT(A1, LEN(A1)-5), A1)
Here the leading zeros are different for different case so you need to change the length like Len(A1)-4 for 000,391,156.42

Hi kate middleton: Your Solution is effective for One Cell, we look for an automatic Solution for Range...

 

[jplank]

I just tested it and see what you mean. If it is a Text entry (so those leading zeroes are actually in there), the Text to Columns method will not work because of the physical presence of the commas. But there is a way that is even easier still.

First, format the column as Number with the Thousands separator.
Then simply do a "Find and Replace" on that column replacing a single comma (,) with nothing.
It will convert all entries to numbers, thereby dropping the leading zeroes.

Worked perfectly! Thanks.