Text to columns: How to prevent date formatting of decimal numers?

Question

With a dataset such as this:

Date, Value
04.03.2020, 13.35
04.03.2020, 13.8
04.03.2020, 21.21

You can split the data using Data > Text to columns and specifying the value separator.

The problem is that Excel recognizes 13.8 as august 13, so the output is:

Date    Value
04.03.2020  13.35
04.03.2020  13.aug
04.03.2020  21.21

How can I make sure that Excel never interprets decimal numbers such as 13.8 as dates?

I'm working in a region that uses , as decimal separator, but I often have to work with data that is set up with . as the decimal separator. One work-around is of course to replace , with ; and . with , before opening a .csv file. And if the only other solution is to set it up with VBA, I'm perfectly able to do so myself. But I often find myself trying to help colleagues on their computers without my own VBA configurations ready to go. So if there's any other way to do this using standard Excel system settings, that would be great!

This little problem has bugged me for years and I'm eager to get rid of it once and for all.

Edit:

I'm running Excel version 1908 on Windows 7, Office 365.

This problem often occurs when I'd like to inspect csv files that are recognized as Excel files on my system. There have been suggestions to format the cells as General before splitting the data. That does not seem to work on my end.

Answer 1

Another solution is to get what you want with formulas:

Formula for Dates:

=TEXT(LEFT(A2,FIND(",",A2,1)-1),"General")

Formula for Values:

=TEXT(MID(A2,(FIND(",",A2,1)+2),(LEN(A2)-(FIND(",",A2,1)+1))),"General")

Results:

During Text to Column process at Step 3 you have the option to to select Data Column Format you could also select the Value column and play around with Text or General formating.