CSV to XLS with decimals

SEO

New Member
Joined
Feb 2, 2010
Messages
10
Hi there!

I got a bunch of data stored in a CSV format, where some of the values are decimal values (two decimals). When I convert this into XLS something strange happens. When the first two digits (before the . and the decimals) are from 1 to 12 the XLS document doesnt display the number, it displays it as a date. I have checked that all my cells are formatted to general, and also tried to format them to scientific with 2 decimals and number with 2 decimals, none of which helps :confused: Any idea how to fix this?

(not surprisingly) the values without decimals remains as they wore in the CSV format.

Help will be greatly appreciated!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,500
Office Version
365
Platform
Windows
How are you converting your CSV file to Excel?
Are you simply opening the CSV file directly in Excel?
I would not recommend doing that, as Excel will try to "guess" the format of each cell, and could guess wrong, like you are experiencing.

Rather, import the CSV file into Excel instead (Get External Data -> Text). This will invoke the Import Wizard, where YOU can specify the format of each column.
 

SEO

New Member
Joined
Feb 2, 2010
Messages
10
When I open the CSV document in Excel, all the data gathers in cell A1, so I then use the "text to column" feature to allocate the correct values to the correct cells.

I tried the "get external data" procedure now, but it results in the same wizard as the "text to column" feature, and hence I get the same problem with the dates.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Can you give some examples? What is the date separator in your Control Panel Regional settings?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,500
Office Version
365
Platform
Windows
When you are performing the steps of the Text to Columns wizard, or the Import Wizard, are you specifically going through and specifying the format of each and every data field?
 

Forum statistics

Threads
1,082,478
Messages
5,365,789
Members
400,851
Latest member
FrankNStein

Some videos you may like

This Week's Hot Topics

Top