如何确保在excel里面输入的身份证号符合要求

发布网友 发布时间：2022-05-12 15:08

我来回答

共3个回答

热心网友 时间：2024-03-14 00:46

在身份证那个单元格的右边单元格里输入“=if（100000000000000000<A1<999999999999999999,"正确"，"错误"）”注意：中文引号不要，括号里面要英文引号。
然后往下拉。可以判断是不是18位的数字，但是字母的不行

热心网友 时间：2024-03-14 00:46

数据-有效性-序列
=OR(LEN(A1)=16,LEN(A1)=17)

//*输入长度为16或17位.

热心网友 时间：2024-03-14 00:46

自定义函数check_no():
功能:判断18位身份证的有效性,升级15位到18位.
返回值:有效返回TRUE,无效返回FALSE,原15位则返回新18位身份证号,如果出生年月不是有效的日期格式则返回空.
Function check_no(no As String) As String
Dim ai(18)
Dim Wi(17)
Dim rst(11)
s = 0
Wi(1) = 7
Wi(2) = 9
Wi(3) = 10
Wi(4) = 5
Wi(5) = 8
Wi(6) = 4
Wi(7) = 2
Wi(8) = 1
Wi(9) = 6
Wi(10) = 3
Wi(11) = 7
Wi(12) = 9
Wi(13) = 10
Wi(14) = 5
Wi(15) = 8
Wi(16) = 4
Wi(17) = 2
rst(1) = "1"
rst(2) = "0"
rst(3) = "X"
rst(4) = "9"
rst(5) = "8"
rst(6) = "7"
rst(7) = "6"
rst(8) = "5"
rst(9) = "4"
rst(10) = "3"
rst(11) = "2"
If Trim(Len(no)) = 18 Then
For i = 1 To 18
ai(i) = Mid(no, i, 1)
Next
For i = 1 To 17
s = s + Int(Val(ai(i))) * Wi(i)
Next
y = s Mod 11
If Trim(rst(y + 1)) <> UCase(Trim(ai(18))) Then
check_no = "False"
Else
check_no = "True"
End If
ElseIf Len(no) = 15 Then
cd = "19" + Mid(no, 7, 2) + "-" + Mid(no, 9, 2) + "-" + Mid(no, 11, 2)
On Error GoTo send
dd = CDate(cd)
st = ""
For i = 1 To 6
st = st + Mid(no, i, 1)
Next
st = st + "19"
For i = 7 To 15
st = st + Mid(no, i, 1)
Next
For i = 1 To 17
ai(i) = Mid(st, i, 1)
Next
For i = 1 To 17
s = s + Int(Val(ai(i))) * Wi(i)
Next
y = s Mod 11
st = st + rst(y + 1)
check_no = st
End If
send:
End Function