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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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 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



"Dave Peterson" wrote:

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" wrote in message
...

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" wrote in message
...
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" wrote in message
...
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

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


--


Gary


"AccessHelp" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.

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" wrote in message
...
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" wrote in message
...

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" wrote in message
...
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" wrote in message
...
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 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.

"Gary Keramidas" wrote:

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


--


Gary


"AccessHelp" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.

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)


AccessHelp wrote:

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

"Dave Peterson" wrote:

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" wrote in message
...

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" wrote in message
...
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" wrote in message
...
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


--

Dave Peterson

And you can remove the "dim cctr as long", too.

Dave Peterson wrote:

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)

AccessHelp wrote:

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

"Dave Peterson" wrote:

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" wrote in message
...

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" wrote in message
...
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" wrote in message
...
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


--

Dave Peterson

--

Dave Peterson

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.



"Dave Peterson" wrote:

And you can remove the "dim cctr as long", too.

Dave Peterson wrote:

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)

AccessHelp wrote:

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

"Dave Peterson" wrote:

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" wrote in message
...

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" wrote in message
...
Hi Gary,

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.




AccessHelp wrote:

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.

"Dave Peterson" wrote:

And you can remove the "dim cctr as long", too.

Dave Peterson wrote:

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)

AccessHelp wrote:

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

"Dave Peterson" wrote:

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" wrote in message
...

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" wrote in message
...
Hi Gary,


--

Dave Peterson