PREVENT conversion of text to number?

I have a CSV file that I will open (or import) and which has one column that contains serial numbers. The problem is that some of the numbers have this format: 123E567.

Excel promptly converts this text to a number: 1.23E+569. Even if I then change the format of the cell to text, it still keep the text 1.23E+569.

How can I either prevent the original text from being converted to begin with or how can I correct it after the fact?

Best regards,
Ken

Change the name of the file from .csv to .txt.

Then when you do file|open, you'll see text to columns wizard pop up. You'll be
able to specify each field--including this one as Text.



celticvalley wrote:
>
> I have a CSV file that I will open (or import) and which has one column
> that contains serial numbers. The problem is that some of the numbers
> have this format: 123E567.
>
> Excel promptly converts this text to a number: 1.23E+569. Even if I
> then change the format of the cell to text, it still keep the text
> 1.23E+569.
>
> How can I either prevent the original text from being converted to
> begin with or how can I correct it after the fact?
>
> Best regards,
> Ken
>
> --
> celticvalley
> ------------------------------------------------------------------------
> celticvalley's Profile: http://www.excelforum.com/member.php...o&userid=26360
> View this thread: http://www.excelforum.com/showthread...hreadid=396287

--

Dave Peterson

Thank you very much, Dave. This worked great.

Have a nice day.
Ken

Reviving an old thread...

How can the solution given here be replicated using VBA (i.e. when saving a .csv file to .xlsx such that numbers stored as string are not converted to scientific notation)?

The code I currently have is:

Sub CSVtoXlsx()
Dim CSVfolder As String
Dim XlsFolder As String
Dim fname As String
Dim wBook As Workbook
 

inCSVfolder = "c:\temp\output\"

CSVfolder = inCSVfolder
XlsFolder = inCSVfolder

fname = Dir(CSVfolder & "*.csv")

Do While fname <> ""
   Set wBook = Workbooks.Open(CSVfolder & fname, Format:=6, Delimiter:=",")
   wBook.SaveAs XlsFolder & Replace(fname, ".csv", ""), 51
   wBook.Close False
fname = Dir
Loop
 
End Sub