엑셀함수 스페셜: 사용자 정의함수 - 한글과 영어가 섞여있을때 한글/영어만 추출

먼저, 이 것은 제가 만든 것이 아닙니다. 출처를 아래에 밝힙니다.

http://kin.naver.com/detail/detail.php?d1id=1&dir_id=1050202&eid=Cldup1sGgmV5wXb3Ktt4CKnryRloYMd0&qb=x9Gx2yC757/rwNogwaTAxyDH1Lz2&enc=euc-kr

사용자정의함수란, 엑셀에서 제공하고 있지 않은 기능을 가진 함수를 VBA를 이용하여 사용자가 직접 정의하여 만드는 함수입니다. 따라서 자기가 쓰고자하는 목적을 구현할 수 있기에 편리합니다만, VBA를 다룰 줄 알아야하기때문에 상당히 고급의 경지에 올라야합니다.

저도 VBA는 잘 모르기 때문에, 여기서는 위의 출처에 나온 내용만을 따라하도록 하겠습니다.

참고로, 한글,영어,숫자등이 섞여있는데 숫자만을 분리하고 싶다면? 아래 코드를 잘 보시면 

strEng = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"

이런 부분이 있습니다. 그러니까 이런 영문알파벳을 찾으면 어떻게 하라는 이야기지요. 여기서 영문알파벳이 들어가 있는 부분을 숫자로 바꿔서

strEng = "0123456789"

이렇게 쓰면

보시는 것처럼 숫자와 문자가 분리됩니다.  프로그래밍을 해보신 분이라면 VBA를 배우기가 어렵지 않을 것입니다. 더 자유로운 엑셀 사용을 위해 한번 도전해 보시지 않으시렵니까? ^^*

=======================================================================================

1.해당 엑셀 시트에서 Alt+F11을 누르거나, 도구-매크로-'Visual Basic Editor"를 실행합니다.

2. VBA편집기가 나오면, 메뉴바에서 "삽입-모듈"을 실행합니다.

3. 하얀 백지화면이 나오면 아래 코드를 그대로 복사해다가 붙여넣습니다.

4. Alt+F11을 눌러 다시 원래의 워크시트로 돌아오십니다.

5. 일반 워크시트 함수와 똑같이 사용하시면 됩니다.

 

=KeSplit(한영섞인문자열 또는 셀 주소,구분기호)  (※ 구분기호를 생략하면 '|'가 삽입됨)

e.g.

= Kesplit("He wants to go. 그는 가고 싶어한다.","#")

또는

= KEsplit(A1, "@") 등등..

Pack your things. 네 물건들을 싸라.	Pack your things. #네 물건들을 싸라.
비행기 날개 the wings of an airplane	비행기 날개 #the wings of an airplane
나는 수영에 흥미가 있다. I'm interested in swimming.	나는 수영에 흥미가 있다. #I'm interested in swimming.
draw pictures with crayons 크레용으로 그림을 그리다	draw pictures with crayons #크레용으로 그림을 그리다

 

6. 위 함수로 구분기호가 삽입되었다면, 간단히 Left함수를 이용해 영문을 뽑아내세요.

 

A1: "Pack your things. #네 물건들을 싸라." 일때

=Left(A1,Find("#",A1)-1)

 

Function KEsplit(str As String, Optional spliter As String) As String

Dim intLen As Integer
Dim i As Integer
Dim ChngStart As Integer
Dim strEng As String
Dim strSymbol As String
Dim strIndv As String
Dim isEng As Boolean

strEng = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
strSymbol = ".;,?!""')([]"

    intLen = Len(str)
           
    If InStr(1, strEng, Left(str, 1)) > 0 Then
                 isEng = True
    Else    
            If InStr(1, strSymbol, Left(str, 1)) > 0 And InStr(1, strEng, Mid(str, 2, 1)) > 0 Then               

isEng = True
            Else
               isEng = False
            End If            
    End If       
If isEng Then          
    For i = 1 To intLen            
            strIndv = Mid(str, i, 1)            
            If strIndv >= "가" And strIndv <= "힣" Then                 
                If InStr(1, strSymbol, Mid(str, i - 1, 1)) > 0 Then 

                        ChngStart = i - 1
                        Exit For
                Else
                        ChngStart = i
                        Exit For
                End If                            
            End If    
    Next i    
        If spliter = "" Then spliter = "|"                    
                    KEsplit = Left(str, ChngStart - 1) & spliter & Mid(str, ChngStart, intLen - ChngStart + 1)               
Else 

    For i = 1 To intLen            
            strIndv = Mid(str, i, 1)            
            If InStr(1, strEng, strIndv) > 0 Then                
                If InStr(1, strSymbol, Mid(str, i - 1, 1)) > 0 Then 
                        ChngStart = i - 1
                        Exit For
                Else
                        ChngStart = i
                        Exit For
                End If                
            End If    
    Next i    
    If spliter = "" Then spliter = "|"    
            KEsplit = Left(str, ChngStart - 1) & spliter & Mid(str, ChngStart, intLen - ChngStart + 1)
End If

End Function