text to date conversion

[chahine]

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 i found out is that there is hidden character in the dates , i tried len function showed 11 characters

so when i tried to remove this hidden character by backspace at the beginning of cell ,it changed to real date /number format

But how to remove this hidden character ?? i tried clean function but it didnt work, very strange, first time i encounter something like that although text date and region of my computer are same

Thanks

[#2 · Accepted solution]

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

[#3]

Thanks, but why this happens? strange

[#4]

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

[#5]

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

[#6]

Thanks Peter, explode is new to me, lambda also i didnt learn it yet

[#7]

@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))

[#8]

@chahine 

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.