엑셀 셀에서 영문, 숫자, 문자만 따로 추출하는 매크로 만드는 방법(사용자 정의 함수)

 셀에 입력된 내용 중 숫자만 필요하거나, 영문자, 혹은 한글 같은 문자만 필요한 경우가 있다.

 이런 경우 여러 가지 함수를 잘 조합하면 원하는 부분만 추출해서 사용하는 것이 가능한데 너무 많은 함수의 조합은 상당히 복잡해 보이고 복사해서 사용하다 실수가 발생하는 경우도 많기 때문에 자주 사용한다면 매크로를 이용하여 사용자 정의 함수를 만들고 사용하는 것이 더 좋지 않을까 한다.

 

매크로 설명

 사용자 정의 함수 형태의 매크로로 엑셀의 함수를 사용하듯이 extract_chars(추출 셀, 옵션) 형태로 사용이 가능하다.

 옵션은 아래와 같다.

extract_chars(추출 셀, 1) : 영문만 추출. extract_chars(추출 셀, 2) : 숫자만 추출. extract_chars(추출 셀, 3) : 한글만 추출. extract_chars(추출 셀, 4) : 영문과 한글 추출.

 

 추가로 옵션은 1,2나 1,2,3 등과 같이 한 번에 여러 옵션을 넣을 수 있게 만들었으나 추출되는 방식이 영문, 숫자, 한글 순으로 추출되기 때문에 셀의 내용이 '1seogilang 2이것3저것'이라고 되어있는 셀에서 'extract_chars(a1,1,2,3)'과 같은 형태로 함수를 사용하면 'seogilang123이것저것' 과 같이 추출된다.

 이 부분은 연습삼아 그냥 한번 넣어 본 기능이니 필요하다면 사용해도 좋고, 사용하지 않아도 좋고, 원하는 내용으로 수정해도 된다.

 

셀에서 숫자, 문자만 따로 추출하는 매크로 만들기

1. vba 실행.

 엑셀에서 [Alt + F11]을 눌러 vba를 실행하자.

 

2. 새 모듈 만들기.

 vba 창이 나타나면 상단의 메뉴에서 [삽입 -> 모듈]을 클릭하여 새 모듈을 만들고 아래의 코드를 복사하여 붙여 넣자.

Function extract_chars(input_str As String, ParamArray modes() As Variant) As String  Dim output_str As String  Dim i As Integer, char As String  Dim mode As Variant       ' 모든 모드에 대해 반복  For Each mode In modes   ' 문자열에서 문자만 추출하기   If mode = 1 Then    For i = 1 To Len(input_str)     char = Mid(input_str, i, 1)     If Asc(char) >= 65 And Asc(char) <= 90 Or Asc(char) >= 97 And Asc(char) <= 122 Then      output_str = output_str & char     End If    Next i   End If   ' 문자열에서 숫자만 추출하기   If mode = 2 Then    For i = 1 To Len(input_str)     char = Mid(input_str, i, 1)     If IsNumeric(char) Then      output_str = output_str & char     End If    Next i   End If   ' 문자열에서 한글만 추출하기   If mode = 3 Then    For i = 1 To Len(input_str)     char = Mid(input_str, i, 1)     If AscW(char) >= &HAC00 And AscW(char) <= &HD7A3 Then      output_str = output_str & char     End If    Next i   End If   ' 문자열에서 문자와 한글만 추출하기   If mode = 4 Then    For i = 1 To Len(input_str)     char = Mid(input_str, i, 1)      If Asc(char) >= 65 And Asc(char) <= 90 Or Asc(char) >= 97 And Asc(char) <= 122 Or AscW(char) >= &HAC00 And AscW(char) <= &HD7A3 Then       output_str = output_str & char      End If     Next i    End If   Next mode       ' 결과 반환  extract_chars = output_str End Function

3. 매크로 실행.

 매크로는 함수 형태이기 때문에 위에서 언급했듯이 extract_chars(추출 셀, 옵션) 형태로 사용이 가능하다.

 

코드의 간단한 설명

 코드에 대한 간단한 설명을 추가해 봤다.

 본인의 입맛대로 수정하고자 하지만 잘 모겠다면 참고해 보자.

 

- Function extract_chars(input_str As String, ParamArray modes() As Variant) As String

 : 사용자 정의 함수를 시작하는 부분으로 함수의 이름, 입력 값과 변수 등을 정의한다.

 참고로 modes는 여러 옵션을 넣어보기 위해 배열로 지정했지만 하나의 옵션 값만 받아올 것이라면 integer로 값을 받아와도 된다.

 

- For Each mode In modes

 : 배열로 받은 옵션 값의 수만큼 반복되는 부분으로 옵션값을 배열로 받지 않는다면 빼도 된다.

 

- If mode = 1 Then

 : 옵션 값의 조건문 부분

 

- For i = 1 To Len(input_str)

 : 셀 내용의 길이만큼 반복문이 실행된다.

 

- char = Mid(input_str, i, 1)

 : 반복문이 진행되면서 문자를 하나씩 char 변수 값에 넣게 된다.

 첫 문자부터 마지막 문자까지를 비교하기 위한 변수라고 보면 된다.

 

- If Asc(char) >= 65 And Asc(char) <= 90 Or Asc(char) >= 97 And Asc(char) <= 122 Then

 : char 변수의 문자 값이 조건에 맞는지 확인하는 부분.

 해당 부분을 수정하는 것으로 문자만, 숫자만, 한글만, 특수 문자만 등등 원하는 문자 추출 매크로를 만들 수 있다.

 

- output_str = output_str & char

 : 조건에 맞는 값은 output_str 변수 값에 순서대로 추가된다.

 

그 외 내용

- 상황에 따라서 함수 조합을 사용해야 할 수도 있다.

 매크로가 포함된 엑셀 문서를 사용하지 못하는 환경이라면 어쩔 수 없이 여러 함수를 조합해서 사용해야 할 수도 있다.

 상황에 따라서 매크로를 사용할지 함수 조합을 사용하지 결정하자.

 함수 조합으로 추출하는 방법은 아래의 글을 참고해 보자.

 셀에서 숫자만 추출하는 방법

 

- if Asc 부분을 변경하면 원하는 문자만 추출할 수도 있다.

 위 코드의 if asc 부분은 문자 추출과 관련된 조건문 부분으로 특수문자라거나 특별히 원하는 문자가 있다면 이 부분을 수정해서 특정 문자만 추출하는 것도 가능하다.