☰

Excel 字母加数字格式

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 字母加数字格式

我在想办法强制特定列采用某种格式时遇到了问题。

我正在处理的是用于跟踪库存的电子表格。它被几个人使用，因此位置格式不一致。

（例如：A1、A01、A-1、A-01）

这使得排序有点麻烦，因为 A1 位于 A10 上方，而不是 A1、A2。我希望修复这个问题，这样无论输入什么，都会更正为字母+2 位数字格式 (A01)。

提前致谢

答案1

在输入列中这是不可能的。

  • 您可以添加额外（辅助）列其中包含拆分字符串各部分的公式，对每个部分进行相应的格式化，并将它们完美地组合在一起。
  • 如果你所有的字符串都以 A 开头，你可以告诉每个人删除 A（和破折号），然后输入一个数字（但也许你只是举了一个例子）
  • 你可以有二列，一列用于字母部分，一列用于数字
  • 你可以写一个宏它会分析用户输入的内容并进行排序。但从经验来看，世界上没有任何宏可以处理用户可能想出的所有荒谬变体 - 他们总会找到一种破坏它的变体。

答案2

您还可以使用数据验证，并给出易于理解的提示和消息。数据验证公式（假设您希望字母大写）如下：

=AND(CODE(A1)>=65,CODE(A1)<=90,CODE(MID(A1,2,1))>=48,CODE(MID(A1,2,1))<=57,CODE(MID(A1,3,1))>=48,CODE(MID(A1,3,1))<=57)

答案3

将以下事件宏放置在工作表代码区域：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim B As Range, s As String
    Set B = Range("B:B")
    If Intersect(Target, B) Is Nothing Then Exit Sub
    s = Target.Value
    If Len(s) <> 3 Then GoTo fixit
    If Not Left(s, 1) Like "[A-Z]" Then GoTo fixit
    If Not IsNumeric(Mid(s, 2, 2)) Then GoTo fixit
    Exit Sub
fixit:
    Application.EnableEvents = False
        Target.Value = "A00"
    Application.EnableEvents = True
End Sub

此示例宏监视并更正列中的条目乙。如果条目有效，则保留它们。如果条目无效，则将其替换为A00。

答案4

如果您只是想检查值，并且检查逻辑不太复杂，则可以使用标准 Excel 数据验证来完成。但是，由于您希望重新格式化值以使其正确，因此您需要使用 VBA 或 Visual Studio Tools for Office。以下是一个简单的 VBA 宏，可以完成您想要的操作。

奇迹发生在工作表_更改事件被触发。为了举例说明，我假设您在第一列中输入单个值。正确零件编号函数会完成繁重的工作。它会分析输入的值并返回正确格式的值或不返回任何值以表明存在验证错误（它仅检查最大长度）。处理适用于您的示例，但对于现实世界来说可能太简单了。它可以通过正则表达式或其他类似的强大处理轻松增强。

事件处理程序继续通过使用正确格式的零件编号更新单元格或使用户输入的值变为粗体和红色以指示错误。

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim NewValue As String

    If (Target.Column = 1 And Target.Count = 1) Then
        Application.EnableEvents = False
        NewValue = CorrectPartNo(Target.Value)
        If (Len(NewValue) = 0) Then
            Target.Font.Color = vbRed
            Target.Font.Bold = True
        Else
            Target.Value = NewValue
        End If
        Application.EnableEvents = True
    End If
End Sub

Function CorrectPartNo(PartNo As String)
    Dim StartPartNo As String
    Dim EndPartNo As String
    Dim EndPartNoPosition As Integer

    StartPartNo = Left(PartNo, 1)
    If (Mid(PartNo, 2, 1) = "-") Then
        If (Len(PartNo) > 4) Then
            CorrectPartNo = ""
            Exit Function
        Else
            EndPartNoPosition = 3
        End If
    Else
        If (Len(PartNo) > 3) Then
            CorrectPartNo = ""
            Exit Function
        Else
            EndPartNoPosition = 2
        End If
    End If
    EndPartNo = Right("0" + Mid(PartNo, EndPartNoPosition), 2)

    CorrectPartNo = StartPartNo + EndPartNo
End Function

相关内容