Forum Discussion
Excel .csv Conversion - Date-Formatting keeps returning even with prior formatting
Could you perform a global edit and replace the soft hyphen '-' (hex 00AD) by an En Dash '–' (hex 2013) or possibly a non-breaking hyphen (hex 2010). These would be visually similar to the original but Excel would no longer try to convert the strings into date values.
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.
