使用VBA判断单元格各字符是否为中文、英文、数字、符号的复杂代码
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Function IsLike(strText As String, pattern As String) As Boolean
IsLike = strText Like pattern
End Function
Function IsChinese(strText As String) As Boolean
Dim i%, h$
h = Hex(Asc(strText))
If Asc(Left(h, 1)) >= 66 And Asc(Left(h, 1)) <= 70 Then
IsChinese = True
End If
End Function
Function StringType(strText As String, Optional outPutType As Integer = 1, Optional sumVar As Boolean = False) As Variant
Dim strtemp As String, blnArray(1 To 5) As String, strPreType As Integer
Dim intNum As Integer, startPos As Integer, intlen As Integer
Dim strArray As Variant, strCompare1 As String, strCompare2 As String, dblSum As Double
If sumVar = True And Not (outPutType <> 2 Or outPutType <> 4) Then sumVar = False
For i = 1 To Len(strText)
strtemp = Mid(strText, i, 1)
If i > 1 Then strCompare1 = WorksheetFunction.Asc(Mid(strText, i - 1, 3))
strCompare2 = WorksheetFunction.Asc(Mid(strText, i, 2))
If WorksheetFunction.Dbcs(strtemp) = strtemp Then
strtemp = WorksheetFunction.Asc(strtemp)
If IsLike(strtemp, "[0-9]") Or IsLike(strCompare1, "[0-9].[0-9]") Or IsLike(strCompare2, "-[0-9]") Then
If strPreType = 4 Then
blnArray(4) = Left(blnArray(4), Len(blnArray(4)) - 1) & intNum
Else
intNum = 1
blnArray(4) = blnArray(4) & "- " & i & "/" & intNum
End If
strPreType = 4
intNum = intNum + 1
ElseIf IsLike(strtemp, "[a-zA-Z]") Then
If strPreType = 5 Then
blnArray(5) = Left(blnArray(5), Len(blnArray(5)) - 1) & intNum
Else
intNum = 1
blnArray(5) = blnArray(5) & "- " & i & "/" & intNum
End If
strPreType = 5
intNum = intNum + 1
ElseIf IsChinese(strtemp) Then
If strPreType = 1 Then
blnArray(1) = Left(blnArray(1), Len(blnArray(1)) - 1) & intNum
Else
intNum = 1
blnArray(1) = blnArray(1) & "- " & i & "/" & intNum
End If
strPreType = 1
intNum = intNum + 1
Else
strPreType = 0
End If
Else
If IsLike(strtemp, "[0-9]") Or IsLike(strCompare1, "[0-9].[0-9]") Or IsLike(strCompare2, "-[0-9]") Then
If strPreType = 2 Then
blnArray(2) = Left(blnArray(2), Len(blnArray(2)) - 1) & intNum
Else
intNum = 1
blnArray(2) = blnArray(2) & "- " & i & "/" & intNum
End If
strPreType = 2
intNum = intNum + 1
ElseIf IsLike(strtemp, "[a-zA-Z]") Then
If strPreType = 3 Then
blnArray(3) = Left(blnArray(3), Len(blnArray(3)) - 1) & intNum
Else
intNum = 1
blnArray(3) = blnArray(3) & "- " & i & "/" & intNum
End If
strPreType = 3
intNum = intNum + 1
Else
strPreType = 0
End If
End If
Next
strtemp = ""
strArray = Split(blnArray(outPutType), "-")
For i = 1 To UBound(strArray)
intNum = InStr(1, strArray(i), "/")
startPos = Mid(strArray(i), 1, intNum - 1)
intlen = Mid(strArray(i), intNum + 1)
If sumVar Then
dblSum = dblSum + WorksheetFunction.Asc(Mid(strText, startPos, intlen)) Else
strtemp = strtemp & Mid(strText, startPos, intlen) & " "