Skip to content

Instantly share code, notes, and snippets.

@JeancarloFontalvo

JeancarloFontalvo/validator.gist.gs

Created January 28, 2019 22:59
Show Gist options
  • Save JeancarloFontalvo/f656eb3ad7668daf89ffbb96f95b7df9 to your computer and use it in GitHub Desktop.
Save JeancarloFontalvo/f656eb3ad7668daf89ffbb96f95b7df9 to your computer and use it in GitHub Desktop.
Download ZIP
Formula for validating a cell with the J2 Format
Raw
validator.gist.gs
-- J2 = "Alfanumerico|6|SI|SI|Personalizado"
--FORMULA
=Y(
SI(
SI.ERROR(HALLAR("Alfa"; J2); -1) <> -1;
VERDADERO;
SI(
SI.ERROR(HALLAR("Numerico"; J2); -1) <> -1;
REGEXMATCH(TO_TEXT(K2); "^(\d+)$");
SI(
SI.ERROR(HALLAR("Fecha"; J2); -1) <> -1;
REGEXMATCH(TO_TEXT(K2);"^(19|20)\d{2}[\/](0[1-9]|1[0-2])[\/](0[1-9]|[12]\d|3[01])$");
SI(
SI.ERROR(HALLAR("Hora"; J2); -1) <> -1;
REGEXMATCH(TO_TEXT(K2); "^(((0|1)[0-9])|2[0-3]):[0-5][0-9]$");
VERDADERO
)
)
)
);
LARGO(TO_TEXT(K2)) <= INDICE(SPLIT(J2; "|"); 0; 2);
SI(
O(SI.ERROR(HALLAR("Obligatorio"; J2); -1) <> -1; SI.ERROR(HALLAR("Personalizado"; J2); -1) <> -1);
NO(ESBLANCO(K2));
VERDADERO
)
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment