Re: How can I remove leading zeros?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

that's why you need to be more specific when you specify your criteria, you did
not provide an example that has a zero after the A characters.

--


Gary


"AccessHelp" <AccessHelp@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:339E25A5-F145-4C47-81C3-6CE3AE2F0C96@xxxxxxxxxxxxxxxx
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" <chip@xxxxxxxxxxxx> wrote in message
news:F0A66B6D-6477-4B19-B2A3-B4A1DADB5905@xxxxxxxxxxxxxxxx

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" <AccessHelp@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C39958F9-228E-449B-AB1A-506249EEB396@xxxxxxxxxxxxxxxx
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" <AccessHelp@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:00BEEC86-53BF-4252-85F9-52274CC9C119@xxxxxxxxxxxxxxxx
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.









.

Relevant Pages

  • RE: Help Importing to Access 2003 SSNs from Excel 2003
    ... zeros and hyphens. ... There doesn't seem to be a way to format the field ... You would think that Access and Excel would compliment each other on the ... Social Security number format, but apparently not. ...
    (microsoft.public.access.externaldata)
  • Re: bcp to export sql table to excel, how avoid suppress of leading ze
    ... I know 2 ways to view leading zeros in excel: ... personalized cell formatting like "00...0" with as many 0 as are the ... Have you tried to use an already formatted and empty Excel file? ... exec master..xp_cmdshell @sql ...
    (microsoft.public.sqlserver.dts)
  • Re: Stop excel from dropping the 0 in the beginning of a number?
    ... I reimported it into Excel to see what would happen when Amazon received it. ... If Amazon still cannot read the file and you have the leading zeros in the ... Format, Cells, Number, custom, 0-0000-0000-0. ...
    (microsoft.public.excel.setup)
  • Re: How do I stop Excel from dropping leading zeros?
    ... "Excel Leading Zero Import" and the number is 2083021. ... valid leading zeros and the values are not fixed width. ... Is there a way to disable auto format? ... I also need to drop the last digit (the check ...
    (microsoft.public.excel.misc)
  • Re: How do I stop Excel from dropping leading zeros?
    ... I will have a look at the file and open it in Excel. ... valid leading zeros and the values are not fixed width. ... Is there a way to disable auto format? ... I also need to drop the last digit (the check ...
    (microsoft.public.excel.misc)