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.

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" <(E-Mail Removed)> wrote in message
> news:00BEEC86-53BF-4252-85F9-(E-Mail Removed)...
> > 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.
>
>
>

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" <(E-Mail Removed)> wrote in message
news:C39958F9-228E-449B-AB1A-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:00BEEC86-53BF-4252-85F9-(E-Mail Removed)...
>> > 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.
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
news:C39958F9-228E-449B-AB1A-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:00BEEC86-53BF-4252-85F9-(E-Mail Removed)...
>> > 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.
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:C39958F9-228E-449B-AB1A-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:00BEEC86-53BF-4252-85F9-(E-Mail Removed)...
> >> > 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.
> >>
> >>
> >>
>
>
>

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" <(E-Mail Removed)> wrote in message
> news:C39958F9-228E-449B-AB1A-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:00BEEC86-53BF-4252-85F9-(E-Mail Removed)...
> >> > 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.
> >>
> >>
> >>
>

chip:

if i have this in a cell 000AAA, your code blanks the cell

if i substitute "" for vbnullchar, it works,



--


Gary


"Chip Pearson" <(E-Mail Removed)> wrote in message
news:F0A66B6D-6477-4B19-B2A3-(E-Mail Removed)...
>
> 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" <(E-Mail Removed)> wrote in message
> news:C39958F9-228E-449B-AB1A-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>>> news:00BEEC86-53BF-4252-85F9-(E-Mail Removed)...
>>> > 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.
>>>
>>>
>>>
>

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" <(E-Mail Removed)> wrote in message
> news:F0A66B6D-6477-4B19-B2A3-(E-Mail Removed)...
> >
> > 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" <(E-Mail Removed)> wrote in message
> > news:C39958F9-228E-449B-AB1A-(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> wrote in message
> >>> news:00BEEC86-53BF-4252-85F9-(E-Mail Removed)...
> >>> > 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.
> >>>
> >>>
> >>>
> >
>
>
>

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range
Dim cCtr As Long
Dim myStr As String

'select the range to fix first
Set myRng = Selection

For Each myCell In myRng.Cells
myStr = myCell.Value
Do
If Left(myStr, 1) = "0" Then
myStr = Mid(myStr, 2)
Else
'get out
Exit Do
End If
Loop
myCell.Value = myStr
Next myCell

End Sub



AccessHelp wrote:
>
> 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" <(E-Mail Removed)> wrote in message
> > news:F0A66B6D-6477-4B19-B2A3-(E-Mail Removed)...
> > >
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:C39958F9-228E-449B-AB1A-(E-Mail Removed)...
> > >> 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" <(E-Mail Removed)> wrote in message
> > >>> news:00BEEC86-53BF-4252-85F9-(E-Mail Removed)...
> > >>> > 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.
> > >>>
> > >>>
> > >>>
> > >
> >
> >
> >

--

Dave Peterson

Hi Chip,

As I mentioned to Gary earlier, I also tried your code again, I realized
that it would not work when I have "00A0". The "Replace(xxx,"0","")" will
remove all the zeros and will leave with "A" (from the above example).

If I changed it to "Replace(xxx,"0",xlNullChar)", it removes the text and
number on the ones with numeric. For example, if I have "00AA", the result
is "".

Please help. Thanks.

"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" <(E-Mail Removed)> wrote in message
> news:C39958F9-228E-449B-AB1A-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:00BEEC86-53BF-4252-85F9-(E-Mail Removed)...
> >> > 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.
> >>
> >>
> >>
>