Stop auto correction of number into a date

Your complaints are entirely legitimate, just as those from the 19 pages before. Obviously, there is no avail in addressing Microsoft, as it turns out. I take it so that they, in their effort to meet formal habits of financials, loose interest in the application in the other less lucrative branches. We may abandon Excel, but, as you remark, sticking to has its own benefits.

Anyhow, there were bidden several ways, how some faults can be treated. At least the amendment of wrong conversion of  CAS ID’s can be a next useful example of such a working method. (Poor old CAV nomenclature does not deserve such a barbarity of newer Excel.) The gist is in the obstinate format of CAS (something like 0-00-0) so that it is clear, which part of a wrongly created date should be linked to the part of CAV ID. As the issues of this kind may be numerous, I tried to create a macro in rather a slightly comfortable package.

The sub browses the cells. If  correctly transferred format (with hyphens) is encountered, it does nothing. In the opposite, however, the proper transposition to individual ID parts takes place. The  movement is dependent of the format of the wrongly created date and accordingly processed.

In calling it, you have to have one or several cells with doubtfully transferred cells selected; the proof will run on the hinted selection.

Option Explicit

Sub Date2CAS()

'Sub corrects wrongly converted CAS ID's from date to string format

Dim S As Variant, MonthLeading As Variant, T As Variant, D1 As Variant, D2 As Variant

Dim MB As Long

Dim F As String

Dim Hyphen As Boolean

Dim Cell As Range

Set S = Selection

If S.Count = 1 Then

MB = MsgBox("- downward list from here (Yes)" & vbCrLf & _

"  (can't be undone)" & vbCrLf & _

"- only this single cell (No)", _

vbYesNoCancel + vbDefaultButton2, _

     "Fix wrong dates into correct CAS numbers")

If MB = vbCancel Then Exit Sub

If MB = vbYes Then

Set S = Range(S, S.End(xlDown))

End If

End If

Application.ScreenUpdating = False

For Each Cell In S

F = Cell.NumberFormat

Cell.NumberFormat = "@"

Hyphen = InStr(1, Cell, "-") > 0

If Not Hyphen Then

T = Cell.Value

If IsNull(MonthLeading) Then _

MonthLeading = InStr(F, "m") < InStr(F, "d")

If Not MonthLeading Then

D1 = Day(T): D2 = Month(T)

Else

D2 = Day(T): D1 = Month(T)

End If

Cell.Value = Year(T) & "-" & Format(D2, "00") & "-" & D1

End If

Next Cell

Application.ScreenUpdating = True

End Sub

::After correcting a heavy mistake.

Apologies and regards

PB

So I am not the only frustrated user.

I have a pretty large .csv file with biochemical data, and one column has CAS IDs (Id's for chemical compounds). Many cells in this column are automatically formatted to dates, just because the CAS IDs have dashes in them. For instance 2439-07-8 becomes 08/07/2439, and if formatted back to text, number, or general it becomes 197056, whatever the logic is there.

Setting aside that this automatic formatting can apparently not be disabled, what I find the most ridiculous here is that the original cell content is lost. I mean it is one thing to automatically format it to date, but why does it turn into a random number when changed back to a non-date format?

Now I tried to import the data into Excel after formatting a worksheet to all text (not an ideal situation) but that did not solve the problem at all. Pasting the data also does not work. The table is simply too large (the .csv alone makes 400 MB). Granted I do not work on the data in Excel, that would be ludicrous, since Excel does not have the functionalities to work with such large data. But looking at the data and even doing some aesthetic changes is easier in Excel than it is programmatically, or even presenting part of the data is easier/more intuitive in Excel.

Bottom line, I agree with the opinions stated here that Excel is not suitable for professional use of large data. Professionals don't need "user-friendliness", but flexibility. And that is thoroughly missing in recent implementations of Microsoft software.

Maybe some day the idiots at Microsoft excel would be kind enough to stop thinking that every cell must contain a date.   Even if the default was general, there would be fewer ridiculous conversions.     To show how bad the the auto date feature is, it converted a CHEMICAL FORMULA into a date the other day.   Yes, I typed in FeBr3 and it instantly knew I must have been thinking 03-Feb  NOT.   All the work arounds are doable, but a waste of the customers time.  HELLO MICROSOFT, THERE ARE CUSTOMERS.  PLEASE LISTEN TO THEM.