Forum Discussion

chahine's avatar
chahine
Iron Contributor
Jan 20, 2023
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...
excel
training
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jan 20, 2023

    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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 21, 2023

chahine 

Another option to exclude hidden character in front of date is to convert as

=DATE(RIGHT(A2,4), MID(A2,5,2), MID(A2,2,2))

Resources