Forum Discussion
retaining text format
Amitfre1 MS has worked on the issue of Automatic Data Conversion (ADC). In my Excel Insider version I have a set of options that allow me to choose what and what not to convert under Excel Options, Data.
It used to be labeled "BETA" and it was part of the Advanced options. I haven't seen an announcement on this feature (perhaps I missed it). but it could be that it is now generally available in Excel365.
If not, consider using Power Query to connect to the data that you scraped from the web. Then you take control over data conversion (or transformation in PQ terms).
Edit Sep 2, 2023:
I now found the note I was looking for earlier. The ADC option is still for Windows Insiders only (Version 2207 [Build 15427.20000] or later)
I wasn't able to find Automatic Data Conversion in Advanced Options, yet any changes to my date entries automatically converted. However, the very bottom of Advanced Options has two boxes for Lotus compatibility settings. They were checked. I unchecked them and the problem was resolved.
rhoneyman If the Automatic Data Conversion (ADC) options are generally available already (which might not be), they sit under Excel options , Data. When this feature was in the beta phase they where stashed away in Advanced options.
I'm aware of the existence of the Lotus compatibility settings but not of the impact they may have on ADC. I don't recall that these were ever checked in my set-up and Excel persisted to automatically convert data upon import, before I could select the new ADC options.
By the way, we are talking about data conversion here, not just 'date entries being converted'. That is, dropping leading zeroes, transforming very large numbers or digits surrounding the letter E into scientific notation, or for example converting a text like "18/04/5405" to a date.
(ignore "s)
"1/1/2024" becomes "=1/1/2024". The former resolves to 45292, while the latter resolves to 0.000494071146245059. Is that not ADC?
- preceding each value with an apostrophe.
e.g.
'2023-06-07
