# w.a.n.t formula



## JOJO2730 (Dec 20, 2022)

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 (Dec 20, 2022)

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 (Dec 20, 2022)

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 (Dec 20, 2022)

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 

Book1A111-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 reply23FALSE4511-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 reply67TRUESheet1Cell FormulasRangeFormulaA3,A7A3=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 (Dec 20, 2022)

JOJO2730 said:


> 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@sha*w*.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 (Dec 20, 2022)

Peter_SSs said:


> 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
> 
> ...


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 (Dec 21, 2022)

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



Peter_SSs said:


> 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@sha*w*.com





Peter_SSs said:


> 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 (Dec 21, 2022)

Peter_SSs said:


> 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 (Dec 21, 2022)

JOJO2730 said:


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


OK, that helps with the first question.


----------



## etaf (Dec 21, 2022)

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


----------



## JOJO2730 (Dec 20, 2022)

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 (Dec 21, 2022)

etaf said:


> 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. *"}
> ...


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


----------



## Peter_SSs (Dec 22, 2022)

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


Peter_SSs said:


> 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.xlsmABC111-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 replyFALSEFALSE211-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 replyTRUETRUE311-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.comFALSEFALSE411-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 replyTRUETRUESheet1Cell FormulasRangeFormulaB1:B4B1=WANT(A1)C1:C4C1=LET(x,{":","-","."}&"*",AND(OR(COUNTIF(A1,"*W"&x)),OR(COUNTIF(A1,"*A"&x)),OR(COUNTIF(A1,"*N"&x)),OR(COUNTIF(A1,"*T"&x))))


----------



## JOJO2730 (Dec 28, 2022)

JOJO2730 said:


> 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 (Dec 28, 2022)

JOJO2730 said:


> 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.*")))
```


----------

