How can I stop Excel from formating my infromation as a 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
 
J

Jim Thomlinson

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.
 
P

Phil Smith

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 auto-inserting a date? 1
Date Formating Problem 1
Cell that is formated for Date shows a diffrent number 2
How do I convert a specific date to a fiscal quarter ? 10
Defaulting to date format 4
How do I stop Excel from copying date format from cell above? 2
How can I stop date formatting? 2

Top