Question Locked

Default Date Formatting?

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
When I copy and paste data into Excel spreadsheet, values like "6-10" get converted into dates and becomes "Jun 10". I would like to prevent it from happenning. I am getting around it for now by highlighting the whole worksheet and changing cells format to 'text'. Is there a default setting that I can change? Thank you!

6 replies

by thumbsup2

You might try typing all numbers as mentioned above with an appostrophe first to indicate that it IS text so that autoformat doesn't take over. Example

Instead of typing 6-10
type '6-10

The appostophe won't show in the cell, either looking at it or printing it. You won't see that it's even there unless you click into the cell itself and look at the formula bar.

Note: You don't have to format the whole spreadsheet as text. You can format JUST the column that you don't want to autoformat and leave the rest as they are. That way your numbers remain numbers (right aligned).

by anna.a.zelikman

Unfortunately, the data is not being typed in. It is an output of a SAS program and the column in questions contains ranges, i.e. "1-3", "3-5", "6-10". I do not control what the program can output. Therefore, I am looking for a way to possibly change the default of the spreadsheet. Thanks very much for your answer though!

by thumbsup2

I saw the part in the first post that says you're copying and pasting... now you say you're getting the output from another program. I had assumed you were copying and pasting from another Excel spreadsheet.

Does your program have an export feature where you can save it as a comma or tab separated text file and then import that into the spreadsheet? During import, you can tell Excel what types of data are in each column (ie: text instead of a date).

by scott_heath

Go to Control Panel > Regional Settings and check your date formats. Try changing them there and see what happens. These are, after all, Microsoft programs! :)

by scott_heath

I get what you are saying now. It isn't a date and you don't want it formatted differenty, you just want it to stay the same. Create a template that is preconfigured with all cells set to text.

This discussion is closed to new replies.