Re: How can I remove leading zeros?

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: non-VBA Alternative to SUBTITUTE function
    ... single cell in excel. ... If you must use SUBSTITUTE, you can do it in steps using helper columns (or ... number that will still work with Excel) at a time. ... one of the cells in the range above the template cell, ...
    (microsoft.public.excel.worksheet.functions)
  • RE: non-VBA Alternative to SUBTITUTE function
    ... single cell in excel. ... If you must use SUBSTITUTE, you can do it in steps using helper columns (or ... number that will still work with Excel) at a time. ... one of the cells in the range above the template cell, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: One part number with several substitute numbers
    ... Excel usually doesn't handle numbers that start with leading 0s very well. ... it's own qty but the original part will still have the same qty row ... "Susan" wrote: ... substitute part number, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: SUBSTITUTE (more than one in Excel ?)
    ... >> Using the SUBSTITUTE function in Excel 2002, can i have more than one ...
    (microsoft.public.excel.newusers)
  • Re: EXCEL IF FUNCTION
    ... It looks like this is a bug, if you substitute the values you have used with ... Ok I am having problem with Excel IF Function. ... Then on (Cell A7) i put IF Function ...
    (microsoft.public.excel.worksheet.functions)