☰

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

相關內容