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

Word macro that can call cells out of Excel for a find and replace 0
how can i change a sequence of numbers using find replace 2
Excel Date Formatting 0
Excel Saves Random .xlsm File Everytime I Save 1
Excel date question 0
How do I stop Excel from 'interpreting' my data? 7
Sum of Numbers of a Rolling 6 month Period range 0
How do I stop Excel from auto-inserting a date? 1

Top