Help with leading zero getting strip by Excel when saving to a .cs

K

klafert

I am saving a worksheet from Excel to .csv I have a number 0000457, but when
I open it up the .csv file then the zeros all goine. I can show them in
Excel if I format the column as text. Need answer ASAP!!
 
M

Mexage

Type a ' before the number.

Hope this helps; if it does, please rate my post.

G.Morales.
 
P

Peo Sjoblom

It gets stripped when opening it in excel, not when saving as CSV, you can
change it to a txt file and use the text import wizard and import it as text
or if the number of digits are equal use a custom format like 0000000

Regards,

Peo Sjoblom
 
K

klafert

That only works when the Spreadsheet is the the .xls format and not the .csv
format.
 
R

ras

When I prefix a number in a CSV with a single quote, the single quote appears
in the cell. If I then select the cell, put my cursor in the edit bar and
hit tab, it'll reinterpret the quote and treat the cell as text.

Is there any way to write a macro that visits each cell and does this? (No,
recording this into a macro does no good.)

This seems to be a common problem- and no, treating it as a txt file and
specifying all the formats is not a decent solution. I could write a perl
script to do this (or presumably a Basic script), but that seems like a lot
of work for a simple problem.
 
D

Dave Peterson

I created a .csv file that looked like this:

'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345

And this worked ok:

Option Explicit
Sub testme02()

Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "No text values found"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
If Left(.Value, 1) = "'" Then
.NumberFormat = "@"
.Value = Mid(.Value, 2)
End If
End With
Next myCell

End Sub


I created a .csv file that looked like this:

'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
'1234,'1234,'2345,'2345
 
Top