IF/OR Statement

Just noticed my last ss shows any comments from AP, i only want the comments returned to AO if they are exactly as they are in the brackets. Otherwise "No Narrative Provided"

e.g. AO14 would be "No Narrative Provided" as well as A015

Thanks
Seamus
Example.xls
Seamus2626Asked:
Who is Participating?
 
Rory ArchibaldCommented:
Then you need:

=IF(OR(TRIM(SUBSTITUTE(AP2,CHAR(160),""))="Fidessa Booking instructed, Awaiting Sophis Booking",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="Re - Allocated to different Source code",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="No Narrative Provided",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="Open Investigation",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="fidessa booking to be made",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="Corporate Actions Booking Adjustment required",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="Paris involved in resolution",TRIM(SUBSTITUTE(AP2,CHAR(160),""))="New break requires investigatation (1 - 3 day)"),TRIM(SUBSTITUTE(AP2,CHAR(160),"")), "No Narrative Provided")

because you have character 160 and trailing spaces in column AP (I guess you copied form the web)
0
 
Rory ArchibaldCommented:
Can you clarify that? That appears to be the opposite of thhe intention of your formula, which is trying to return "No narrative provided" if the cell does match one of the items.
0
 
Seamus2626Author Commented:
I know! Basically what im looking to achieve is:

If the text in AP matches any of the bracketed options in the IF Staement, return AP, If it doesnt exactly match the bracketed options or is blank, return "No Narrative Provided"

Thanks
Seamus
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Seamus2626Author Commented:
Thanks Rory!

Out of interest, what is Character 160?

Thanks
Seamus
0
 
Rory ArchibaldCommented:
It's a non-breaking space, I believe.
0
 
Seamus2626Author Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.