Excel Auto correct

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do i change auto correct in Excel so it doesnt change my numbers (10-19)
in a cell to a date (Oct 19)
 
You cannot change this feature through autocorrect.

Preformat the cells as Text or precede the 10-19 with an apsostrophe.

i.e. '10-19

The apostrophe won't show in the cells.


Gord Dibben MS Excel MVP
 
You can pre-format the column as Text before you enter any data, or
you can include an apostrophe at the beginning of each data entry,
i.e. '10-19 would show as 10-19.

Hope this helps.

Pete
 
I think there is some bugz working agaist us here. I have a similar problem
and changing the format to text doesn't help and putting quotes in front of
my phone numbers will break the system.

If you have a column of phone numbers like this (imagine hundreds so you
know how annoying this problem is)

0425 888 666
0426 666 888

and I need to remove the spaces so I can match the keys in my database. The
cells are formatted as TEXT and I do a replace all space to nothing and all
the leading zeros disappear! Now they aren't phone numbers anymore. If I
manually delete the spaces by hand the zeroes remain.

So my choice is to manually add loads of zeros back or manually remove loads
of spaces, both EXTREMELY TIME CONSUMING, so much for office automation...

Excel is so smart it's stupid.
 
Try this on your situation:

Say your data is in Column A, starting in A2.

In a helper column, enter this:

=SUBSTITUTE(A2," ","")

And copy down as needed.
This should give you the display you're looking for.

Now, to remove the formulas and leave the data behind:

Select the column of formulas, and right click in the selection and choose
"Copy".
Right click in the selection again and choose "Paste Special".
Then, click on "Values", then <OK>, then <Esc>.

You should now have the type of data you're looking for, where you can copy
it to wherever you like.
 
Back
Top