Datavalidation of social security numbers

[ausswe]

Hi,
I've been trying for a while to figure out a way to use data validation and/or conditional formatting when entering Swedish social security numbers (personal id-number) to check that the value is valid but can't get my head around it.

I found an old thread on a forum that has been shut down so can't follow up there, but the formula they suggested works, at least partially.

The number is built up with a check number (the last digit in the number) and the formula validates that correctly as long as the ID-number is entered WITHOUT 19 or 20 (millenium) in the beginning which isn't good of course when entering mixed ages in the same list.
The ID-numbers are usually stated as either of these: 19651012-1314, 196510121314, 651012-1314, 6510121314 - as you can see either with 10 or 12 digits and with our without hyphen - and as you can see the original poster took this into account by substituting "-" in the formula.

I've been trying to figure out if I can use the original formula (that checks the ID numbers correctly as long as they are 10 numbers in length - without the millenium digits in front:

=RIGHT(A2;1)*1=MOD(10-MOD(SUM(MOD(MID(SUBSTITUTE(A2;"-";"");{1;2;3;4;5;6;7;8;9};1)*{2;1;2;1;2;1;2;1;2};10);TRUNC(MID(SUBSTITUTE(A2;"-";"");{1;7;9};1)*{2;2;2}/10;0));10);10)*1

So far I have tried IF statemens if the cell contains more than 11 characters, then ignore the first two (19/20) and use the original formula but haven't gotten that to work either.

So I'm checking if anyone would have any suggestions?

Thanks in advance!

 

[AlanY]

this will clear the data to 10 digits, will that hwlp?

Book1
	A	B	C	D
1	19651012-1314	196510121314	651012-1314	6510121314
2
3	6510121314	6510121314	6510121314	6510121314
Sheet1

Cell Formulas
Range	Formula
A3:D3	A3	=RIGHT(SUBSTITUTE(A1,"-",""),10)

 

[ausswe]

Thanks AlanY, the subsititute function is already built into the original formula and removing the millenium (19/20) would potentially cause duplicates so thats why I'm trying to figure out a way to modify the existing formula to account for 12 numbers instead.