Re: How can I remove leading zeros?


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: How can I remove leading zeros?
    ... "Gary Keramidas" wrote: ... remove all the zeros and will leave with "A". ... "Chip Pearson" wrote: ... In Excel 2000 and later, ...
    (microsoft.public.excel.programming)
  • Re: default form behavior
    ... Microsoft MVP - Excel ... Pearson Software Consulting ... (email on the web site) ... "Gary Keramidas" wrote in message ...
    (microsoft.public.excel.programming)
  • 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: How can I remove leading zeros?
    ... "Gary Keramidas" wrote: ... if i have this in a cell 000AAA, ... In Excel 2000 and later, ... remove leading zeros on the ones with leading zeros. ...
    (microsoft.public.excel.programming)