Count wildcard date in Text String

user_16158172 0
Viewed 77

I am looking for a formula to Count Specific date in Text String. My answers work with words, but if I use wildcards it doesn't work. W33 is the sample text below. I would expect a result of 2 but I am getting 0. I am using Excel 2010.

I have tried the following:

=(LEN(W33)-LEN(SUBSTITUTE((W33),("????-??-?? ??:??:??.??? "),"")))/LEN("????-??-?? ??:??:??.??? ")

Sample Text in one cell:

[(updated, will clear)swya (C - HF )(2022-04-15 07:37:08.826 )][(does not have details on the transaction, to advise on the same)swya (C - HF )(2022-04-12 06:57:12.440 )]
excel-2010 excel-2007 excel-formula excel
3 Answers

You tagged Excel 2010, Excel 2007. In that case try:

=SUM(--IFERROR(1=SEARCH("????-??-?? ??:??:??.???",MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),LEN(A1))),0))

And confirm through CtrlShiftEnter.

user_97581940

Unfortunately, wildcards only work in certain functions with Excel.

Wildcards work with all of the following functions:

  • SUMIF, SUMIFS
  • COUNTIF, COUNTIFS
  • AVERAGEIF, AVERAGEIFS
  • VLOOKUP
  • HLOOKUP ('<>' won't work with this)
  • MATCH ('<>' won't work with this)

(Source)

If your data is consistently in that format, then I would target 'swya' or '(C-HF)'. If not you can try Text to Columns and then use COUNTIF within that range, however, it isn't very automated.

user_188552880

One option, assuming access to the LET and SEQUENCE functions:

=LET(ξ,"????-??-?? ??:??:??.??? ",COUNT(SEARCH(ξ,MID(A1,SEQUENCE(LEN(A1)),LEN(ξ)))))

user_170077040
Related