Forum Discussion
text to date conversion
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.
chahine Wrote my previous answer when I was looking at the file on a Mac. Now, I'm checking on a PC and the vertical line does not show. And when I analyze the text string the "hidden" comes up as character code 63 (95 on the Mac). In UNICODE this becomes 8206 on both platforms. Google tells me it a "left-to-right mark", whatever that may be.
By the way, I need to correct my previous post. After using the RIGHT function, it doesn't become a real date directly, but it can be easily transformed to one with DATEVALUE.