Gary,

After I sent you a message last evening, I realized that
"Application.Substitute(xxx,"0","")" would not work because it will remove
all the zeros. For example, if I have "00H0", the result is "H". The
correct result should be "H0", not "H".

Please help. Thanks.

"Gary Keramidas" wrote:

chip:

if i have this in a cell 000AAA, your code blanks the cell

if i substitute "" for vbnullchar, it works,



--


Gary


"Chip Pearson" wrote in message
...

In Excel 2000 and later, use Replace. E.g.,

Sub AAA()
Dim S As String
S = Range("B2").Text
S = Replace(S, "0", vbNullChar)
Range("B2").Value = S
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"AccessHelp" wrote in message
...
Hi Gary,

Thanks for the formula. Unfortunately, the formula is for Visual Basic in
Excel, not for the Excel itself. I don't think Visual Basic has a formula
for "Substitute". I am sorry I should have indicated in my message that it
is for Visual Basic.

Thanks again.

"Gary Keramidas" wrote:

try this if the cell in B2 had your example

=SUBSTITUTE(B2,"0","")

--


Gary


"AccessHelp" wrote in message
...
Hello,

I have a field with values "AAAA", "0AAA", "00AA", and "000A". I want to
remove leading zeros on the ones with leading zeros. For example, "0AAA"
will turn into "AAA".

Can someone help me with the formula? I have tried using "Val" and "Str",
and they don't seem to work.

Thanks.