Essa é velha. Tão velha que dá até vergonha admitir conhecê-la.

Existem algumas soluções já conhecidas que utilizam apenas fórmulas. Todas são ótimas, mas acabam caindo num dos problemas abaixo:

1. quando há mais de um dígito zero à esquerda
2. quando há mais de um dígito zero à direita
3. quando há zeros espalhados pelo número

Quando o número de zeros é conhecido, até dá para resolver, repetindo a fórmula tantos quantos zeros houverem, o que a depender dessa quantidade, pode tornar a fórmula inviável.

Felizmente, fizeram essa pergunta no fórum recentemente aqui. A primeira resposta foi uma fórmula bem bacana (veja no link), mas que caiu no problema 2. Por fim, a paciência bateu e o resultado foi o código abaixo:

Public Function RemoverZerosEsquerda(ByVal textoCelula As String) As String
    'converte em array
    Dim caracteresCelula() As String
    caracteresCelula = Split(StrConv(textoCelula, vbUnicode), Chr$(0))
    ReDim Preserve caracteresCelula(UBound(caracteresCelula) - 1)
    'conta quantos zeros têm no começo
    Dim zeros As Integer
    zeros = 0
    For Each c In caracteresCelula
        If c = "0" Then
            zeros = zeros + 1
        Else
            'quando não achar mais, pára
            Exit For
        End If
    Next c
 
    RemoverZerosEsquerda = Right(textoCelula, Len(textoCelula) - zeros)
End Function

Pode ser chamado via fórmula com um =RemoverZerosEsquerda(A1).

Bom proveito!

Referências

http://superuser.com/questions/42844/best-way-to-remove-leading-zeros-from-a-non-numeric-value-in-excel/42871#42871

http://stackoverflow.com/questions/13195583/split-string-into-array-of-characters