Excel translated w/o my permission.
May 14, 2007 12:49 PM Subscribe
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. :)
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. :)
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
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
Best answer: 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
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
"...
select Format Cells,
Click on tab that says "Number"
..."
posted by muddgirl at 1:11 PM on May 14, 2007
Best answer: 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
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
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
Response by poster: To each and every one of you: many thanks.
posted by seeminglyshy at 4:50 PM on May 14, 2007
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
posted by EndsOfInvention at 2:37 AM on May 15, 2007
This thread is closed to new comments.
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