Regular expression for a time format

Question

I am trying to search for cells in a column containing a time in the format of 00:00 using the MATCH function. I have tried things similar to MATCH("??:??",A:A) and MATCH("?*:?*",A:A) with no luck. How can I form a regular expression of 2 digits, then a colon, then 2 digits?

Have you ever used regex? What regex expressions have you tried? — Kyle, Aug 30 '16 at 18:07
Is the time that is stored in your cells formatted as time or text? If it's formatted as time, then the actual value is a decimal that you are searching for. Also, the `match()` function in excel doesn't take regex as an argument, so regex isn't really the way to go here. — JNevill, Aug 30 '16 at 18:09
It is stored as the custom format of hh:mm. Is there no way to use `match()` and use the hh:mm format as the first argument, similar to how "ZZZ" as the first argument returns true if any word is found? — KOB, Aug 30 '16 at 18:18
The `MATCH("ZZZ",A:A)` from your last question was not the part that found if any had text. It found the last cell in which there was text. — Scott Craner, Aug 30 '16 at 18:26
Ok, but how can I find the last cell in which there is a time in the format of hh:mm, rather than text? — KOB, Aug 30 '16 at 18:51

score 0 · Accepted Answer · answered Aug 30 '16 at 18:19

Often times what is displayed to us in excel isn't the actual value. Time and Dates are one such time. Generally if you enter a time into excel 3:55 it will convert that automatically to excel's time format, which is a decimal number: 0.163194444444444, but it formats it automatically to "mm:ss" just like you entered it.

So... when you try to =MATCH() using a wildcard, you aren't going to find a hit since the value in the cell is actually that decimal number.

Instead you have to convert the value of the cells you are searching into a text format. You can do that with the =TEXT() formula. Assuming your data starts in A1 you can put in B1:

=Text(A1, "mm:ss")

Now the returned value from that formula still looks like the mm:ss format, but it's now text. The underlying value is actually 3:55. No funny business. You can now base your wild card search of "*:*" off of this column:

=Match("*:*", B1:B10, 0)

If you want to do this all in one formula you can use an Array formula (or CSE):

=Match("*:*", Text(A1:A10,"mm:ss"),0)

Using Ctrl+Shift+Enter (instead of Enter) when you enter the formula.

Regular expression for a time format

1 Answers1