Forum Discussion
Solved
text to date conversion
Hello everyone One strange thing happening, i have a file with dates (which are text) But am not able to convert them to date using datevalue function or text to columns or some other methods What...
chahine The "hidden" character is CHAR(95) and it sits the front of the date. Though it shows itself as a vertical line .
Tried getting rid of it with TRIM and CLEAN and SUBSTITUTE but it persists. And TTC indeed doesn't like it either.
However, =RIGHT(A2,10) will return only the date and on my machine it's directly recognised as a valid 10 character date.
Thanks Peter, explode is new to me, lambda also i didnt learn it yet
Do not worry or feel bad about it. It is functionality that has only existed in Excel for the past year or two and is only implemented within Excel 365 (Office 2021 as well if you want to be picky).
If you have the good fortune to have access to 365 it is only necessary to learn how to pass parameters to your formula using the Lambda function. All 'Explode' does is to use a number sequence as a parameter in the MID function that has been around forever to extract the characters from the corrupted date and 'Value' rebuilds the number from valid ASCII characters. The main point for you, is that you know it is the first character that is not valid so that is all you need to ignore.
Either the Riny_van_Eekelen or SergeiBaklan solutions should work fine for you. Mine can happily be left for another problem on another day.
