☰

Excel 문자 플러스 숫자 형식

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 문자 플러스 숫자 형식

특정 열에 특정 형식을 강제 적용하는 방법을 찾는 데 문제가 있습니다.

제가 다루고 있는 것은 재고를 추적하는 데 사용하는 스프레드시트입니다. 여러 사람이 사용하기 때문에 위치 형식이 일치하지 않습니다.

(예: A1, A01, A-1, A-01)

이로 인해 A1, A2가 아닌 A10 위에 A1이 있어 정렬이 약간 불편해졌습니다. 무엇을 입력해도 영문+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

관련 정보