|
本帖最后由 fjmxwrs 于 2011-9-9 00:18 编辑
有一个数组公式可以完成,论坛上就有,我记不太清了。
这个自定义函数的,你试下就知道了
Function IdCardLastChar(num)
Dim cId As String
Dim nsum As Integer
Dim check_number As String
If Len(num) = 15 Then
cId = Left(num, 6) & "19" & Right(num, 9)
ElseIf Len(num) = 17 Or Len(num) = 18 Then
cId = Left(num, 17)
End If
'计算方法
nsum = Mid(cId, 1, 1) * 7
nsum = nsum + Mid(cId, 2, 1) * 9
nsum = nsum + Mid(cId, 3, 1) * 10
nsum = nsum + Mid(cId, 4, 1) * 5
nsum = nsum + Mid(cId, 5, 1) * 8
nsum = nsum + Mid(cId, 6, 1) * 4
nsum = nsum + Mid(cId, 7, 1) * 2
nsum = nsum + Mid(cId, 8, 1) * 1
nsum = nsum + Mid(cId, 9, 1) * 6
nsum = nsum + Mid(cId, 10, 1) * 3
nsum = nsum + Mid(cId, 11, 1) * 7
nsum = nsum + Mid(cId, 12, 1) * 9
nsum = nsum + Mid(cId, 13, 1) * 10
nsum = nsum + Mid(cId, 14, 1) * 5
nsum = nsum + Mid(cId, 15, 1) * 8
nsum = nsum + Mid(cId, 16, 1) * 4
nsum = nsum + Mid(cId, 17, 1) * 2
'*计算校验位
check_number = 12 - nsum Mod 11
If check_number = 10 Then
check_number = "X"
End If
If check_number = 12 Then
check_number = "1"
End If
If check_number = 11 Then
check_number = "0"
End If
IdCardLastChar = check_number
End Function
摘自无爱无恨老师:http://www.excelpx.com/forum.php?mod=redirect&goto=findpost&ptid=121285&pid=1721284&fromuid=281125
|
|