Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / Programming / September 2007

Tip: Looking for answers? Try searching our database.

How can I remove leading zeros?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
AccessHelp - 26 Sep 2007 21:58 GMT
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.
Reply to this Message
Gary Keramidas - 26 Sep 2007 22:03 GMT
try this if the cell in B2 had your example

=SUBSTITUTE(B2,"0","")

Signature

Gary

> Hello,
>
[quoted text clipped - 6 lines]
>
> Thanks.
Reply to this Message
AccessHelp - 26 Sep 2007 22:31 GMT
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.  

> try this if the cell in B2 had your example
>
[quoted text clipped - 10 lines]
> >
> > Thanks.
Reply to this Message
Gary Keramidas - 26 Sep 2007 22:41 GMT
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

Signature

Gary

> Hi Gary,
>
[quoted text clipped - 19 lines]
>> >
>> > Thanks.
Reply to this Message
AccessHelp - 26 Sep 2007 23:06 GMT
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.  

> 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.
[quoted text clipped - 30 lines]
> >> >
> >> > Thanks.
Reply to this Message
Chip Pearson - 26 Sep 2007 22:45 GMT
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

Signature

Cordially,
Chip Pearson
Microsoft MVP  - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

> Hi Gary,
>
[quoted text clipped - 23 lines]
>> >
>> > Thanks.
Reply to this Message
AccessHelp - 26 Sep 2007 23:09 GMT
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.  

> In Excel 2000 and later, use Replace. E.g.,
>
[quoted text clipped - 32 lines]
> >> >
> >> > Thanks.
Reply to this Message
Gary Keramidas - 26 Sep 2007 23:16 GMT
chip:

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

if i substitute "" for vbnullchar, it works,

Signature

Gary

> In Excel 2000 and later, use Replace. E.g.,
>
[quoted text clipped - 28 lines]
>>> >
>>> > Thanks.
Reply to this Message
AccessHelp - 27 Sep 2007 16:13 GMT
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.

> chip:
>
[quoted text clipped - 34 lines]
> >>> >
> >>> > Thanks.
Reply to this Message
Dave Peterson - 27 Sep 2007 16:21 GMT
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

> Gary,
>
[quoted text clipped - 59 lines]
> > >>> >
> > >>> > Thanks.

Signature

Dave Peterson

Reply to this Message
AccessHelp - 27 Sep 2007 18:30 GMT
Hi Dave,

Thanks for the code.  Where should I put your code in my code?  Should I put
it right below my code?  I have tried to put it as a separate code, and it
didn't work.  I got an error "Variable not defined".  

I need help with one of the line item below:

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

Where you see "Application.Substitute(xxx,"0","")", that is where I need
help with.

Thanks.  Below is my entire code.

Sub CreateCSV()
Dim CSVDir As String                'Directory where the CSV files are saved
   Dim CSVFName As String              'Original Name of CSV file
   Dim CSVAFName As String             'Additional Name for CSV File, if
one exists
   Dim CRYear As Integer               'Year
   Dim FName As String        ‘Portion of CSV File Name
   Dim InitName As String              'Placeholder to create additional
CSV files
   Dim i As Integer                    'Use to create additional CSV files
   Dim j As Integer                    'Use to create CSV sheet
   Dim myFile As String                'Use to test for file existence to
create additional CSV files
   With Sheets(“Sheets3”)
       CRYear = Year(.Cells(2))
   End With
   With Sheets(“Sheets2”)
        FName=.cells(2)
    End With
   CSVFName = FName & CRYear & ".csv"
   CSVDir = "C:\" & CRYear
   
   ActiveWorkbook.Save                 'Save the input file before creating
a CSV file.
   
   'CREATE A CSV SHEET
   Set NewSheet = Worksheets.Add       'Create a new worksheet for CSV
   NewSheet.Name = "CSV"
   j = 1
   'Create a CSV sheet
   For Each nName In ActiveWorkbook.Names
       NewSheet.Cells(j, 1).Value = "'" & Right(Left(nName.Name, 7), 6)
       'Data
       NewSheet.Cells(j, 3).Value = nName.RefersTo
       If Right(Left(nName.Name, 7), 2) <> 0 And Right(Left(nName.Name, 5),
2) <> 0 Then
           NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2)) & "-" & Val(Left(Right(nName.Name, 16),
2))
       ElseIf Right(Left(nName.Name, 7), 2) = 0 And Right(Left(nName.Name,
5), 2) <> 0 Then
           NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2))
       ElseIf Right(Left(nName.Name, 7), 4) = 0 Then
           NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
       End If
       If Left(Right(nName.Name, 14), 2) <> 0 Then
           NewSheet.Cells(j, 7).Value = Left(Right(nName.Name, 14), 2)
       End If
       If Left(Right(nName.Name, 12), 2) <> 0 Then
           NewSheet.Cells(j, 8).Value = Left(Right(nName.Name, 12), 2)
       End If
       If Left(Right(nName.Name, 10), 4) <> 0 Then
           NewSheet.Cells(j, 9).Value =
Application.Substitute(Left(Right(nName.Name, 10), 4), "0", "")
       End If
       NewSheet.Cells(j, 10).Value = "'" & Left(Right(nName.Name, 6), 2) &
"." & Left(Right(nName.Name, 4), 2)
       NewSheet.Cells(j, 11).Value = Right(nName.Name, 2)
       j = j + 1
   Next
   NewSheet.Columns("A:K").AutoFit

   'CREATE A CSV FILE
   'Check to see the CSV file is already exist.
   'If exist, ask the user whether to overwrite the existing file.
   If Len(Dir(CSVDir & CSVFName)) > 0 Then
       PROMPT1 = MsgBox(Prompt:="There is a CSV file " & "'" & CSVFName &
"'" & _
                       " already created for this cost report." & _
                       "  Would you like to overwrite the existing CSV
file?", _
                       Buttons:=vbYesNo + vbQuestion, Title:="CSV Macro")
           'If 'Yes', overwrite it.
           If PROMPT1 = vbYes Then
               Sheets("CSV").Select
               Sheets("CSV").Move
               Application.DisplayAlerts = False
               ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , ,
True, _
                   False, xlNoChange
               Application.DisplayAlerts = True
               ActiveWindow.Close False
           'If 'No', ask the user whether to create a new file with a new
name.
           ElseIf PROMPT1 = vbNo Then
               PROMPT2 = MsgBox(Prompt:="Would you like to create an
addition CSV file?", _
                               Buttons:=vbYesNo + vbQuestion, Title:="CSV
Macro")
                   'If 'Yes', additional CSV files are created automatically.
                   If PROMPT2 = vbYes Then
                       InitName = CSVDir & CSVFName
                       CSVAFName = InitName
                       Do
                           myFile = Dir(CSVAFName)
                           If myFile <> "" Then
                               i = i + 1
                               CSVAFName = Left(InitName, Len(InitName) -
4) & i & ".csv"
                           End If
                           
                           If i > 2 Then
                               MsgBox "Sorry! There are already " & i & "
files created " & _
                                       "in the directory C:\'.  " & Chr(13)
& "No " & _
                                       "additional file is created.",
vbInformation, _
                                       "CSV Macro"
                               Application.DisplayAlerts = False
                               Sheets("CSV").Delete
                               Application.DisplayAlerts = True
                               Workbooks("MACRO to Create CSV.xls").Close
False
                                   End
                           End If
                       Loop While myFile <> ""
                       Sheets("CSV").Select
                       Sheets("CSV").Move
                       ActiveWorkbook.SaveAs CSVAFName, xlCSVWindows, , , _
                       True, False, xlNoChange
                       ActiveWindow.Close False
                       MsgBox "An additional CSV file '" & Right(CSVAFName,
Len(CSVAFName) - Len(CSVDir)) & _
                       "' has created in " & _
                       "the directory 'C:\'.", _
                       vbInformation, "CSV Macro"

                   'If 'No', delete the CSV sheet that created in the input
file.
                   Else
                       Application.DisplayAlerts = False
                       Sheets("CSV").Delete
                       Application.DisplayAlerts = True
                       MsgBox "No additional CSV file is created.",
vbInformation, _
                               "CSV Macro"
                   End If
           End If
   'If the CSV file does not exist, create one.
   Else
       Sheets("CSV").Select
       Sheets("CSV").Move
       ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , , , False,
xlNoChange
       ActiveWindow.Close False
       MsgBox "A CSV file '" & CSVFName & "' has created in the directory "
& _
               "'C:\'.", vbInformation, _
               "CSV Macro"
   End If
   
   'Close the Macro file after saving the CSV file
   Workbooks("MACRO to Create CSV.xls").Close False
End Sub

> Option Explicit
> Sub testme()
[quoted text clipped - 84 lines]
> > > >>> >
> > > >>> > Thanks.
Reply to this Message
Dave Peterson - 27 Sep 2007 22:52 GMT
Don't use that other code.

Add this to a General module in your workbook's project:

Option Explicit
Function RemoveLeadingZeros(myStr As String) As String
   Dim cCtr As Long
   Do
       If Left(myStr, 1) = "0" Then
           myStr = Mid(myStr, 2)
       Else
           'get out
           Exit Do
       End If
   Loop
   RemoveLeadingZeros = myStr
End Function

Then you can change your code to:

 NewSheet.Cells(j, 9).Value = removeleadingzeros(nName.Name)

I'm confused about what the left(right()) was doing.

But I'm confused about this, too:

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

Maybe using:
mid(nname.name,len(nname)-9,4)

Maybe...

NewSheet.Cells(j, 9).Value _
  = removeleadingzeros(mid(nName.Name, len(nName.name)-9, 4)

> Hi Dave,
>
[quoted text clipped - 263 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message
Dave Peterson - 27 Sep 2007 23:27 GMT
And you can remove the "dim cctr as long", too.

> Don't use that other code.
>
[quoted text clipped - 304 lines]
>
> Dave Peterson

Signature

Dave Peterson

Reply to this Message
AccessHelp - 28 Sep 2007 17:48 GMT
Hi Dave,

Thank you very much for your help and patience.  Your code works perfectly.

Yes, you were right about the "left(right(....))" thing.  I could have used
Mid instead.  I have corrected all to Mid.  I really appreciate you for tips
and suggestions.  Please feel free to share with me if you find anything in
my code that I can improve on.  

Would you recommend anything just to get to 10% of your programming knowledge?

Thanks again.

> And you can remove the "dim cctr as long", too.
>
[quoted text clipped - 272 lines]
> > > > > > >
> > > > > > >> Hi Gary,
Reply to this Message
Dave Peterson - 28 Sep 2007 19:12 GMT
Hang around these newsgroups.  You don't have to respond until you want to.  But
you'll want to try to come up with a solution--or at least with a possible
technique.  Don't be afraid of making mistakes.  There will be enough people
jumping in to "help" you out <vbg>.

You'll find responders that you'll like--read every post of theirs that you
can.  And then compare their answers with other suggested answers.  See which
one looks simpler/faster/easier to understand.  And save the ones you like best
so you can review them at your leisure.

> Hi Dave,
>
[quoted text clipped - 285 lines]
> > > > > > > >
> > > > > > > >> Hi Gary,

Signature

Dave Peterson

Reply to this Message
Gary Keramidas - 27 Sep 2007 19:16 GMT
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.

Signature

Gary

> Gary,
>
[quoted text clipped - 47 lines]
>> >>> >
>> >>> > Thanks.
Reply to this Message
AccessHelp - 27 Sep 2007 16:23 GMT
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.

> In Excel 2000 and later, use Replace. E.g.,
>
[quoted text clipped - 32 lines]
> >> >
> >> > Thanks.
Reply to this Message
Gary Keramidas - 27 Sep 2007 19:15 GMT
try chip' code with the following change

Sub AAA()
    Dim S As String
    S = Range("B2").Text
    S = Replace(S, "0", "")
    Range("B2").Value = S
End Sub

Signature

Gary

> Hi Chip,
>
[quoted text clipped - 44 lines]
>> >> >
>> >> > Thanks.
Reply to this Message
AccessHelp - 27 Sep 2007 20:01 GMT
Hi Gary,

Thank you very much for your patience.  I don't think the code below will
work for me because the values ("00A0") are not specific to particular cells.
The values are derived from "Defined Names".  

If you don't mind, please read my message (4th message above from this) to
Dave Peterson today.  The message has my entire code and has a particular
line that I have problem with.  

Thanks again for your patience.  

> try chip' code with the following change
>
[quoted text clipped - 53 lines]
> >> >> >
> >> >> > Thanks.
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.