Forum Discussion
Excel .csv Conversion - Date-Formatting keeps returning even with prior formatting
If there is any point at which you have a text string comprising
2 digits, soft hyphen, 2 digits
and are in an environment with an editor, that is the moment to convert the soft hyphen into another character. If on the other hand, you already have a mix of 15-20, 15-Oct, 5-Oct then you are faced with the task of unscrambling the omelette.
The values you require will be held within the date so a formula
= IF( ISNUMBER(interval), DAY(interval) & UNICHAR(8208) & MONTH(interval), interval)
would be a start to getting a column that you can Copy / Paste values over the top.
PeterBartholomew1 Just tried it and the result is that all hyphen are turned into question marks, likely because the different hyphen is not supported ('26?50' is the result). So it works well before the conversion to .csv but stops working after. Unfortunately, we need the files in .csv format to be in compliance with our database, so working in .xlxs is not an option.
How would I use that formula exactly? I would imagine this is a bug that needs fixing, considering Excel undoes an earlier action because it saves a file, but I can't wait for that to ever happen. If I can use that formula to create a new column that reverts the 5-digit integer string to what it originally should be, I'm okay with that.
Looks like you need to stick to the regular ASCII keyboard characters such as tilde "~", underscore "_" or vertical line "|". The attraction of the non-breaking hyphen or n-dash was their visual similarity to the normal hyphen.
As I understand it, anything you do by formatting is lost once you convert to CSV. I imagine the damage is done once you bring a CSV file back into Excel and it tries to 'make sense' of it. Viewed in a text editor and the CSV file is probably still OK.
The formula idea only kicks in once the damage is done and you have a mix of text, US dates and 'rest of world' dates. By inserting a formula column that returns the day and month for any date (a number) as text you get to see what was in the CSV file before Excel helpfully scrambled it. Copy/paste values could be used to overwrite the misbehaving ranges with something more acceptable.
Not that I would consider this a well-ordered process. I really dislike such manual processing.
One further thought. Have you tried importing the data using Power Query. That should provide the opportunity to edit and adjust the number formats before trying to load to an Excel table.
PeterBartholomew1No never tried Power Query, but I also don't think it's possible. We have a scanning system in place which delivers all data already in excel-files. So there's only importing from one type of excel-file to a .csv. We don't have a text-file of raw data which is then exported to .xlxs.
If, at any point in the process you have a valid file (.txt, .csv, .xlsx), Power Query will import it and, assuming the data is laid out as a database table, it can be loaded to a Table in Excel. If you chose you could separate the upper and lower limits into two fields or replace the hyphen by underscore (say) before loading to the Excel table. Fresh data could be appended from multiple files.
If you need more flexibility, SergeiBaklan 's approach should offer that.