• Instructions to Ask a Question

    Click on the "Ask a Question" button and select the application for which you would like to ask questions.

    We have 5 different products namely - Pabbly Connect, Pabbly Subscription Billing, Pabbly Email Marketing, Pabbly Form Builder, Pabbly Email Verification.

    The turnaround time is 24 hrs (Business Hours - 10.00 AM to 6.00 PM IST, Except Saturday and Sunday). So your kind patience will be highly appreciated!

    🚀🚀Exclusive Discount Offer

    Just in case you're looking for any ongoing offers on Pabbly, you can check the one-time offers listed below. You just need to pay once and use the application forever -
     

    🔥 Pabbly Connect One Time Plan for $249 (🏆Lifetime Access) -  View offer 

    🔥 Pabbly Subscription Billing One Time Plan for $249 (🏆Lifetime Access) - View offer

Spreadsheet formula not calculating properly - formula returns as the result

Status
Not open for further replies.

C Spencer

Member
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:

Capture.PNG
 
P

Pabbly13

Guest
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-

1660822980393.png


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.
 
Status
Not open for further replies.
Top