w.a.n.t formula

[JOJO2730]

I have a column as NOTES that contains data such as
01-feb-2022 text text text
w. text date text
a.text date text
n. text date text
t. text date text
basically, w.a.n.t. are the stages the note is written for.

I want a formula to check if w.a.n.t. is present in the cell.
There are some other formats to write w.a.n.t. such as W-A.N't. I want to run a formula through the text present in each cell of the column and check if w.a.n.t is present in all acceptable formats and return pass if present and fail otherwise

 

[JOJO2730]

if ther is always a space - then try
=AND(SUM(COUNTIF(A1,{"*W: *","*W- *","*W. *"})),SUM(COUNTIF(A1,{"*A: *","*A- *","*A. *"})),SUM(COUNTIF(A1,{"*N: *","*N- *","*N. *"})),SUM(COUNTIF(A1,{"*T: *","*T- *","*T. *"})))
update the
{"*W: *","*W- *","*W. *"}
with any other possible types of entry for each letter

you can then add other letters S and X by adding to the AND

I'm sure there is a better way to do this - other members may have

Absolutely a better way... I never thought the formula could be this simple !!
Thanks!

 

[Peter_SSs]

Unfortunately I didn't ever get an answer to ..

If w.a.n.t. does occur in the cell would it always be in that particular order (as they are in the two examples that you have given)?

If the answer is "Yes, they would always be in that order", then another approach could be to employ a user-defined function like this.
One thing about it is that if those characters that can follow w a n t change, it just needs the 'Const' line edited in the same pattern as below to add/remove items.

Function WANT(s As String) As Boolean
  Const CanFollow As String = " : - ." '<-Edit as required. Put a space before ech one
  
  With CreateObject("VBScript.RegExp")
    .Ignorecase = True
    .Pattern = Replace("W# .*?A# .*?N# .*?T# .*?", "#", "[" & Replace(CanFollow, " ", "\") & "]")
    WANT = .Test(s)
  End With
End Function

The UDF would be used as in column B below.

If you do want to stick with a worksheet formula then you could try this (column C) modification of @etaf's idea. It is a bit more efficient and again easier to maintain any changes the the 'following' characters by only needing to edit them in one place near the beginning of the formula.

JOJO2730.xlsm
	A	B	C
1	11-Feb-2022 - chasing client via email A: They paid 26/01 9.12300 we asked for the restN: 'ljhdudnh@hdgdh.com''lock.med@gmial.com'T: awaiting customers reply	FALSE	FALSE
2	11-Feb-2022 W- chasing client via email A: They paid 26/01 9.12300 we asked for the restN: 'ljhdudnh@hdgdh.com''lock.med@gmial.com'T: awaiting customers reply	TRUE	TRUE
3	11-Feb-2022 W- chasing client via email A: They paid 26/01 9.12300 we asked for the restN: 'ljhdudnh@hdgdh.com''lock.med@gmial.com	FALSE	FALSE
4	11-Feb-2022 W- chasing client via email A. They paid 26/01 9.12300 we asked for the restN- 'ljhdudnh@hdgdh.com''lockt.med@gmial.com'T: awaiting customers reply	TRUE	TRUE
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=WANT(A1)
C1:C4	C1	=LET(x,{":","-","."}&"*",AND(OR(COUNTIF(A1,"*W"&x)),OR(COUNTIF(A1,"*A"&x)),OR(COUNTIF(A1,"*N"&x)),OR(COUNTIF(A1,"*T"&x))))

 

[JOJO2730]

Absolutely a better way... I never thought the formula could be this simple !!
Thanks!

Hello i have an addition to make with WANT there's PD. And QD.
there's no alternate format for PD. and QD.
What edits to make in the same formula??

 

[Peter_SSs]

What edits to make in the same formula??

Not the same formula, but does this do what you want?

=LET(x,{":","-","."}&"*",AND(OR(COUNTIF(A1,"*W"&x)),OR(COUNTIF(A1,"*A"&x)),OR(COUNTIF(A1,"*N"&x)),OR(COUNTIF(A1,"*T"&x)),COUNTIF(A1,"*PD.*"),COUNTIF(A1,"*QD.*")))