Forum Discussion
HOW TO INCREASE THE MAXIMUM SIZE FORMULA IN VALIDATION OPTION
Please check this blog https://rednectar.net/2019/07/15/validating-ip-address-entries-in-excel/, here is formula suggestion how to validate IP address and compromise 255 characters limit.
To translate formulas in English locale to Spanish one you may use https://en.excel-translator.de/translator/ (if formulas are within Excel file they are translated automatically).
To add file to the post click this icon at the bottom left of the Reply window
anyway, below the formula that I want to add to a validation cell in B4
=SI(VALOR.NUMERO(EXTRAE(B4;1;HALLAR(".";B4;1)-1))<256;(SI(VALOR.NUMERO(EXTRAE(B4;(HALLAR(".";B4;1)+1);((HALLAR(".";B4;(HALLAR(".";B4;1)+1)))-(HALLAR(".";B4;1))-1)))<256;SI(VALOR.NUMERO(EXTRAE(B4;(HALLAR(".";B4;(HALLAR(".";B4;1)+1)))+1;((HALLAR(".";B4;H4+1))-(HALLAR(".";B4;(HALLAR(".";B4;1)+1)))-1)))<256;1;0);0));0)
a printscreen of the Sheet:
Please check this blog https://rednectar.net/2019/07/15/validating-ip-address-entries-in-excel/, here is formula suggestion how to validate IP address and compromise 255 characters limit.
To translate formulas in English locale to Spanish one you may use https://en.excel-translator.de/translator/ (if formulas are within Excel file they are translated automatically).
To add file to the post click this icon at the bottom left of the Reply window
SergeiBaklan i really appreciatte it, I will try, I´m going to share result as soon as is over
I followed the procedure described in the link wich you attach with success result, indeed there is the same restriction with length of the validation formula however that formula is shorter than mine Thx a lot
To my knowledge it's not possible to change the limit on number of characters for validation formula.
However, I checked comments to this blog. If in latest suggested formula take not array constant but helper range as here
when it looks like
=SUMPRODUCT(N(LOG(1+MID(SUBSTITUTE(B6,".",REPT(" ",10)),$A$1:$A$4,10),2)<=8))=4works. It's only 79 characters.
Please check in attached file, I didn't test it carefully.