영문자만 골라내기

Step 1: 이번 시간의 메일 한 통

엑셀을 아주 다양한 용도로 잘 활용하고 계시는군요. 누가 만들었는지 모르겠지만 스프레드시트(Spreadsheet)라는 용어, 우리말로 "표계산 프로그램"이라는 단어는 별로 마음에 들지 않습니다. 이런 해괴한(?) 이름을 붙여 놓으니까 엑셀이라고 하면,

이런 선입견을 가지게 되는 것이지요. 하여튼 아래의 버튼을 누르면 현재 시트 내에서 알파벳만 모조리 골라서 새로운 시트로 옮겨줍니다.

잘 되지요? 코드를 보시기 전에 머리 속으로 작업 순서를 한번 떠올려 보세요. 어떤 오브젝트나 프로퍼티, 메서드를 사용할 것인가가 떠오르면 좋겠지만 단순히 시나리오만 짜 보는 것도 괜찮습니다.

Step 2: ExtractAlphabet 프로시저

Sub ExtractAlphabet()
    Dim rngSource As Range
    Dim rngCell As Range
    Dim rngTarget As Range
    Dim i As Integer
    Dim j As Integer
    Dim intLen As Integer
    Dim intCode As Integer
    Dim shtTarget As Worksheet
    Dim shtSheet As Worksheet
    Dim strTemp As String
    Dim strChar As String
    Set shtSheet = Sheets("Preface")
    Set rngSource = shtSheet.UsedRange
    Set shtTarget = Worksheets.Add
    Set rngTarget = shtTarget.Columns(1)
    '''필요한 변수와 타입, 즉 등장인물과 배역을 설정해 주었습니다.

    For Each rngCell In rngSource
        If IsError(rngCell.Value) Then GoTo NextCell
    '''rngSource는 현재 시트의 UsedRange가 지정되어 있습니다. 따라서 Preface 시트 내에서
    '''사용된 영역의 셀을 하나씩 돌아가면서 검색을 하는 것입니다.

        intLen = Len(rngCell)

        For i = 1 To intLen
        '''셀을 하나씩 검색할 때 그냥 검색하는 것이 아니라 각 셀에 들어있는 문자열 정보를
        '''하나씩 조사를 합니다. 무슨 조사냐구요? 각각의 문자가 한글인지 알파벳인지를 일단
        '''알아야 나중에 어디로 옮겨도 옮기겠지요?

            strChar = Mid(rngCell, i, 1)
            intCode = Asc(strChar)
            If (intCode >= 65 And intCode <= 90) Or (intCode >= 97 And intCode <= 122) Then _
                strTemp = strTemp & strChar
            '''이 부분이 오늘의 핵심입니다. 65, 90 등과 같은 숫자는 어디 하늘에서 떨어진 것일까요?
            '''컴퓨터는 모든 것을 숫자로 받아들입니다. 한글이나 한자, 영어는 물론이고 심지어는
            '''그림이나 소리까지도 말입니다.

            '''65라는 것은 대문자 A, 90은 대문자 Z에 해당하는 아스키 코드 값입니다.
            '''그것을 어떻게 알 수 있느냐구요? 워크시트 함수 중에 Char 이라는 것이 있습니다.
            '''이것은 코드 번호에 해당하는 문자열을 알려주는 함수인데… 확인을 해 볼까요?
            '''아래의 회색 셀에 1~255 사이의 숫자값을 넣어 보세요.

            '''따라서 영어 대문자 A~Z, 소문자 a~z 값일 경우에만 strTemp 변수에 차곡차곡 담습니다.

        Next i

        rngTarget.Cells(1).Offset(j) = strTemp
        '''알파벳만 다 골라 낸 다음 새로 삽입한 시트에 뿌려 줍니다.

        If Len(strTemp) > 0 Then j = j + 1
        strTemp = ""
NextCell:
    Next rngCell
    rngTarget.Columns.AutoFit
End Sub

필요한 변수와 타입, 즉 등장인물과 배역을 설정해 주었습니다. rngSource는 현재 시트의 UsedRange가 지정되어 있으므로 Preface 시트 내에서 사용된 영역의 셀을 하나씩 돌아가면서 검색을 합니다. 그냥 검색하는 것이 아니라 각 셀에 들어있는 문자열 정보를 하나씩 조사를 합니다. 무슨 조사냐구요? 각각의 문자가 한글인지 알파벳인지를 일단 알아야 나중에 어디로 옮겨도 옮기겠지요?

Step 3: 오늘의 핵심, 아스키 코드

이 부분이 오늘의 핵심입니다. 65, 90 등과 같은 숫자는 어디 하늘에서 떨어진 것일까요? 컴퓨터는 모든 것을 숫자로 받아들입니다. 한글이나 한자, 영어는 물론이고 심지어는 그림이나 소리까지도 말입니다. 65라는 것은 대문자 A, 90은 대문자 Z에 해당하는 아스키 코드 값입니다. 그것을 어떻게 알 수 있느냐구요? 워크시트 함수 중에 Char 이라는 것이 있습니다. 이것은 코드 번호에 해당하는 문자열을 알려주는 함수인데… 확인을 해 볼까요? 아래의 셀에 1~255 사이의 숫자값을 넣어 보세요.

따라서 영어 대문자 A~Z, 소문자 a~z 값일 경우에만 strTemp 변수에 차곡차곡 담습니다. 알파벳만 다 골라 낸 다음 새로 삽입한 시트에 뿌려 줍니다.

Step 4: 숙제

엄청 간단하지요? 거~ 뭐, 쉽구만. 아무 것도 아니네. 이런 생각에 하산 하시려는 분들을 위해 숙제를 내어 드리겠습니다. ^^

우선, 질문하신 분의 경우, 영한 사전의 내용을 분리하는데 위의 코드를 사용하면 공란이나 콤마 등이 모두 제거되어 버리므로 띄어쓰기가 전혀 되지 않을 것입니다. 이것을 해결해 보세요. 힌트를 드리자면… 콤마는 아스키 코드값이 44, 공란은 32 입니다.

그리고 또 한가지! 셀 하나 검사하고 결과값을 워크시트에 옮기고, 또 다음 셀 검사하고 워크시트에 옮기고… 이렇게 매번 메모리 변수와 워크시트 사이를 왔다갔다 하면서 작업하는 방법은 그다지 효율적이지 못합니다. 배열을 사용하여 값을 차례대로 담아 두었다가 맨 마지막에 한꺼번에 좌악 워크시트로 옮기는 방법을 연구해 보세요.

오늘은 여기까지…

정리 — 영문자 추출 핵심

자주 묻는 질문 (FAQ)

마치며

엑셀은 복잡한 숫자만 다루는 프로그램이 아니라 문자열 처리까지 아주 다양한 용도로 활용할 수 있습니다.