Hi Gary,

Thank you again for the code. Basically, I was looking for a function to
remove leading zeros for a portion of my code. Below is what I was looking
to do. It works now by using the function
"Application.Substitute(xxx,"0","")".

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

Thanks again.


"Gary Keramidas" wrote:

you weren't very specific about where you wanted the results. here i use a range
of B2:b17 and put the result in column C.

Sub test()
Dim cell As Range
For Each cell In Range("b2:B17")
cell.Offset(0, 1).Value = Application.Substitute(cell.Value, "0",
"")
Next
End Sub

--


Gary


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