Homework: Can you extract dates from text? - ? being counted (in error) . . .

[polarisking]

but it's not in the list array. In other words, the cell containing


Date of birth?!? What is that now?


has a count of 3 even though my list array has 11 entries - 0-9 and /.


Diagnosing the formula finds that the ? character is being counted.

 

[Luke M]

I assume you're talking about this article?

http://chandoo.org/wp/2012/08/17/extract-dates-from-text/


If so, what formula(s) are you using that's causing an error?

 

[polarisking]

You're referencing the right article.


When I'm counting the number of numeric characters in the cell using

=SUMPRODUCT(COUNTIF(lstDigitsWithSlash,MID(B4,ROW($A$1:$A$200),1)))


The array 1stDigitsWithSlash contains 11 items the "/" character as well as as 0-9.


When you evaluate the phrase "Date of birth?!? What is that now?", you get a count of 3 (the total of the ?'s). I'd expect a count of 0 since 0-9 and / aren't found in the array.


I'm suspecting it's because ? is being interpreted in some wild-card-like capacity, but I just don't know.

 

[keymaster]

That is right. ? is a wild card when used in COUNTIF (and SUMIF, Search, VLOOKUP etc.)

One way to overcome this is to replace ? with something else, like ! (as you do not need to worry about ? symbol at all). So the below should work:


=SUMPRODUCT(COUNTIF(lstDigitsWithSlash,MID(substitute(B4,"?","!"),ROW($A$1:$A$200),1)))