leading zeros
 

When export to Excel most of the time I loose the leading zeros, I want to
know if someone have a macro to restore the zeros?

Thanks,

leading zeros
 

Fernando Duran wrote:
When export to Excel most of the time I loose the leading zeros, I want to
know if someone have a macro to restore the zeros?

Thanks,

You should watch how the data is being imported to avoid in the future.
However, This should work to bring it back once you already have it
imported.

Sub LeadZeros()
Dim DLength As Integer
Dim xlCurrLen As Integer


DLength = 10 'Desired total length
Dim xlCell As Range

For Each xlCell In ActiveSheet.UsedRange
xlCurrLen = Len(Trim(xlCell.Value))
If xlCurrLen < DLength And xlCurrLen < 0 Then
'Have to prepend value with tick to retain the zeros
xlCell.Value = "'" & String(DLength - xlCurrLen, "0") &
xlCell.Value
End If
Next xlCell

End Sub


The weakness I see is that i's converted to text. So you will have to
deal with that if calcing the data. Not sure how to lead zeros and not
have it as text. I'll be watching this post to see if one of the gurus
post a better suggestion.

Regards,
Jamie

leading zeros
 

http://www.asap-utilities.com/

Try this awesome add in. Ensure the folder is placed in the root of the C
drive when installing.

It will add an extra button with a huge folder of items

--
Saruman

---------------------------------------------------------------------------
All Outgoing Mail Scanned By Norton Antivirus 2003
---------------------------------------------------------------------------
"jseven" wrote in message
ups.com...

Fernando Duran wrote:
When export to Excel most of the time I loose the leading zeros, I want
to
know if someone have a macro to restore the zeros?

Thanks,

You should watch how the data is being imported to avoid in the future.
However, This should work to bring it back once you already have it
imported.

Sub LeadZeros()
Dim DLength As Integer
Dim xlCurrLen As Integer


DLength = 10 'Desired total length
Dim xlCell As Range

For Each xlCell In ActiveSheet.UsedRange
xlCurrLen = Len(Trim(xlCell.Value))
If xlCurrLen < DLength And xlCurrLen < 0 Then
'Have to prepend value with tick to retain the zeros
xlCell.Value = "'" & String(DLength - xlCurrLen, "0") &
xlCell.Value
End If
Next xlCell

End Sub


The weakness I see is that i's converted to text. So you will have to
deal with that if calcing the data. Not sure how to lead zeros and not
have it as text. I'll be watching this post to see if one of the gurus
post a better suggestion.

Regards,
Jamie