Forum Discussion
Excel .csv Conversion - Date-Formatting keeps returning even with prior formatting
PeterBartholomew1In a global edit, do you mean to reformat the column? Or simply find and replace? Most 'global edit' searches lead me back to reformatting, it seems. I could replace all the different hyphens I guess, although I'm not sure how this would work when entering new data and then storing it again. At the moment, the moment I save the file after the conversion to .csv, it happily destroys the formatting already in place and I'm back at square one. After I did that, I can't get the actual input back, as Excel has already decided to create a date-column and as a result, any formatting automatically produces this string counted from 01-01-1900 I believe.
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.
PeterBartholomew1Well, the weird thing is that I'm not stuck with the omelette left unscrambling when I initially save the file as a .csv. It only happens again after I make any change and save it again. As we get the data from a different program entirely, we get in the form of an existing .xlxs file, and I'm not sure how to then re-edit that hyphen into a different character as I'm not making the .xlxs file myself with any editor of sorts. Is there any direction you can point me towards to get that going? Or otherwise, how to best use the formula you described? I'm assuming this has to utilize the initial column and then transform into a new column (meaning I'd likely have to do this every time I get additional data, which I what I wanted to prevent).
What I had in mind was to select the offending column in Excel before it is corrupted and use Replace (Ctrl+H) to replace the soft hyphen by another character.
Avoiding the use of CSV files would appear the be good abut, assuming that is not possible, and the eggs are broken then type the formula into a helper column and use the resulting values from there.