Hi Chip,

Thanks for the code. As I indicated to Gary, mine would work with the code
"Replace(xxx,"0","")". I guess I could use either your function "Replace" or
Gary's "Application.Substitute".

NewSheet.Cells(j, 9).Value = Replace(Left(Right(nName.Name, 10), 4), "0", "")

Thanks again.


"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.