Excel에서 예외를 포함하여 텍스트 문자열을 적절한 대소문자로 변환하는 방법은 무엇입니까?

Excel에서는 텍스트 문자열을 쉽게 적절한 대소문자로 변환하기 위해 Proper 함수를 사용할 수 있습니다. 하지만 때로는 다음 스크린샷과 같이 특정 단어들을 제외하고 텍스트 문자열을 적절한 대소문자로 변환해야 할 수도 있습니다. 이 문서에서는 Excel에서 이 작업을 해결하기 위한 몇 가지 빠른 팁에 대해 설명하겠습니다.

수식을 사용하여 예외를 포함한 텍스트 문자열을 적절한 대소문자로 변환

VBA 코드를 사용하여 예외를 포함한 텍스트 문자열을 적절한 대소문자로 변환

수식을 사용하여 예외를 포함한 텍스트 문자열을 적절한 대소문자로 변환

아래의 수식이 이 작업을 신속히 처리하는 데 도움이 될 수 있습니다. 아래와 같이 진행해 주세요:

다음 수식을 입력하세요:

=UPPER(LEFT(A2))&MID(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&PROPER(A2)&" "," Of "," of ")," A "," a "),"Is "," is ")," Usa "," USA ")),2,LEN(A2)) 결과를 얻고자 하는 셀에 입력한 후, 채우기 핸들을 드래그하여 이 수식을 채우면, 특정 예외를 제외한 텍스트 문자열이 적절한 대소문자로 변환됩니다. 스크린샷을 참조하세요:

참고: 위의 수식에서 A2는 변환하려는 셀이며, “Of ”, “A”, “Is”, “Usa”는 변환 후 일반적인 적절한 대소문자 단어입니다. “of ”, “a”, “is”, “USA”는 적절한 대소문자에서 제외하려는 단어들입니다. 필요에 따라 이를 변경하거나 SUBSTITUTE 함수를 사용하여 다른 단어를 추가할 수 있습니다.

VBA 코드를 사용하여 예외를 포함한 텍스트 문자열을 적절한 대소문자로 변환

위의 수식이 이해하고 필요에 맞게 수정하기 어렵다면, 여기서 VBA 코드를 적용하여 이 작업을 완료할 수도 있습니다. 아래 단계를 하나씩 따라 해 보세요.

1. ALT + F11 키를 눌러 Microsoft Visual Basic for Applications 창을 엽니다.

2. 삽입 > 모듈을 클릭하고 모듈 창에 다음 코드를 붙여넣습니다.

VBA 코드: 예외를 포함하여 텍스트 문자열을 적절한 대소문자로 변환:

Sub CellsValueChange()
'Updateby Extendoffice
    Dim xSRg As Range
    Dim xDRg As Range
    Dim xPRg As Range
    Dim xSRgArea As Range
    Dim xRgVal As String
    Dim xAddress As String
    Dim I As Long
    Dim K As Long
    Dim KK As Long
    On Error Resume Next
    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xSRg = Application.InputBox("Original cells:", "KuTools For Excel", xAddress, , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    Set xDRg = Application.InputBox("Output cells:", "KuTools For Excel", , , , , , 8)
    If xDRg Is Nothing Then Exit Sub
    Set xPRg = Application.InputBox("Cells to exclude:", "KuTools For Excel", , , , , , 8)
    If xPRg Is Nothing Then Exit Sub
    Set xDRg = xDRg(1)
    For I = 1 To xSRg.Areas.Count
        Set xSRgArea = xSRg.Areas.Item(I)
        For K = 1 To xSRgArea.Count
            xRgVal = xSRgArea(K).Value
            If Not IsNumeric(xRgVal) Then
                xRgVal = CorrectCase(xRgVal, xPRg)
                xDRg.Offset(KK).Value = xRgVal
            End If
            KK = KK + 1
        Next
    Next
End Sub
Function CorrectCase(ByVal xRgVal As String, ByVal xPRg As Range) As String
    Dim xArrWords As Variant
    Dim I As Integer
    Dim xPointer As Integer
    Dim xVal As String
    xPointer = 1
    xVal = xRgVal
    xArrWords = WordsOf(xRgVal)
    For I = 0 To UBound(xArrWords)
        xPointer = InStr(xPointer, " " & xVal, " " & xArrWords(I))
        Debug.Print xPointer
        Mid(xVal, xPointer) = CorrectCaseOneWord(CStr(xArrWords(I)), xPRg)
    Next I
    CorrectCase = xVal
End Function
Function WordsOf(xRgVal As String) As Variant
    Dim xDelimiters As Variant
    Dim xArrRtn As Variant
    xDelimiters = Array(",", ".", ";", ":", Chr(34), vbCr, vbLf)
    For Each xEachDelimiter In xDelimiters
        xRgVal = Application.WorksheetFunction.Substitute(xRgVal, xEachDelimiter, " ")
    Next xEachDelimiter
    xArrRtn = Split(Trim(xRgVal), " ")
    WordsOf = xArrRtn
End Function
Function CorrectCaseOneWord(xArrWord As String, xERg As Range) As String
    With xERg
        If IsError(Application.Match(xArrWord, .Cells, 0)) Then
            CorrectCaseOneWord = Application.Proper(xArrWord)
        Else
            CorrectCaseOneWord = Application.VLookup(xArrWord, .Cells, 1, 0)
        End If
    End With
End Function

3. 그런 다음 F5 키를 눌러 이 코드를 실행하면 원본 셀을 선택하라는 메시지 상자가 나타납니다. 스크린샷을 참조하세요:

4. 확인을 클릭한 다음 결과를 출력하려는 셀을 선택합니다. 스크린샷을 참조하세요:

5. 계속해서 확인을 클릭하고, 팝업 대화 상자에서 제외할 텍스트를 선택합니다. 스크린샷을 참조하세요:

6. 그런 다음 확인을 클릭하여 대화 상자를 종료하면, 지정된 단어를 제외한 모든 텍스트 문자열이 적절한 대소문자로 변환됩니다. 스크린샷을 참조하세요: