Числовой формат Excel Letter Plus

Question

У меня проблема с поиском способа навязать определенный формат для определенного столбца.

Я имею в виду электронную таблицу, которую я использую для отслеживания запасов. Он используется несколькими людьми, и, как следствие, форматы местоположения несовместимы.

(например: А1, А01, А-1, А-01)

Это делает сортировку болью, имея A1 выше A10, а не A1, A2. Я хочу, чтобы это было исправлено, чтобы независимо от того, что было напечатано, оно было исправлено в формате букв +2-значный номер (A01).

заранее спасибо

Answer 1

Это невозможно в поле ввода.

• Вы можете добавить дополнительный (вспомогательный) столбец , содержащий формулу, которая разбивает фрагменты вашей строки, соответствующим образом форматирует и объединяет их.
• если все ваши строки начинаются с буквы A, вы можете просто попросить всех сбросить A (и тире) и просто ввести число (но, возможно, вы только что привели пример)
• Вы можете иметь два столбца, один для альфа-части, а другой для числа
• Вы можете написать макрос, который анализирует все, что они вводят, и выполняет сортировку. Но по опыту, в мире нет макроса, который бы справлялся со всеми абсурдными вариациями, которые потенциально может предложить пользователь - он всегда найдет вариант, который его нарушает.

Answer 2

Поместите следующий макрос событий в область кода рабочего листа:

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

Этот образец макроса отслеживает и корректирует записи в столбце B. Если записи действительны, они остаются одни. Если запись недействительна, она заменяется на A00.

Answer 3

Если вы просто хотите проверить значение и логика проверки не слишком сложна, это можно сделать с помощью стандартной проверки данных Excel. Однако, поскольку вы хотите переформатировать значение, чтобы оно было правильным, вам нужно будет использовать VBA или Visual Studio Tools for Office. Ниже приведен простой макрос VBA, который делает то, что вы хотите.

Волшебство происходит, когда происходит событие Worksheet_Change . Для примера я предполагаю, что вы вводите отдельные значения в первом столбце. Функция CorrectPartNo выполняет тяжелую работу. Он анализирует введенное значение и возвращает правильно отформатированное значение или ничего, что указывает на наличие ошибки проверки (проверяется только максимальная длина). Обработка работает на вашем примере, но, вероятно, она слишком проста для реального мира. Это может быть легко улучшено с помощью регулярного выражения или другой такой же мощной обработки.

Обработчик событий продолжает либо обновляя ячейку с правильно отформатированным номером детали, либо делает значение, введенное пользователем, жирным и красным, чтобы указать на ошибку.

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

Answer 4

Вы также можете использовать проверку данных с понятными подсказками и сообщениями. Формула проверки данных (при условии, что вы хотите прописные буквы):

=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)