=if(count(search) formula

[rakesh seebaruth]

Dear Guys

Any help on the below formula


=IF(COUNT(SEARCH({"MR","AND","MRS"},C2)),"Dear Sir/Madam"," ") it works well for example for
MR JACK SMITH AND MRS JACK SMITH. But for MR JACK SMITH only it does not work. If in cell c2 name is MR JACK SMITH then result shall be "Dear Mr"

thanks

rakesh

 

[Marcílio_Lobão]

rakesh seebaruth, Good morning.

Try to use:

="Dear "& IF(ISERROR(SEARCH("AND";C2));IF(ISERROR(SEARCH("MRS";C2));IF(ISERROR(SEARCH("MR ";C2));"";"Sir");"Madam");"Sir/Madam")

Is that what you want?

Probably will appear a more elegant formule. hi hi hi

I hope it helps.

 

[rakesh seebaruth]

no it's not working .have a look at the following link https://www.dropbox.com/s/harergtb4kr6mmo/TESTING2.xls?dl=0 . . i have inserted the results in column b and c .

can i have a vba code for that or a formula

thanks

rakesh

 

[Marcílio_Lobão]

rakesh seebaruth, Good afternoon.

Maybe your argument separator in the formula is a COMMA ( , ) instead of a semicolon ( ; )

As in your new example you included a new title, I adapted the formula to meet the new requirement.

="Dear "& IF(ISERROR(SEARCH("AND",A1)),IF(ISERROR(SEARCH("MRS",A1)),IF(ISERROR(SEARCH("MR ",A1)),IF(ISERROR(SEARCH("MISS ",A1)),"", "Miss"),"Sir"),"Madam"),"Sir/Madam")

Here's your model with the formula I suggested.

https://www.sendspace.com/file/2vqcn9

Hope this helps.

 

[rakesh seebaruth]

thanks you are a genius can you put this vba for me please.

regards

rakesh

 

[Aladin Akyurek]

Just enter and copy down:

="Dear "&IF(SUM(COUNTIFS(A2,"*"&{"mr ","mrs ","miss"}&"*"))=1,LOOKUP(9.99999999999999E+307,SEARCH({"mr ","mrs ","miss"},A2),{"Sir","Madam","Miss"}),"Sir/Madam")

where A2 houses a string like NUCK MR MOH IRS.

 

[markmzz]

Another way:

="Dear "&LOOKUP(MMULT(--ISNUMBER(SEARCH({" MISS "," MR "," MRS "}," "&C13&" ")),{1;2;3}),{1;2;3;4},{"Miss";"Sir";"Madam";"Sir/Madam"})

Markmzz