try chip' code with the following change

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


--


Gary


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

As I mentioned to Gary earlier, I also tried your code again, I realized
that it would not work when I have "00A0". The "Replace(xxx,"0","")" will
remove all the zeros and will leave with "A" (from the above example).

If I changed it to "Replace(xxx,"0",xlNullChar)", it removes the text and
number on the ones with numeric. For example, if I have "00AA", the result
is "".

Please help. Thanks.

"Chip Pearson" wrote:


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.