엑셀 VBA 정규표현식 사용하는 방법, 공백 문자 제거하기

엑셀(Excel)/VBA

엑셀 VBA 정규표현식 사용하는 방법, 공백 문자 제거하기

 

환경: Microsoft Excel 2013

 

프로그래밍을 하다 보면 좀더 깔끔하고 효과가 좋은 정규표현식을 사용하게 됩니다. 거의 모든 프로그램 툴에서는 지원합니다. VBA 에서 사용하시려면 정규식 지원 컴퍼넌트인 Microsoft VBScript Reqular Expression 5.5를 추가해야 합니다.

 

먼저 정규식을 사용하기 위해 VBA 편집 에디터에서 [도구] > [참조] 메뉴 선택합니다. 참조 팝업창이 뜨면 사용 가능한 참조 목록에서 Microsoft VBScript Reqular Expressions 5.5 를 선택하고 [확인]을 눌러 창을 닫습니다.

 

엑셀 VBA 정규표현식 사용하는 방법, 공백 문자 제거하기

 

이제 정규식이 들어간 함수를 만들기 위해 개발 [도구] > [매크로] 리본 메뉴를 클릭합니다. 매크로 함수명을 넣고 [만들기] 를 클릭합니다.

엑셀 VBA 정규표현식 사용하는 방법, 공백 문자 제거하기

 

만든 매크로 함수에는 공백 제거 기능이 들어갈 것입니다. 아래 그림처럼 VBA 에서 Trim() 함수를 사용하면 좌우 공백만 제거가 됩니다. 완벽한 공백 제거를 위해 정규식을 이용해 보겠습니다.

엑셀 VBA 정규표현식 사용하는 방법, 공백 문자 제거하기

 

정규식 특정기능 문자매칭에서 “\s” 는 스페이스바, , 줄바꿈 등을 나타냅니다. 이것을 넣고 replace() 함수를 사용해서 모두 제거하는 것입니다. replace() 함수는 정규식에서 지원하는 것입니다. 이 외에도 패턴이 있는지 판단하는 Test() 와 검색한 결과값을 담아서 리턴하는 Execute() 가 있습니다.

 

Sub 정규식함수()

   

    Dim RegEx As Object

    Dim row As Integer

    Dim chName As String

   

    Set RegEx = CreateObject("vbscript.regexp")

   

    Set re = CreateObject("VBScript.RegExp")

    With re

        .Pattern = "\s"

        .Global = True

    End With

   

    '// 공백제거

    Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)

    For Each ea In rng

        ea.Value = re.Replace(ea.Value, "")

    Next ea

 

    Set rng = Nothing

 

End Sub

 

아래 그림은 매크로 함수를 실행한 결과 입니다. Trim() 함수를 사용한 것과는 확연히 차이가 나죠. 공백은 모두 제거했습니다.

엑셀 VBA 정규표현식 사용하는 방법, 공백 문자 제거하기

¤ 정규식 함수와 속성

 

함수는 3가지가 있으며 Replace Test 를 많이 사용합니다. 대부분 패턴으로 값을 찾는 이유가 값을 바꾸거나 일치하는 값이 있는지 찾기 때문입니다.

 

l  Execute : 패턴을 실행하며 결과값을 객체에 담는다. 하나씩 값을 꺼낼 수 있다.

l  Replace : 패턴에 일치하는 값을 검색해서 인수로 입력한 값으로 대체한다.

l  Test : 패턴을 찾아서 일치하는 것이 있는지 결과값을 리턴 한다.

 

속성은 4가지가 있으며 다음과 같다.

 

l  Pattern : 정규식 문자열을 정의합니다.

l  Global : True 는 전체탐색, False 는 첫 번째 일치까지만 탐색, 기본은 False

l  IgnoreCase : True 는 대소문자 구분, False 는 구분하지 않는다. 기본은 False

l  MultiLine : True 는 복수라인탐색, False 는 현재라인만 탐색한다. 기본은 False

 

Posted by 녹두장군