Excel cost us few thousands dollar for changing SKUs to dates

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.

From the horse's mouth: https://support.microsoft.com/en-us/...4-72cec11c4ed8

Microsoft Excel is preprogrammed to make it easier to enter dates. For example, 12/2 changes to 2-Dec. This is very frustrating when you enter something that you don't want changed to a date. Unfortunately there is no way to turn this off. But there are ways to get around it.

Preformat the cells you want to enter numbers into as Text. This way Excel will not try to change what you enter into dates.

If you only have a few numbers to enter, you can stop Excel from changing them into dates by entering:

A space before you enter a number. The space remains in the cell after you press Enter. (See Notes)

An apostrophe (‘) before you enter a number, such as ’11-53 or ‘1/47. The apostrophe isn’t displayed in the cell after you press Enter.

A zero and a space before you enter a fraction such as 1/2 or 3/4 so that they don’t change to 2-Jan or 4-Mar, for example. Type 0 1/2 or 0 3/4. The zero doesn’t remain in the cell after you press Enter, and the cell becomes the Fraction number type.

Originally Posted by aliplumb

Literally when I open excel it goes through my file and changes whatever close to a date to a date.

I have never once in 30 years of using Excel seen pre-existing data changed automatically to dates upon opening a file.

Microsoft says you can't simply turn it off. The alternatives they provide are ones you have rejected.

Formatting cells that will contains SKUs as Text is your best option. The cell will continue to be Text no matter what you do as far as filters, prefix and postfix. You can create a template that is preformatted with the desired columns/rows as Text to make the process easier.

Not sure how you get what you get. Concatenating cells, however you do it, should give you a Text output.

3-3211 Cell formatted as text (A1)

3-3211-29 =A1&-29
3-3211-29 =CONCATENATE(A1,-29)

3-3211 Cell formatted as text (A6)
-29 Cell formatted as General (A7)

3-3211-29 =A6&A7
3-3211-29 =CONCATENATE(A6,A7)

Originally Posted by aliplumb

Like I said I have tried to apply those methods but they keep changing back because we need to keep these SKU as numbers and add options so the SKU from 3-3211 becomes 3-3211-26 and 3-3211-29 and 3-3211-30 and so on. This way we create options for the products.

Excel will never change data to a date when entered into a cell that is formatted as Text. I am trying to get that to happen using the examples you show here and it is never changing it back from Text to a date.

the SKU from 3-3211 becomes 3-3211-26

How do you make this change? Edit the cell? VBA? Paste? I just can't reproduce the behavior you are describing once I format a cell as Text.

Perhaps in your 30 years of working with excel you didn't come across this issue of SKUs with that format.

Sure I have. But I have never seen data that was correct when a file was closed spontaneously change just because the file is opened again, unless it is directly linked to an external file.

I sympathize with you. An application I used at work (and was, fortunately, one of the administrators for) had certain severity ranges that were coded, and one of them was 10-13. Every time I downloaded the data for import into an access database, the first step was the output of a xlsx file. In that file EVERY TIME the 10-13 was changed to a date of october 13 of whatever year it was. Since I had administrator privileges to the application I went into it and added ' to the 10-13 so it never changed the 10-13 to a date in the xlsx download again. It always only changed that cell to a date until I added that, all the other severities in that column stayed as general.

So the only thought I can add to this is if you can get to the administrators of the application to add the ' to all the SKUs your problem should be solved.

"...And that's just one import. ..."
Are you importing csv/txt files? Are the sku's in specific columns or, are they embedded in text strings?

OK, in testing, if you use a Global Replace to remove "NB-", only the first of those examples will produce a date. In this case, 01/03/3286, with a custom format displaying ad Mar-86. This happens regardless of whether the cell is formatted as General or Text.

Formulae such as =SUBSTITUTE(A2, "NB-", "") and =RIGHT(D2, LEN(D2)-3) will remove the "NB-" and output text. The cell would need to be formatted as General.

This short VBA subroutine will add a single quote to the beginning of each value in column A. You cann then do a Global Replace or individually edit each cell to remove the "NB-". Note that deleting the single quote with the "NB-" will put you back to square 1.

Please Login or Register  to view this content.

... however I need admin access to remove the useless feature of excel turning any number to date.

There is no feature you can turn off. I refer you back to post #2:

Microsoft Excel is preprogrammed to make it easier to enter dates. For example, 12/2 changes to 2-Dec. This is very frustrating when you enter something that you don't want changed to a date. Unfortunately there is no way to turn this off. But there are ways to get around it.

Originally Posted by aliplumb

find and replace

I owe you apology, you are correct. I had not tried anything like this. If I enter

NB-3-3286

into a cell and format the cell as Text, then

Find: NB-
Replace with: < null string >

it changes the cell format from Text to Custom and changes the data to the date 3/1/3286.

This does not happen if I change the cell manually.

However, this is a deeply embedded "feature" of Excel and there is no user-controlled setting that you can turn off. We might be able to provide a solution with more information on how you import this data.

This should be quicker with large volumes:

Please Login or Register  to view this content.

given your example, the best way to clean up the data would be to use a formula like this to remove everything before and including the first hyphen, and after and including the first forward slash (if there is one):

Please Login or Register  to view this content.

or this which returns the first 6 characters after the first 3 characters:

Please Login or Register  to view this content.

Is there a way to replay A2 with a multiple selection in your formula? The one that changed to date, those are the parent SKU and the rest are variants of a product. and I have 170k Parent SKU

I don't understand what you mean. You have outlined a problem and have been drip feeding examples.

Whilst you understand what your data looks like and your processes, we don't. The VBA code is applied to every cell in column A. It adds a single quote to the start of the existing content making it text.

What you do after that is up to you.

Now at post #20 and no further forward - see big yellow banner - a worksheet is worth a thousand words (pictures) and will most likely get you a solution from the next poster.

Ok, I've been following this thread since I posted at #7, so it appears you do not yet have it solved.
So my point in post 7 was if you have access to the source data which you do not.
but to a similar point, you were saying you use find and replace where you use find "NB-" and replace with nothing.
I just tried it with your examples and yes with leaving replace blank you get a date, BUT if you put ' in the replace I get 3-3286.
Can it be as simple as that for you?

Something so simple is only solvable by turning off auto dating, I hope Bill Gate consider this as the next update LOL.

I don't know how to say this another way: that is NOT going to happen.

All I want to know if I can expand the selection, in case of your formula from A2 to A2:ABC2.

Short answer: no. The formula would go in a Helper column and can only refer to a single column. That said, it is quite possibly TOTALLY IRRELEVANT. That would be because I have no idea what your process(es) involve, despite the snippets of information you provide.

Where does the data come from? What format is it in? What does it look like? How does it get into Excel? Is it a single column, or lots of related data? What do you do with it? Is it put in a separate sheet? Or is it tagged onto the end of existing data? What do you do with the data? When, why and how do you edit the data? What variations on a theme do the SKU prefixes have? Do you just get parent records and add child records?

Why has it cost you thousands of dollars? How could you not notice that the data had become "corrupted"?

If you provide realistic examples of the data, we might be able to work out how to reverse the corruption. The "corrupt" data entries can be identified as dates (albeit with a custom format). I suspect it is possible to check the entries, format them as full dates and remove the day part.

All that said, I’m out until we can see some meaningful data and have a full description of what you do with it.