'Count wildcard date in Text String
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 )]
Solution 1:[1]
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.
Solution 2:[2]
One option, assuming access to the LET and SEQUENCE functions:
=LET(?,"????-??-?? ??:??:??.??? ",COUNT(SEARCH(?,MID(A1,SEQUENCE(LEN(A1)),LEN(?)))))
Solution 3:[3]
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | JvdV |
| Solution 2 | Jos Woolley |
| Solution 3 | halfer |