Stop autoformating from text to custom date.

dwrmann

New Member
Joined
Nov 22, 2005
Messages
1
Excel is automatically reformatting cells from text to date. I have cells that I want as text that are "Oct-97", "Nov-97", etc. I want the retained as text so I can load the excel sheet into Pagemaker. As dates, the text is too large for my cells causing the "###" to be in the cell. As text, there is no problem.

I am using VBA to read "Oct-97" from one worksheet and placing it into another worksheet as text. Excel automatically converts the format from text to Custom "mmm-yy". I do not want this.

So how can I stop excell from changing my cell formats?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hmmm... I am having problems recreating your problem.

Can you post your VBA code?
 
Upvote 0
Stumbled across this thread while looking for an answer to the same question (which I haven't found)...

When you enter, for example, 1-3 into a cell, it gets converted to a date 01-Mar automatically, but I want to preserve it in the format as originally typed.

I know if you preceed the entry with an apostrophe e.g. '1-3, the text is preserved, but my spreadsheet is being distributed to people where only some have knowledge of this function.

So, does anyone have a handy bit of VBA* that will preceed my text string with an apostrophe automatically, but ignore the entry if someone enters a text string beginning with an apostrophe? Or if this a cumbersome way of doing it, and alternative solution!

many thanks,

Ian

*VBA isn't my strong point, but I'm trying to learn :oops:
 
Upvote 0
Howard

Are these 'real' dates?

How are they being copied?

Ian

Try formatting the range/cell as Text before you enter anything in the cell.

Note you should really have started a new thread, I think your problem is quite different to Howard's.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top