Office: Formel oder Tool zum überprüfen der SV Nummer auf Richtigkeit

Marcys · 24. Oktober 2010

Hallo Zusammen,

ich muss ca. 180 Sozialversicherungsnummer in Excel übertragen. Da die SV-Nummer ja aus Alphanumerischen Zeichen besteht, kann es schon mal vorkommen, dass ein Zahlendreher passiert.

Weiß jemand von euch, ob es in Excle eine Möglichkeit gibt die SV-Nummer einer Plausibilitätsüberprüfung zu unterziehen?

Das was ich noch als Dateninput habe ich das Geburtsdatum Name und Vorname.

Grüße
Martin

:)

targa850 · 26. Oktober 2010

Hi Marcys,
schon solange dabei und kein Beispiel???

Wäre doch eine schöne Nuss zum knacken.

CU

hary · 26. Oktober 2010

Hallo
hab mal Interessenthalber Tante Google gefragt.
Versicherungsnummer – Wikipedia
Wenn es das richtige ist. Steht dort der Aufbau und wie die Pruefziffer berechnet wird.
oder auch hier:
Prüfziffern: Rentenversicherung [DE]
gruss hary

hary · 26. Oktober 2010

Hallo
mal ein vlt. unschoener Versuch.Hab keine SV zur Hand deshalb auf Seite
Prüfziffern: Rentenversicherung [DE]
bezogen.
In Zelle A1: 65180539W001

Code:

Sub n()
Dim intI As Long
Dim b As Long
Dim a As String
Dim c As Long
Dim d As Long
Dim nummer As String
'var = ersten 8 Ziffern & Buchstabe in Zahl & Ziffern rechts vom Buchstaben
nummer = Mid(Cells(1, 1), 1, 8) & Asc(Mid(Cells(1, 1), 9, 1)) - 64 & Mid(Cells(1, 1), 10, 2)
a = ""
For intI = 1 To Len(nummer)
Select Case intI
Case 1, 3, 7, 9, 11: a = a & Mid(nummer, intI, 1) * 2
Case 2, 6, 8, 10, 12: a = a & Mid(nummer, intI, 1) * 1
Case 4: a = a & Mid(nummer, intI, 1) * 5
Case 5: a = a & Mid(nummer, intI, 1) * 7
End Select
Next intI
For b = 1 To Len(a)
d = d + Mid(a, b, 1)
Next
If d Mod 10 = Right(Cells(1, 1), 1) Then
MsgBox "Prüfziffer stimmt"
Else
MsgBox "Prüfziffer stimmt nicht"
End If
End Sub
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

gruss hary
edit: Code gekuerzt

pefeu · 26. Oktober 2010

Hallo Martin,

ich würde erstmal die Prüfziffer verproben, was mit der beigefügten Function möglich ist:

Deine SV-Nummer steht in Zelle A1, dann in B1 = SV_PZ(A1)

Gruß Peter

Option Explicit Public Function SV_PZ(rZelle As Range) As String 'Integer Dim vMultiplikator As Variant Dim vSozVers_Nr() As Variant Dim sZeichen As String Dim iQuersumme As Integer Dim sBuchstabe As String Dim iIndx_I As Integer Dim iIndx_O As Integer vMultiplikator = Array(0, 2, 1, 2, 5, 7, 1, 2, 1, 2, 1, 2, 1) On Error Goto Fehler For iIndx_I = 1 To 11 sZeichen = Mid(rZelle, iIndx_I, 1) iIndx_O = iIndx_O + 1 If sZeichen <> "" And IsNumeric(sZeichen) Then ReDim Preserve vSozVers_Nr(iIndx_O) vSozVers_Nr(iIndx_O) = sZeichen Else sBuchstabe = Format(Asc(UCase(sZeichen)) - 64, "00") ReDim Preserve vSozVers_Nr(iIndx_O) vSozVers_Nr(iIndx_O) = Left(sBuchstabe, 1) iIndx_O = iIndx_O + 1 ReDim Preserve vSozVers_Nr(iIndx_O) vSozVers_Nr(iIndx_O) = Right(sBuchstabe, 1) End If Next iIndx_I If vSozVers_Nr(12) = "" Or Not IsNumeric(vSozVers_Nr(12)) Then MsgBox "Die eingegebene Sozialversicherungs-Nr """ & rZelle.Value & _ """ ist falsch.", 48, " Hinweis für " & Application.UserName Exit Function End If For iIndx_I = 1 To 12 sZeichen = vSozVers_Nr(iIndx_I) * vMultiplikator(iIndx_I) If CInt(sZeichen) > 9 Then iQuersumme = iQuersumme + CInt(Left(sZeichen, 1)) + CInt(Right(sZeichen, 1)) Else iQuersumme = iQuersumme + CInt(sZeichen) End If Next iIndx_I If CInt(Right(rZelle.Value, 1)) <> iQuersumme Mod 10 Then SV_PZ = "Falsch" Else SV_PZ = iQuersumme Mod 10 End If Fehler: If Err.Number = 0 Then Exit Function MsgBox "Es gibt Probleme:" & Chr(10) & Chr(10) & _ "Die Fehler-Nummer ist " & Space(4) & Err.Number & Chr(10) & _ "Die Fehler-Beschreibung ist " & Err.Description, _ 48, " Information für " & Application.UserName End Function
Klicke in dieses Feld, um es in vollständiger Größe anzuzeigen.

Code eingefügt mit

Marcys · 27. Oktober 2010

Hallo Pefeu,

Danke für den Tipp. Jedoch muss ich noch ein paar Fragen stellen, da ich in VBA und Excel nicht so fit bin.

Ich habe deinen Code eingefügt und in die Zelle A1 die richtige SV Nummer eingefügt. Es passiert jedoch nichts.

Was ich mir vorstelle ist, dass man in die Spalte A1:A180 die SV Nummern einträgt und in Spalte B jeweils ein ok oder nicht ok erscheint.

Ist das mit dem Code machbar?

hary · 27. Oktober 2010

Hallo
Das ist eine Function, also eine Formel.
Dieser code gehoert in ein Modul. In spalteB gibt's du eine Formel ein. Diese ist in der Formelsammlung unter benutzerdefeniert zu finden.
Hab mal im Code das OK eingeaendert. Hoffe Pfeu simmt zu ;-))
Formel brauchst Du nur runterziehen.
siehe Anhang.
gruss hary

Marcys · 27. Oktober 2010

pirma, es klappt.

Office: Formel oder Tool zum überprüfen der SV Nummer auf Richtigkeit

Formel oder Tool zum überprüfen der SV Nummer auf Richtigkeit

Formel oder Tool zum überprüfen der SV Nummer auf Richtigkeit

Formel oder Tool zum überprüfen der SV Nummer auf Richtigkeit

Formel oder Tool zum überprüfen der SV Nummer auf Richtigkeit

Formel oder Tool zum überprüfen der SV Nummer auf Richtigkeit - Similar Threads - Formel Tool überprüfen

Ergebnis aus INDEX und Vergleich Formel weiter Verwenden

dynamische Bezug auf Feiertagskalender in Formel "Nettoarbeitstage"

Formel funktioniert nur bis 19.30 Uhr

Formel sucht nur unvollstaendig

Produktionsende ermitteln - Formel gesucht

Formel in Spalten / Zellen aktivieren

Formel einsetzen über Auswahl

Users found this page by searching for:

sv-nummer excel prüfen