Excel translated w/o my permission.

May 14, 2007 12:49 PM

Excel - automatically converted my numerical values -

I just pulled a significant amount of data.
In several of the columns when translated, excel automatically made them dates. Drats!

For example:

Said data was: 11-15
Instead this is reflected: 15-Nov

How do I convert this back to what should actually be reflected. I've perused the formatting portion, looked through the customization bar, scoured older posts for similar problematic scenarios....nothing.

Any ideas would be appreciated. :)

posted by seeminglyshy to computers & internet (8 comments total)

Right click on the header of the column (like A),
select Format Cells,
Click "General" (it is probably on Date) in the list on the left.

All done!
posted by rmless at 12:55 PM on May 14, 2007

I played around for a bit but couldn't figure out how to convert the format from date back to text.

If you can, copy the data from the source, and then use Paste Special...Values instead of regular Paste.
posted by mbd1mbd1 at 12:58 PM on May 14, 2007

Depends on what you want:
If it is actually a date and that's what you want, then just format the cells (per rmless suggestion) by going to "custom" rather than the "general" or "date" options and type mm-dd above the list of custom formats.

However, if Excel converted it to a date, and you don't actually want anything to do with dates do this:
Assuming that J53 is where the data is, just type =MONTH(J53) & " - " & DAY(J53) in an adjacent cell. The space on either side of the dash is not necessary if you don't want it. This now means though that the data is text.
posted by Mave_80 at 1:03 PM on May 14, 2007

rmless is right, except I'd add this:

"...
select Format Cells,
Click on tab that says "Number"
..."
posted by muddgirl at 1:11 PM on May 14, 2007

Another approach:

Highlight the existing data.
Select the Data menu, then "Text to Columns"
Selct the Delimited Radio Button, then the Next Button
Check the Other box, then put a forward slash in the field to the right
Select the next button.
Highlight the column that contains the year field, and select the "Do not import" radio button.
In the Destination range finder field, identify a column that contains no data (!)
Select the Finish button.

Next, combine the "month" and "day" data using the following formula: =C1&+"-"&+D1

Other solutions may be more elegant...
posted by grateful at 1:24 PM on May 14, 2007

The best way is:

1) Rename your file to whatever.txt (NOT csv).
2) Open it from Excel.
3) Use the Text Import Wizard, which lets you specify the column type for each imported column. Change that column to text from date.

I don't know any way to just open this on any given file. It seems to use the extension to decide when it needs it.
posted by smackfu at 1:33 PM on May 14, 2007

To each and every one of you: many thanks.
posted by seeminglyshy at 4:50 PM on May 14, 2007

A related question that I asked a while ago that might be helpful in some way.
posted by EndsOfInvention at 2:37 AM on May 15, 2007