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

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

 

 

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

 

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

 

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

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 VBA 정규식으로 한글, 일어, 한자 추출하기 ▶ 엑셀 VBA 개체, 속성, 함수, 이벤트 사용법과 사례 검색해서 참고하기 ▶ 엑셀 VBA 변수 범위 종류와 설정 방법 ▶ 엑셀 VBA 메시지박스 (MsgBox) 에 문자열 줄 바꿈을 적용하는 방법 ▶ 엑셀 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() 함수를 사용한 것과는 확연히 차이가 나죠. 공백은 모두 제거했습니다.

 

¤ 정규식 함수와 속성

 

▼ 함수는 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

 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 VBA 정규식으로 한글, 일어, 한자 추출하기 ▶ 엑셀 VBA 개체, 속성, 함수, 이벤트 사용법과 사례 검색해서 참고하기 ▶ 엑셀 VBA 변수 범위 종류와 설정 방법 ▶ 엑셀 VBA 메시지박스 (MsgBox) 에 문자열 줄 바꿈을 적용하는 방법 ▶ 엑셀 VBA 메모의 모양을 직사각형, 풍선, 다이어몬드 형태로 만드는 방법