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 :( Any help would be greatly appreciated! 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 )]
CodePudding user response：
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.
CodePudding user response：
One option, assuming access to the LET and SEQUENCE functions:
=LET(ξ,"????-??-?? ??:??:??.??? ",COUNT(SEARCH(ξ,MID(A1,SEQUENCE(LEN(A1)),LEN(ξ)))))