Data Validation - Custom formula: got error message saying "There's a problem with this formula"

Question

Hi,

I am trying to validate the email format in a cell (D14) using "Data Validation". But after selecting "Custom" and typed the formula in the formula's box, I got the message error from Excel saying "There's a problem with this formula".

The formula is below:

=AND(FIND(“@”,D14),Find(".",D14),ISERROR(FIND(" ", D14)))

I am using Windows 10 system and Excel 2016 (Office 365 Home).

Any help will be much appreciated.

Paulo

Answer 1

Yes. I understand that some regions use a semi colon in lieu of comma (and it is called a delimiter not a parameter). I am in Australia and we use commas.

There is some information on changing the delimiter at the following link but I suggest that you don't change your regional default because you do not know what else it might upset.

https://lockone.wordpress.com/2015/08/06/excel-now-using-semicolons-instead-of-commas-in-separating-formulas-fix-it-now/

Answer 2

The formula that I gave you definitely works because I tested it before and I have tested again.

You have a bad character or syntax in there somewhere.

Are you aware that to edit a formula in the field that you need to click in the field and press F2 to invoke "Edit Mode" otherwise as you move the cursor it thinks you are on the worksheet and inserts a cell (or range) address.

Try the following to fix:

• Highlight and Copy the following formula.

=AND(FIND("@",D14),FIND(".",D14),ISERROR(FIND(" ", D14)))

• Click on the required cell (D14)
• Open Date Validation.
• Position the cursor anywhere in the Formula field
• Press F2 to invoke edit mode
• Use right arrow to move cursor out past far right of formula
• Press and hold the Back Space until the formula is totally deleted.
• Use Ctrl and V to paste the new formula.

Hi,

Thank you for the heads up! I knew about the edit.

What I did to solve the problem was to change the "," for the ";" and it worked. See the formula below:

=AND(FIND("@";D14);FIND(".";D14);ISERROR(FIND(" ";D14)))

To be honest, I don't know why and where to check in Excel to find this type of parameter ";" instead of ",".

If you could clarify this, I would be glad!

Kind regards,

Paulo

Answer 3

The formula that I gave you definitely works because I tested it before and I have tested again.

You have a bad character or syntax in there somewhere.

Are you aware that to edit a formula in the field that you need to click in the field and press F2 to invoke "Edit Mode" otherwise as you move the cursor it thinks you are on the worksheet and inserts a cell (or range) address.

Try the following to fix:

• Highlight and Copy the following formula.

=AND(FIND("@",D14),FIND(".",D14),ISERROR(FIND(" ", D14)))

• Click on the required cell (D14)
• Open Date Validation.
• Position the cursor anywhere in the Formula field
• Press F2 to invoke edit mode
• Use right arrow to move cursor out past far right of formula
• Press and hold the Back Space until the formula is totally deleted.
• Use Ctrl and V to paste the new formula.

Answer 4

You have sloping double quotes around the @ symbol. These have a different ascii value and not recognized by Excel.

Copy the following and try.

=AND(FIND("@",D14),FIND(".",D14),ISERROR(FIND(" ", D14)))

Hi, 

Thank you, but I tried your solution and the problem persists. See the screenshot.

Answer 5

You have sloping double quotes around the @ symbol. These have a different ascii value and not recognized by Excel.

Copy the following and try.

=AND(FIND("@",D14),FIND(".",D14),ISERROR(FIND(" ", D14)))