I work at a company where I import large excel sheet containing our product info to our website. It often happens that SKUs are similar to data, sometimes even a number like this 3-3231 is treated as date in excel and excel magically decides to change it to 31-Mar.
Now I do understand the whole changing numbers to text or adding the ` sign to force numbers not to change to date, I want to have a way completely turning off auto-decisions excel makes. This issue has cost of a lot of money because it does not prompt for a auto conversion it just does it without even applying any edit to that particular cell. Literally when I open excel it goes through my file and changes whatever close to a date to a date.
I have looked all over web to find an answer but they all require me to apply a layer on what's already there but I want the default setting of my workspace be that nothing changes automatically. Excel date conversion literally makes no sense and is not helping at all not to mention it has cost us thousands of dollar trying to fix the imported SKUs that looks like date in our system.
Adding ` to the sheet before the number is just another useless solution because we don't want our SKU have ` in the beginning. and changing numbers to text is not an option because we do a lot of filters and pre-fix and post-fix of our own to the SKU and in the process it keeps changing to number or I need them to be number and not text at all.
Please advise with a working solution that turns this "feature" off.
Sorry if I am not clear or overly descriptive. This is how I do it: Take the following in a excel sheet and try removing prefixes such as NB- and see what happens: Even after you turn them to text and run formula like find and replace with empty string, it turns them to number and then dates.