Data validation for “EXACT” and “LEFT” with multiple criteria

Question

Hello,

I have a cell (J9) where I need the user to input either “P” or “I” at the start and then they can type whatever they want after that.

If I use the formula =EXACT(LEFT(J9,1),”P”) in the custom data validation formula field I can force it start with a P (and same if I change the P to an I). Where I am struggling is getting both of those conditions to work. I have tried variations of =OR but either the formula doesn’t work or it doesn’t allow any value, including the “P” and the “I”.

I know the answer is probably very simple but this one has defeated me for the best part of 2 hours now and the forums don’t seem to have anything that would help. Any help would be greatly appreciated.

Answer 1

Oops! Sorry about that. Went a bit too quick there without proper testing.

=OR(EXACT(LEFT(J9,1),"P"),EXACT(LEFT(J9,1),"I"))

Tested and it works.

Answer 2

Thank you for the response.

Unfortunately excel pops up an error message saying that this type of formula cannot be used in the data validation formula field.

Thank you for trying though :)

Answer 3

Try this:

=SUM(EXACT(LEFT(J9,1),{"P","I"})*1)