Spreadsheet formula not calculating properly - formula returns as the result

Status: Closed · Asked by Cheryl Spencer on · 0 views

C Spencer — Question ·

I want to identify whether a person's name has any special characters in it. Here's my formula:

IF((LEN('2. Result : Em')-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('2. Result : Em','1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'&',''),'@',''),'.',''),'#',''),'$',''),'*',''),'(',''),')',''),''',''),'"',''),'%',''),'-',''),'+',''),'=',''),'/',''),';',''),':',''),',',''),'?',''))>0),'TRUE','FALSE')

The step returns the formula as the result rather than TRUE or FALSE.

I've tested the formula in Google Sheets and Excel and it returns FALSE. I'm clearly missing something, but can't figure out what.

Could the formula be too long?

Here's a screenshot:

chetali parve — Reply ·

Hey @C Spencer!

Kindly allow us some time to check your concern.

Your patience will be highly appreciated.

C Spencer — Reply ·

Thank you!

Pabbly13 — Reply ·

Hey @C Spencer !

The formula which you were trying to add inside the formula sheet was creating an issue because of a single Quote, as this is not supported as this is breaking the structure.

The team has removed the single quotes and it started to work without error-

You can try this code-

IF((LEN('Em')-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('Em','1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9',''),'&',''),'@',''),'.',''),'#',''),'$',''),'*',''),'(',''),')',''),'"',''),'%',''),'-',''),'+',''),'=',''),'/',''),';',''),':',''),',',''),'?',''))>0),'TRUE','FALSE')

Kindly check on this and let us know if this works for you.

C Spencer — Reply ·

It works!!! Yay! Thank you SO much!!

Back to all forum threads · Log in to reply