How do I write VBA code to strip leading digits from data in a cell?

[steve tatum]

Hello all, I am trying to write some VBA code in Excel that will strip out leading zeros from a number that is in a group of cells. The cells contain various numbers of varying lengths. The number may or may not contain zeros that are not leading. Leading zeros are all I want to delete. There may be 1 to 4 leading zeros. For example, if the data is C1=00037103, C2=194709, C3=023113, C4=0010110456, C5=00798650. I would like for the code to produce D1=37103, D2=194709, D3=23113, D4=10110456, D5=798650.

 

[Chihiro]

Select entire C column. Go to ribbon tools, data tab ->Text to Columns.

Choose... Delimited->Tab. Set destination to $D$1. Hit finish.

To have vba, just record above process and you'll have a good starting point.

 

[Gary's Student]

If the leading zero are real characters and not the result of formatting, select the cells and:

Sub DumpLeadingZeros()
    Dim rng As Range, r As Range
   
    Set rng = Intersect(Selection, ActiveSheet.UsedRange)
   
    For Each r In rng
        While Left(r.Value, 1) = "0"
            r.Value = Mid(r.Value, 2)
        Wend
    Next r
End Sub

 

[shrivallabha]

You have written VBA solution but this can be done using a simple formula as well. In C1 put below formula and copy down.
=ABS(C1)

 

[NARAYANK991]

Hi ,

There are at least 2 ways of having numeric data with leading zeroes in a cell ; one way is where the entry is prefixed with a single apostrophe , and the other is when the entry has been done after the cell has been formatted as Text.

The method suggested by Shrivallabha works in both cases.

However , another way of just adding 0 through a formula , as in :

=A1 + 0

works only when the numeric data is entered by prefixing it with a single apostrophe.

When numeric data is entered after the cell has been formatted as text , using the formula :

=A1 + 0

leaves the result , without its leading zeroes , with the cell formatted as Text ; you need to manually change the cell format to General for the result to become numeric.

Narayan

 

[Gary's Student]

You have written VBA solution but this can be done using a simple formula as well. In C1 put below formula and copy down.
=ABS(C1)

Very good! I did not realize that ABS() would work on text strings as well as numbers!

 

[steve tatum]

Thanks to everyone. All of these options were very helpful. I am looking to code this in VBA so I like the code submitted by Gary's Student for this particular circumstance. The record macro posted by ChiHiro worked as well and I did not know this was even possible using the Delimited functionality so I never thought of trying it.