Forum Discussion

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...

date.xlsx10 KB

excel

training

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.

PeterBartholomew1

Silver Contributor

Jan 20, 2023

chahine

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.