retaining text format

[Amitfre1]

Hi, I have this problem when I scrap data in the format of XXXX-XX-XX but when I open the Excel file it turns into a date format as follows:

How do I keep the original format I scrapped?

Thank you

[#2]

@Amitfre1With General-format cells, Excel looks at the user input for each cell (including a pasted value) and tries to determine if the input is a formula, a date value, a time value, a Boolean value, a percentage, a currency amount, or some other number*.  If Excel decides it is any of those, it stores the value differently than the input characters.  So some of your Case Number values looked like they were dates (in the YYYY-MM-DD format) and Excel stored them as dates, and displayed them in the date format it felt was appropriate based on your regional settings, etc.

The correction will involve two of these three steps:

1. Manually edit each (inappropriate) date value in that column to the correct value, preceding each value with an apostrophe.  As each cell is changed, Excel will then store those characters (rather than an internal representation of a date, as was the case before this edit).
2. When that editing is done for all the bad-content cells…  If there is no content beneath the table in the Case Numbers column, select the entire spreadsheet column, and format it as Text.  Then you can skip step 3.
3. Otherwise, when that editing is done for all the bad-content cells, select all the cells in that table column (including any empty cells), and format them as Text.  Then, in the future, as table rows are added, the new Case Number cells will probably already be formatted as Text, and Excel will not try to interpret their input.

* I might be forgetting something in this list.  Not important now.

[#3]

@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)

[#4]

@Riny_van_Eekelen 

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.

[#5]

preceding each value with an apostrophe.
e.g.
'2023-06-07

[#6]

@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.

[#7]

@Riny_van_Eekelen 

(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?

[#8]

@rhoneyman If that happens and you don't have the feature I described, you'll have to live with it and properly import data via the legacy text wizard or preferable Power Query.