How to remove leading zeros from alphanumeric text in excel

user_11060026 0
Viewed 670

One of my cell column in Excel looks like this:

00071331 
000062KV 
00008M01 
00009R22 
001N5350
12345678
00123456

I want to remove all the leading zeros. The output should look like this:

71331 
62KV 
8M01 
9R22 
1N5350
12345678
123456

I have tried using Flash fill in excel but it is not picking up the pattern.

excel-formula vba excel
5 Answers

Use MID,AGGREGATE:

=MID(A1,AGGREGATE(15,7,ROW($1:$8)/(MID(A1,ROW($1:$8),1)<>"0"),1),8)

enter image description here

user_48515900

Because you tagged VBA, try:

Sub NoZero()
    Dim r As Range, v As String
    
    For Each r In Range("A:A")
        v = r.Text
        If v = "" Then Exit Sub
        While Left(v, 1) = "0"
            v = Mid(v, 2, Len(v))
        Wend
        r.Value = v
    Next r
    
End Sub

This will perform the conversion "in-place", without the need of a "helper column".

user_24746560

I think in your case you could use:

=MID(A1,FIND(LEFT(SUBSTITUTE(A1,"0",""),1),A1),8)

Or an array formula like:

=MID(A1,MATCH(TRUE,MID(A1,ROW($1:$8),1)<>"0",0),8)

Or if you don't want to enter it as array formula:

=MID(A1,MATCH(TRUE,INDEX(MID(A1,ROW($1:$8),1)<>"0",),0),8)
user_97581940

Same as @Gary's Student, but with minor changes: working not with column A, but with the current selection; in a string of the form 0000, not all zeros are removed, but only leading ones, etc.

Sub NoZeroAnywhere()
    Dim r As Range, v As String
Rem Work with all selected cells
    If TypeName(Selection) <> "Range" Then Exit Sub
    For Each r In Selection.Cells
        v = r.Text
        If v <> "" Then ' Don't break loop on empty cell
Rem Condition Len(v)>1 prevent remove all zeros, 0000 will be 0, not empty string
            While Len(v) > 1 And Left(v, 1) = "0"
                v = Right(v, Len(v) - 1)
            Wend
            r.Value = v
        End If
    Next r
End Sub
user_140946170

You could also do it as UDF using regex:

Function RemoveLeadingZeroes(stringOne As String) As String

Dim regexOne As Object
Set regexOne = New RegExp

regexOne.Pattern = "^0*"

RemoveLeadingZeroes = regexOne.Replace(stringOne, "")

End Function

enter image description here

Note: Have to enable Tools>References>Microsoft VBScript Regular Expressions in VBA editor.

user_38949170
Related