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.
As a 365 user that still has new toys to learn, I went for overkill. Explodeλ is a function I use quite often to split a string into characters
Explodeλ(d)
= LAMBDA(d,
LET(
n, LEN(d),
k, SEQUENCE(1, n),
MID(d, k, 1)
)
)I then went on to write a function Valueλ that removes any non-ASCII character and turns the result into a date/value (if possible)
Valueλ(d)
= LAMBDA(d,
LET(
c, Explodeλ(d),
a, IF(UNICODE(c)<256, c, ""),
s, CONCAT(a),
IFERROR(VALUE(s), s)
)
)The good thing is that, once written, the user neither sees nor needs to think of any of this. The worksheet formula are
= Valueλ(@date)
or
= MAP(date, Valueλ)depending on whether one is processing a single scalar value or an array of potential dates.
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.