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

 

[etaf]

I'm assuming not a NOTE thats in excel
BUT a cell you use as notes

=AND(COUNTIF(F2,"*w.*"),COUNTIF(F2,"*a.*"),COUNTIF(F2,"*n.*"),COUNTIF(F2,"*t.*"))

is present in all acceptable formats

what are all the possible formats

as TEXT , words may have W A N T in
ie
want to be here for xmas
I want to

when it matters
has
W N A T in

so to test just for the characters WANT maybe an issue

or is it always followed by a return as shown

some examples may help here

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[JOJO2730]

Hi,
Below is the sample data from the cell

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

As i mentioned, w.a.n.t are stages and i want tocheck if all the stages are present in that cell in multiple formats such as W:/w./W-/w-.
Similarly for remaining stages.

Later on i can also add additional stage as S/X in the cell.

 

[etaf]

maybe something like
=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.*"})))

Not case sensitive so will pick up a. and A.

but you will need all the possible combinations
otherwise you have for example
W (We , awaiting )
A (chasing, via, asked)
N (client ,
T (client, they, rest, awaiting, customers)

in all the words

Book1
	A
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
2
3	FALSE
4
5	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
6
7	TRUE
Sheet1

Cell Formulas
Range	Formula
A3,A7	A3	=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.*"})))

 

[Peter_SSs]

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

As i mentioned, w.a.n.t are stages and i want tocheck if all the stages are present in that cell in multiple formats such as W:/w./W-/w-.

It seems like the data is quite unstructured and the want letters can be upper/lower case and preceded/followed by nearly anything. As an example, it appears the "w" can be followed by "."
So how would we logically determine that this "w" is one we are interested in
11-Feb-2022 w. chasing client via email

and this is one that we are not interested in?
11-Feb-2022 email to abc@shaw.com

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)?

 

[JOJO2730]

It seems like the data is quite unstructured and the want letters can be upper/lower case and preceded/followed by nearly anything. As an example, it appears the "w" can be followed by "."
So how would we logically determine that this "w" is one we are interested in
11-Feb-2022 w. chasing client via email

and this is one that we are not interested in?
11-Feb-2022 email to abc@shaw.com

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)?

Notes are particularly comments written manually hence, the person writing it can use W:/w./W-/w- and follows the same for the other 3 stages that is a,n,t!
Date isn't structured as it is a comment and hence i don't feel any date format should be decided for the same purpose!

 

[Peter_SSs]

As far as I can tell, that did not answer either of my two questions.

So how would we logically determine that this "w" is one we are interested in
11-Feb-2022 w. chasing client via email

and this is one that we are not interested in?
11-Feb-2022 email to abc@shaw.com

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)?

 

[JOJO2730]

As far as I can tell, that did not answer either of my two questions.

There's space after w. a. n. t.

 

[Peter_SSs]

There's space after w. a. n. t.

OK, that helps with the first question.

 

[etaf]

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