Conditional formatting to help input mask/ensure social security has 9 digits

rwilkes

I've labored over trying to figure how to get around how SS gets input and for it to highlit if it doesn't contain 9 digits and hyphens. …as I'm typing this, I guess I could do it the same way I did my zip code and just leave out hyphens and have them inected into another column…. I'll work on that until I see someone has figured out a better way. So I'm trying to get the cell to highlit when the cell doesn't contain ###-##-####, which works as you can see in the below snapshot, but I want to have the # be any random number…

Paul Newcome

Try something like this to flag rows that are not valid (then build conditional formatting off of this):

=IF(AND(ISNUMBER(IFERROR(VALUE(SUBSTITUTE([Social Security Number]@row, "-", "")), "a"), MID([Social Security Number]@row, 4, 1) = "-", MID([Social Security Number]@row, 7, 1) = "-", LEN([Social Security Number]@row) = 11), "", 1)

Basically we strip out the hyphens and make sure the result is a number, then we make sure the 4th and 7th characters are both hyphens, and finally we make sure the full length is 11 characters long. If all of these are true, do nothing, otherwise flag the row.

Paul Newcome

What formulas are you using for your method? Have you tried my suggestion?

Paul Newcome

Try something like this to flag rows that are not valid (then build conditional formatting off of this):

=IF(AND(ISNUMBER(IFERROR(VALUE(SUBSTITUTE([Social Security Number]@row, "-", "")), "a"), MID([Social Security Number]@row, 4, 1) = "-", MID([Social Security Number]@row, 7, 1) = "-", LEN([Social Security Number]@row) = 11), "", 1)

Basically we strip out the hyphens and make sure the result is a number, then we make sure the 4th and 7th characters are both hyphens, and finally we make sure the full length is 11 characters long. If all of these are true, do nothing, otherwise flag the row.

rwilkes

Thank you; I think that's most likely a nice script instead of my cumbersome, but effective, process. Only issue I've got to circle back on is somehow it's seeing 2- as a number (under SS MID). It checks -2 as not a number though…

Paul Newcome

What formulas are you using for your method? Have you tried my suggestion?

rwilkes

Very similar… I'm finishing up this parcing concept, which checks the field and then conditional formating highlights where it has been checked. I'll inject yours shortly; hopefully your will catch the 2-.

=IF(NOT(ISNUMBER([SS MID]@row))), 1,0) (OR([SS MID]@row < "00", [SS MID]@row > "99", 0,1)

=IF(SS MID@row<00 or >99, 1,0)

=IF(ISNUMBER SSMID@ROW), 0,1)

=IF(OR(SS Suffix@row < "1000", SS Suffix@row > "9999"), 1, 0)

rwilkes

LOL, I'm too stubborn. I should have just did yours when I saw it - works perfectly!! Thanks!!!!

Paul Newcome

Happy to help. 👍️