excel - 如何使用 VBA 定义单元格中的字符是数字、字母还是特殊字符？

Question

我想通过一个包含数字、字母和其他不同长度字符的单元格的列，并在第二行中对该单元格的"template"进行排序。
数字应变为“N”，字母应变为“L”，其他字符应保持不变。
所以例如如果 A1 包含“A35p@5”，则 B1 中的输出应为“LNNL@N”。
到目前为止，这是我的代码，但它仅适用于第一个字符。同样对于其他或特殊字符，输出只是继续复制字符之后的任何内容。在下面的 Excel 和 VBA 代码中查看我的测试用例的输出。我在这里想念什么？

Sub myMacro()

    'Define variables
    Dim char As String

    For I = 1 To Range("A10").End(xlUp).Row
        For J = 1 To Len(Range("A" & I))
            
            char = Left(Range("A" & I), J)
            
            If IsNumeric(char) Then
                Range("B" & I).Value = "N"
            ElseIf IsLetter(char) Then
                Range("B" & I).Value = "L"
            ElseIf IsSecialCharacter(char) Then
                Range("B" & I).Value = char
            End If
            
        Next J
    Next I
End Sub

Function IsLetter(r As String) As Boolean
    If r = "" Then Exit Function
    Dim x As Long
    x = Asc(UCase(r))
    IsLetter = (x > 64 And x < 91)
End Function

Function IsSecialCharacter(r As String) As Boolean
    If r = "" Then Exit Function
    Dim x As Long
    x = Asc(UCase(r))
    IsSecialCharacter = (x > 31 And x < 48) Or (x > 57 And x < 65) Or (x > 90 And x < 97) Or (x > 122 And x < 127)
End Function

Answer 1

很好的问题和查询。我花了一些时间给你几个选择:

1) Microsoft 365 动态数组功能:
如果你有 Microsoft365，你可以使用:
B1 中的公式:

=IFERROR(LET(X,MID(A1,SEQUENCE(LEN(A1)),1),CONCAT(IF(ISNUMBER(X*1),"N",IF(ISNUMBER(FIND(UPPER(X),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")),"L",X)))),"")

2) Excel VBA - Like()运营商:
这是一个 VBA 例程，它将循环每个字符串中的每个字符并通过 Like() 进行比较。运算符(operator):

Sub Test()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
Dim lrow As Long, x As Long, i As Long, arr As Variant
Dim char As String, tmp As String, full As String

lrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
arr = ws.Range("A1:B" & lrow).Value
For x = LBound(arr) To UBound(arr)
    If Len(arr(x, 1)) > 0 Then
        full = ""
        For i = 1 To Len(arr(x, 1))
            char = Mid(arr(x, 1), i, 1)
            If char Like "[!A-Za-z0-9]" Then
                tmp = char
            ElseIf char Like "#" Then
                tmp = "N"
            Else
                tmp = "L"
            End If
            full = full & tmp
        Next
        arr(x, 2) = full
    End If
Next
ws.Range("A1:B" & lrow).Value = arr

End Sub

3) Excel VBA - Regexp对象:
而 Like()运算符看起来像一个正则表达式，它并不完全相同。然而，我们也可以使用“RegeXp”对象:

Sub Test()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
Dim lrow As Long, x As Long, arr As Variant

lrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
arr = ws.Range("A1:B" & lrow).Value

With CreateObject("VBScript.RegExp")
    .Global = True
    For x = LBound(arr) To UBound(arr)
        .Pattern = "[a-zA-Z]"
        arr(x, 2) = .Replace(arr(x, 1), "L")
        .Pattern = "\d"
        arr(x, 2) = .Replace(arr(x, 2), "N")
    Next
End With

ws.Range("A1:B" & lrow).Value = arr

End Sub