CODEWITHSUNDEEP
excelformulasubstitution
redviper2100
redviper2100

Reputation: 265

Remove the first zeros in excel

I want to remove in Excel the first zeros from the following codes, the problem is that the zeros don't have the same pattern. What can I do to achieve this?

The codes before: 0000450D 00320A 0000107B 023B

I want the codes to be like this: 450D 320A 107B 23B

Any ideas? Thanks!

Upvotes: 0

Views: 109

Answers (2)

Mrig
Mrig

Reputation: 11702

Try this:

=MID(A1,FIND(LEFT(SUBSTITUTE(A1,0,"")),A1),255)

Upvotes: 2

AranDG
AranDG

Reputation: 406

If you're happy to use VBA, insert the code below into a new module and on your sheet, use the function like =ReplaceZeros(A1)

Public Function ReplaceZeros(varVal As String)

varLeading = True

For i = 1 To Len(varVal)

    If Mid(varVal, i, 1) = "0" Then

        If varLeading = False Then

            varOutput = varOutput & Mid(varVal, i, 1)

        End If

    ElseIf Mid(varVal, i, 1) = " " Then

        varLeading = True
        varOutput = varOutput & Mid(varVal, i, 1)

    Else

        varLeading = False
        varOutput = varOutput & Mid(varVal, i, 1)

    End If

Next

ReplaceZeros = varOutput

End Function

This will simply cycle through the characters and remove leading zeroes from the start of the string, or following a space

Upvotes: 1

Related Questions