CAS nummer controleren

[AlexCEL]

Beste forumleden,

ik zoek een elegante formule om een CAS-nummer te controleren. Een CAS-nummer is een unieke numerieke identifier voor chemische stoffen (voor de geïnteresseerden: https://nl.wikipedia.org/wiki/CAS-nummer). CAS-nummers bestaan uit 9 of 10 cijfers, verdeeld over 3 groepjes gescheiden door een streepje. Een nummer eindigt altijd met één cijfer: een checksum.

Zonder de streepjes ziet het nummer eruit als: (N₉)N₈N₇N₆N₅N₄N₃N₂N₁R.
N₉ komt erbij als het 10-cijferig nummer betreft.

De R is de rest, die wordt bepaald door de volgende formule gedeeld door 10: N₁ + 2×N₂ + 3×N₃ + 4×N₄ + 5×N₅ + 6×N₆ + 7×N₇ + 8×N₈ (+ 9×N₉)

Voorbeelden:
64-17-5 (ethanol) → (1x7)+(2x1)+(3x4)+(4x6)=45 ... 45/10 geeft als rest 5. Dit nummer klopt dus.
7782-44-7 (zuurstof) → (1x4)+(2x4)+(3x2)+(4x8)+(5x7)+(6x7)=127 ... 127/10 geeft als rest 7. Ook ok.
5555-32-2 ("fantasiestof") → (1x2)+(2x3)+(3x5)+(4x5)+(5x5)+(6x5)=98 … 98/10 geeft als rest 8. Nummer is dus niet ok.
2041311-92-4 (nieuwste CAS-nummer) → (1x2)+(2x9)+(3x1)+(4x1)+(5x3)+(6x1)+(7x4)+(8x0)+(9x2)=94 ... 94/10 geeft als rest 4. Nummer is ok.
etc.

Ik zoek nu dus:
1) een formule die een CAS-nummer van rechts naar links kan lezen, en elk afzonderlijk cijfer hieruit met een oplopend cijfer vermenigvuldigd.
2) dat flexibel om kan gaan met variabele lengte van CAS-nummers;
3) die ik in Voorwaardelijke Opmaak kan gebruiken om een cel rood te kleuren als iemand een verkeerd CAS-nummer ingeeft.

Punt 1 en 2 lukt mij vermoedelijk wel, maar vooral de laatste voorwaarde is complicerend....

Zie ook bijgevoegd voorbeeldje.

 

[WHER]

Misschien kun je hier mee verder...

 

[AlexCEL]

Een elegante formule, zeker.

Ik was zelf hiermee aan de slag:

=--RECHTS(B3;1)=REST(SOM(ALS.FOUT(--DEEL(SUBSTITUEREN($B3;"-";"");LENGTE(SUBSTITUEREN($B3;"-";""))-(RIJ($1:$9));1)*RIJ($1:$9);0));10)

Echter zowel jouw als mijn formule zijn matrixformules, die kun je niet in voorwaardelijke opmaak kwijt.

Misschien dat er nog iets mogelijk is met een SOMPRODUCT formule of zo (ga ik nog even naar speuren), of misschien ontkom ik niet aan het gebruik van een hulpkolom.

 

[Timshel]

Ik kom op onderstaande formule. Die is eventueel ook bruikbaar in VO.

=WAARDE(RECHTS(B3;1))=REST(SOMPRODUCT(DEEL(TEKST(SUBSTITUEREN(B3;"-";"");"0000000000");RIJ($1:$10);1)*(10-RIJ($1:$10)));10)

 

[Cobbe]

Of deze matrixformule: met het nummer in A2

=ALS(A2="";"";REST(SOM(DEEL(SUBSTITUEREN(A2;"-";"");LENGTE(A2)-RIJ(INDIRECT("1:" & LENGTE(A2)-3))-2;1)*(RIJ(INDIRECT("1:" & LENGTE(A2)-3))));10)=WAARDE(RECHTS(A2;1)))

 

[Haije]

voortbordurend op de gegeven oplossing van WHER

Evt één kolom verbergen....

 

[snb]

Ik dacht nu altijd dat de regels in CF per definitie matrixformules waren...

=MOD(SUM(IFERROR(MID(SUBSTITUTE(LEFT(B3;LEN(B3)-2);"-";"");1+LEN(SUBSTITUTE(LEFT(B3;LEN(B3)-2);"-";""))-ROW($1:$10);1)*ROW($1:$10);0));10)<>--RIGHT(B3;1)

Maar ook VBA biedt mogelijkheden:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$6" Then
        sn = Filter(Split(StrConv(Target, 64), Chr(0)), "-", 0)

        For j = 0 To UBound(sn) - 2
          y = y + (UBound(sn) - j - 1) * sn(j)
        Next

        Target.Interior.Color = 16777215 + Abs(y Mod 10 <> Val(sn(j))) * 255
    End If
End Sub

 

[E v R]

Bekijk bijlage Copy of CAS-nummer check-1.xlsx

Echter zowel jouw als mijn formule zijn matrixformules, die kun je niet in voorwaardelijke opmaak kwijt.

En als je de formule in een gedefinieerde naam zet? en naar deze naam in jouw voorwaardelijke opmaak verwijst?

 

[AlexCEL]

Heren (denk ik ), allen hartelijk dank voor de input!

Het is gelukt, zowel met een rechtstreekse formule als met de gedefinieerde naam.

Ik heb weer wat nieuwe inzichten, waaronder dat je een matrixformule in VO kennelijk niet af hoeft te sluiten met control+shift+enter (oftewel: wat snb zegt)...

Ga ik nu aan de slag met het EG-nummer (https://nl.wikipedia.org/wiki/EG-nummer), werkt ongeveer gelijk maar toch weer net even anders...

 

[WHER]

Even offline geweest maar ik wou toch nog even bijtreden wat snb in post 7 opmerkt: formules in V.O. (of in genaamde bereiken) worden door Excel automatisch als matrixformules geëvalueerd.

 

[E v R]

Met de restrictie dat in vo tov gedefineerde namen geen matrix constanten kunnen worden gebruikt

 

[WHER]

Inderdaad, indien je (zoals in mijn formule uit post 2) matrix constanten gebruikt, moet het via een gedefiniëerde naam, dat was ik uit het oog verloren.