엑셀에서 규칙을 갖는 문자열 추출하기

엑셀에서 규칙을 갖는 문자열 추출하는 방법에 대해서 알아보도록 하겠습니다. 이번 글에서는 ‘과일별 코드, 0x1 : 바나나, 0x2 : 사과, 0x3 : 배’ 라는 문자열을 ‘ 1 = 바나나, 2 = 사과, 3 = 배’ 라는 문자열로 자동으로 변경하는 매크로를 예시로 작성해 보도록 합니다.

엑셀로 데이터를 다루다보면 앞선 예시와 같이 일정한 규칙을 가지고 작성된 구문의 작성양식을 수정해야 할 때가 있습니다. 엑셀에서 VBA 매크로를 이용해서 어떻게 문장 안에서 해당 내용을 찾고 양식을 바꿀 수 있는지 순차적으로 알아보도록 하겠습니다.

Table of Contents

• 엑셀에서 규칙을 갖는 문자열 추출
• 특정 문자(delimiter)를 기준으로 문자열 나누기
• 공통으로 포함된 문자열 확인하기

엑셀에서 규칙을 갖는 문자열 추출

예시의 내용안에는 정리하고 싶은 과일별 코드 외에도 문장의 앞에 필요없는 문구가 포함되어 있습니다. 프로그래밍 전략은 다음과 같습니다.

1. 데이터를 구분할 수 있는 문자를 찾습니다. 예시에서는 콤마(,)를 기준으로 텍스트를 더 작은 단위로 구분합니다.
2. 나눠진 텍스트를 순차적으로 접근하여 문자열 안에 ‘0x’라는 문자가 포함되어 있는지 확인하고 해당 문자열의 시작 위치를 파악합니다.
3. ‘0x’라는 문자열 뒤에 바로 시작되는 숫자를 찾습니다.
4. 나눠진 텍스트를 콜론(:)을 기준으로 분리합니다. 분리 된 데이터 중 두번째 문자열을 값으로 저장합니다.
5. 3단계에서 분리한 숫자와 4단계에서 분리한 문자열을 조합하여 새로운 양식 데이터로 조합합니다.

엑셀 파일을 연 뒤, B2 셀에 예시 문자열을 입력하였습니다.

엑셀 파일을 저장할 때 주의할 점은 기본으로 저장되는 xlsx 확장자가 아닌 ‘Excel 매크로 사용 통합 문서’형태로 열어야 합니다.

Alt+F11을 눌러서 VBA 편집기를 연 뒤 화면 좌측에 프로젝트 창에서 해당 문서를 오른클릭하여 나오는 메뉴 중 [삽입] – [모듈]을 클릭하여 새로운 모듈을 생성합니다.

화면 좌측 프로젝트 창에 새로운 모듈(Module1)이 생성이 되고 화면 오른편에서 코드를 입력할 수 있습니다.

다음의 코드를 입력한 뒤 커서를 코드 중간에 위치한 상태에서 ‘F5’ 버튼을 눌러서 매크로를 실행시켜봅니다. 이 때 엑셀에서 셀은 변경하고자 하는 대상 문자열을 선택한 상태여야 합니다.

Sub 문자열양식변경()

    Dim targetString As String
    
    targetString = ActiveCell.Value
    
    MsgBox targetString
    Debug.Print targetString

End Sub

다음과 같이 메시지 창이 뜨면서 선택한 셀의 텍스트 내용이 출력되는 것을 볼 수 있습니다.

또한 VBA 편집기 하단에 직접 실행 창에 동일한 내용이 출력된 것을 볼 수 있습니다. 프로그래밍 중간중간 메시지 박스나 프린트 구문을 이용해서 중간 검증을 하도록 하겠습니다.

예시 문자열의 구조를 살펴보면 데이터가 콤마(,)를 기준으로 구분되어 있는 것을 확인할 수 있습니다. 콤마를 기준으로 데이터를 나누면 다음과 같이 4개의 문자열로 구분됩니다.

특정 문자(delimiter)를 기준으로 문자열 나누기

앞선 예시 문자열에는 공통적으로 “0x” 라는 문자가 포함되어 있습니다. 예시 문자열을 “0x”라는 문자열을 기준으로 나누게 되면 다음의 네 개의 문자열로 나눌 수 있게 됩니다.

• 과일별 코드,
• 1 : 바나나,
• 2 : 사과,
• 3 : 배

위와 같이 특정 문자를 기준으로 문자열을 나누기 위해서 split라는 함수를 이용하겠습니다.

Split 함수는 기본적으로 2개의 인자를 받습니다. 첫번째 인자는 나눠질 텍스트이고 두 번째 인자는 구분이 되는 기준 문자열입니다. 첫번째 인자로는 선택한 셀의 내용을 전달하고, 두번째 인자로는 delimiter로 설정한 “0x”값을 전달합니다. 코드를 정리하면 아래와 같습니다.

Sub 문자열양식변경()
    Dim targetString As String
    Dim delimiter As String
    Dim splitArr() As String

    targetString = ActiveCell.Value
    delimiter = "0x"
    splitArr = Split(targetString, delimiter)
End Sub

실행을 하였을 때 제대로 실행되는지를 확인하는 방법 중 앞서 설명한 Debug.Print나 MsgBox 메서드 외에 지역 창을 이용한 방법이 있습니다. 메뉴 중 [보기] – [지역 창]을 클릭하면 오른쪽 하단에 지역창이 뜹니다.

코드의 중간에 중단점을 생성할 수 있는데 코드 입력창에서 왼쪽편에 빨간색 동그라미가 위치한 부분을 클릭해주면 됩니다. 중단점을 생성한 뒤 프로그램을 실행하면 순차적으로 프로그램이 실행되다 중단점에 도달하면 프로그램이 멈추고 사용자가 다시 시작할 때까지 기다립니다.

중단점을 이용해서 프로그램을 중단할 경우 앞서 열어놓은 지역창에서 각각의 변수들의 현재 값을 볼 수 있습니다. splitArr를 열어보면 앞서 예상한 4개의 원소를 갖는 배열이 생성된 것을 확인할 수 있습니다.

공통으로 포함된 문자열 확인하기

네 개의 문자열 중 우리가 사용하고자 하는 것은 앞에 ‘과일변 코드 ‘를 제외한 나머지 문자열 입니다. 이들 세 개의 문자열과 처음의 문자열의 차이점을 가지고 문자열을 구분해 보도록 하겠습니다. 가장 큰 차이점은 우리가 필요로 하는 문자열은 콜론(:)을 포함하고 있다는 것입니다. 콜론을 중심으로 앞에는 숫자가 뒤에는 문자가 포함된 것을 확인할 수 있습니다.

위의 내용을 바탕으로 다음과 같은 전략을 세워볼 수 있습니다.

1. 배열에 저장된 문자열을 순차적으로 접근합니다. 접근한 문자열에 콜론(:)이 포함되어 있는지를 살펴보고 콜론이 포함되어 있을 때 문자열 안에서 정보를 추출합니다.
2. 콜론(:)의 위치를 중심으로 앞쪽으로 숫자를 찾아갑니다. 숫자는 한 문자로 이뤄져 있기 때문에 앞으로 한문자씩 비교하면서 숫자가 처음 나오는 위치에 숫자를 값으로 저장합니다.
3. 콜론(:) 위치를 중심으로 뒷쪽의 데이터를 문자로 저장합니다. 데이터 중에는 콤마(,)를 포함하는 경우가 있습니다. 콤마가 있을 경우에는 콤마를 제외한 부분까지의 문자를 저장합니다.
4. 값과 문자를 추출한 뒤 우리가 원하는 양식으로 변경하여 순차적으로 누적합니다.

완성된 코드는 아래와 같습니다.

Sub 문자열양식변경()

    Dim targetString As String
    Dim delimiter As String
    Dim splitArr() As String
    Dim stringLoc As Integer
    Dim elemValue As String
            
    
    targetString = ActiveCell.Value
    delimiter = "0x"
    splitArr = Split(targetString, delimiter)
    rstStr = ""
    digitValue = -1
    
    For k = 0 To UBound(splitArr)
        
        elemValue = splitArr(k)
        stringLoc = getStringLoc(elemValue, ":")
        
        If stringLoc > 0 Then
            firstNumLoc = getFirstNumLoc(elemValue, stringLoc)
            digitValue = Mid(elemValue, firstNumLoc, 1)
            
            'comma 위치 확인
            commaLoc = getStringLoc(elemValue, ",")
            
            If commaLoc = -1 Then
            'comma가 없을 경우
                stringValue = Mid(elemValue, stringLoc + 1, Len(elemValue) - stringLoc)
                rstStr = rstStr & digitValue & "=" & stringValue
            Else
            'comma가 있을 경우
                stringValue = Mid(elemValue, stringLoc + 1, commaLoc - stringLoc - 1)
                rstStr = rstStr & digitValue & "=" & stringValue & ", "
                
            End If
            
            
        End If
            
    Next k
    
    ActiveCell.Offset(0, 1).Value = rstStr

End Sub


Public Function getStringLoc(xValue As String, divider As String)

    For getStringLoc = 1 To Len(xValue)
    
        If Mid(xValue, getStringLoc, 1) Like divider Then Exit Function
    
    Next
    
    getStringLoc = -1

End Function

Public Function getFirstNumLoc(xValue As String, srtLoc As Integer)

    For getFirstNumLoc = srtLoc To 1 Step -1
        'Debug.Print Mid(xValue, srtLoc, 1)
        If Mid(xValue, getFirstNumLoc, 1) Like "#" Then Exit Function
    
    Next
    
    getFirstNumLoc = -1
    
End Function

코드는 한 개의 Sub와 2 개의 Function으로 이루어져 있습니다. 먼저 아래쪽에 정의된 각각의 Function에 대해서 설명하면 다음과 같습니다.

getStringLoc function은 탐색할 대상이 되는 텍스트(xValue)와 찾는 문자열(divider)를 인자로 받습니다. xValue 문자열을 처음부터 순차적으로 한 개씩 찾아가면서 divider로 전달한 문자열과 일치한지를 확인하며 일치하는 순간 함수를 종료하고 해당 위치값을 정수로 전달합니다.

두 번째 함수인 getFirstNumLoc 역시 앞선 함수와 비슷한 매커니즘으로 움직입니다. 다만 다른 것은 받는 인수 중 시작위치(srtLoc)가 있다는 것이며, 시작위치에서부터 앞으로 거꾸로 탐색해가며 처음으로 숫자가 발견되는 위치를 리턴합니다.

앞서 콜론(:)의 위치를 찾아 그 위치를 getFirstNumLoc 함수에 전달하게 되면 이 함수는 콜론으로부터 앞으로 가면서 나타나는 첫번째 숫자를 찾게 되고 이 값이 우리가 원하는 각 코드별 숫자값이 됩니다.

실제 Sub에서는 getStringLoc 함수를 두 가지 방법으로 활용하고 있는데 한가지는 콜론(:)의 위치를 찾는 용도이고 다른 하나는 콤마(,)의 위치를 찾는 용도로 사용됩니다.

콤마(,)가 문자열 안에 있을 경우에는 뒤에 다른 코드가 추가적으로 기술된다는 의미이며, 코드값은 찾았으나 콤마를 찾지 못한 경우에는 해당 자료가 마지막 자료임을 의미하게 됩니다. 그래서 rstStr에 결과를 누적하게 될 때 계속되는 경우에는 마지막에 콤마(,)를 붙이고 그렇지 않은 경우에는 콤마 없이 문자열을 누적합니다.

이제 셀이 B2를 선택한 상태에서 [보기] – [매크로] – [매크로 보기]를 선택한 뒤 자신이 만큼 매크로를 실행시키면 C2 셀에 양식이 변경되어 출력이 된 것을 확인할 수 있습니다.