2

My cells are set to text format but when I find and replace numbers in them they automatically change themselves to date format even though I don't want that. How do I globally disable automatic formatting in Excel?

2
  • 1
    As far as I know, that is not possible. I would use VBA. You could use the .Find method to locate cells containing the number to be replaced; then use the Replace function to change the string in the cell, and write the results back to the cell.
    – Ron Rosenfeld
    Jul 29, 2016 at 0:50
  • :/ That's what I was afraid of. For now I'll just copy the cells into notepad.
    – user3780895
    Jul 29, 2016 at 16:43

1 Answer 1

Reset to default
1

The workaround for this would be to put an apostrophe (') in front of your "Replace with:" value. Just one more keystroke.

Find and Replace with leading apostrophe to keep text

BTW: Microsoft indicates that in Excel 2013, using the TEXT format will stop it from auto-converting numbers to dates. support.office.com: Stop automatically changing numbers to dates

1
  • 1
    Thanks, it works. using the **TEXT** format will stop it from auto-converting numbers to dates
    – John Lee
    Dec 25, 2020 at 9:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.