How can I stop Excel from formating my infromation as a Date?

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

I have to use edit/replace in excel to format my information quite often. And
anytime I have a value that could be viewed as a date excel reformats the
cell and presents the numbers as a date (which they are not). ex:

6--12 in cell A1
edit replace "--" with "-" and you will get "12-Jun" instead of 6-12

I have also tried using the format option with edit/replace but I can not
find a way to get excel to stop converting my numbers to dates.

Any and all help would be greatly appreciated!
Thanks,
Eric
 
One solution is to use the substitute function to replace the -- with -
something like this

=SUBSTITUTE(A1, "--", "-")

Where 6--12 is in cell A1. You can copy and paste values to allow ou to
remove the formulas.
 
Specify another format for the whole coloum, using a conditional format.
If you create a conditional format, (using simple formula like 1=1),
it should override excel's naturaly tendency to "fix" things for you.

Phil
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I stop Excel from 'interpreting' my data? 7
How do I stop Excel from copying date format from cell above? 2
yearfrac format error? 4
Remove date format? 3
Cell that is formated for Date shows a diffrent number 2
Some date/time cells appear in different format 3
Is it possible to get Excel to stop formatting text as a date? 2
retain custom date format "text" when pasting or reformatting data 2

Back
Top