Excel 매크로 (VBA) - 017. 텍스트 나누기 (공백, 특수문자)

전체 목차

 - 001. 특정 행을 복사하여 삽입하기

 - 002. 선택하여 붙여 넣기 기능 정리

 - 003. 변수/Assignment/Loop/비교

 - 004. 데이터가 있는 셀의 범위 알아내기

 - 005. 데이터 시트(쉘) 순회하기

 - 006. 셀 병합 하기

 - 007. 변수에 저장되어 있는 데이터 확인하기 (디버그 기능) 

 - 008. 정렬하기 (Sort)

 - 009. 버튼 컨트롤 (Excel Form vs. ActiveX)

 - 010. 콤보 상자 컨트롤 (Excel Form vs. ActiveX)

 - 011. 확인란/옵션단추 컨트롤(Excel Form vs. ActiveX)

 - 012. 스핀단추 컨트롤(스피너, Excel Form vs. ActiveX)

 - 013. 자동필터(Auto Filter)

 - 014. 함수(function) (전편)

 - 015. 함수(function) (후편)

 - 016. 중복 데이터 제거하기 

 

포스트 목차.

01. 엑셀에서의 텍스트 나누기

 1) 구분기호로 분리된 텍스트 나누기

  1-1) 연속된 구분기호를 하나로 처리하지 않는 경우

  1-2) 연속된 구분기호를 하나로 처리하는 경우

  1-3) 텍스트 한정자 사용

 2) 너비가 일정한 텍스트 나누기

02. VBA에서 텍스트 나누기

03. 예제 및 설명

 

이번 포스트에서는 엑셀에서 제공하는 텍스트 나누기 기능에 대해 알아보고, VBA를 통해 해당 기능을 수행하는 방법을 소개한다. 

 

중복 데이터 제거하기 편에 이어 오늘 사용할 데이터도 공공 데이터 포탈에서 구한 자료이며, 울산광역시 동구 실내공기질 측정 대상에 대한 정보이다.

https://eggdrop.tistory.com/30

Excel 매크로 (VBA) - 016. 중복 데이터 제거하기

 

 

 

01. 엑셀에서의 텍스트 나누기

 

1) 구분기호로 분리된 텍스트 나누기

 

텍스트 나누기는 하나의 셀에 뭉쳐있는 데이터를 여러개의 셀로 분리하는 작업을 의미한다.

 

영역 선택 > 데이터 > 텍스트 나누기로 수행할 수 있다.

 

텍스트 나누기를 실행하면 다음과 같이 텍스트 마법사 창이 뜬다.

 

 

1단계에서는 나눌 텍스트의 기준에 대해 정한다.

 (1) 구분 기호로 분리됨: 한글자의 기호를 기준으로 데이터를 구분함

 (2) 너비가 일정함: 일정한 패턴으로 같은 너비로 구성된 데이터의 경우 사용

 

테스트할 데이터는 콤마(,)로 구분되어 있는 것을 확인할 수 있다. 일단 구분기호로 분리됨을 선택 한 후 다음으로 넘어가자

 

다음을 클릭하면 2단계로 넘어간다.

 

다음을 누르면 구분 기호를 선택할 수 있는 메뉴가 나온다. 엑셀에서 기본적으로 탭, 세미콜론, 쉼표, 공백을 구분기호로 선택할 수 있으며, 기타 부분에 문자를 입력하면 해당 문자(1문자만)를 구분기호로 사용할 수 있다. 

 

연속된 구분 기호를 하나로 처리, 텍스트 한정자에 대해서는 따로 다루도록 하겠다. 

 

데이터 미리 보기를 보면 현재 테스트가 하나로 뭉쳐 있는 것을 확인할 수 있다.

구분 기호에서 쉼표를 선택해보자.

 

 

데이터 미리보기를 보면, 업체명, 전화번호, 주소가 분리되어 있는 것을 확인할 수 있다.

다음 버튼을 눌러서 3단계로 넘어가보자.

3단계에서는 위의 그림 처럼 각각의 열 데이터에 서식을 적용할 수 있다.  데이터 미리보기에서 열을 선택한 후 열 데이터 서식에 있는 데이터 서식을 지정하면 된다.  위의 그림은 업체명은 일반, 전화번호와 주소는 텍스트로 서식을 지정한 결과이다.

 

대상이라는 필드가 있는데 이 값은 텍스트 나누기를 한 결과가 표기될 영역의 첫 주소를 지정하는 것이다.  현재 A1에 데이터가 있기 때문에 A1으로 대상을 지정하면 기존 데이터는 지워지고 A1, B1, C1에 각각의 데이터가 채워질 것이다.

 

마침을 클릭하면 위의 그림과 같이 A1, B1, C1에 텍스트 나누기가 수행된 것을 확인할 수 있다. 

 

1-1) 연속된 구분기호를 하나로 처리하지 않는 경우

위의 데이터를 "/" 기호를 기준으로 구분하여 나누기를 수행해보자. 

연속된 구분 기호를 하나로 처리하지 않을 경우, 저자 필드가 비어 있는 것처럼 처리할 수 있다. 

 

1-2) 연속된 구분기호를 하나로 처리하는 경우

 

반면 연속된 구분 기호를 하나로 처리할 경우, 없는 필드만큼 앞으로 밀리게 된다. 

 

사용자의 의도에 맞게 선택하자.

 

1-3) 텍스트 한정자 사용

 

위의 데이터를 나누기한다고 생각해보자. 텍스트 한정자로 따옴표(")를 지정하면, 두개의 따옴표 사이에 존재하는 텍스트는 하나의 텍스트로 인식이 된다. 구분자(/)가 중간에 있어도 구분자로 인식하지 않는다.

 

만약 텍스트 한정자를 지정하지 않게 되면 따옴표도 하나의 문자로 취급하여 다음과 같이 텍스트가 나눠지게 된다.

 

 

2) 너비가 일정한 텍스트 나누기

텍스트 데이터가 일정한 너비로 작성되어 있는 경우, 구분기호 대신 너비를 기준으로 텍스트를 나누는 것이 가능하다.

 

다음과 같이 특정 데이터가 작성되어 있는 상황에서 텍스트 나누기를 실행해보자.

위의 그림 처럼 2단계에서 구분선을 이용하여 텍스트 데이터를 나누는 것이 가능하다.

3단계에서는 나눈 데이터열에 대한 서식을 지정하는 것이 가능하다.

 

위의 그림처럼 데이터가 여러 열로 나눠어지는 것을 확인할 수 있다. 

 

02. VBA에서 텍스트 나누기

위의 작업을 VBA에서 진행하려면 Range객체의 TextToColumns 메소드를 사용하면 된다.

 

좀 더 자세한 내용이 알고 싶다면 다음 링크를 참조하자.

https://docs.microsoft.com/en-us/office/vba/api/excel.range.texttocolumns

Range.TextToColumns method (Excel)

 

TextToColumns  메소드는 생략가능한 파라미터들을 다수 가지고 있다.  다음 파라미터들을 참고하여 사용해보자.

 

파라미터명	타입	설명
Destination	Variant	텍스트 마법사 3단계의 대상에 해당 (텍스트 나누기를 한 결과가 표기될 영역의 첫 주소)
DataType	열거형 XlTextParsingType	텍스트 마법사 1단계의 원본데이터 형식에 해당 xlDelimited: 구분기호로 분리됨 xlFixedWidth: 너비가 일정함
TextQualifier	열거형 XlTextQualifier	텍스트 마법사 2단계(구분기호)의 텍스트 한정자에 해당 xlTextQualifierDoubleQuote: 큰따옴표 xlTextQualifierSingleQuote: 작은따옴표 xlTextQualifierNone: 없음
ConsecutiveDelimiter	Variant	텍스트 마법사 2단계(구분기호)의 연속된 구분기호 하나로 처리에 해당 True: 하나로 처리 False: 구분기호 각각 처리
Tab	Variant	탭을 구분자로 사용 True: 사용, False: 미사용
Semicolon	Variant	세미콜론을 구분자로 사용 True: 사용, False: 미사용
Comma	Variant	쉼표를 구분자로 사용 True: 사용, False: 미사용
Space	Variant	공백문자를 구분자로 사용 True: 사용, False: 미사용
Other	Variant	위에서 언급한 특수기호외의 다른문자 사용 여부 True: 사용, False: 미사용
OtherChar	Variant	Other가 True 인 경우에 지정 필요 구분기호로 사용할 문자, 여러 문자 입력시 첫문자로 인식
FieldInfo	Variant	텍스트 마법사 3단계의 열 데이터 서식을 지정하기 위한 배열
DecimalSeparator	Variant	소수 구분 기호 아래 그림 참조
ThousandsSeparator	Variant	1000 단위 구분 기호 아래 그림 참조
TrailingMinusNumbers	Variant	음수일 경우 마이너스 표시여부 아래 그림 참조

텍스트 가져오기 고급설정 (마법사3단계에서 열 데이터 서식을 일반으로 선택 한 후 고급 버튼 클릭)에서 소수 구분 단위(DecimalSeparator), 1000 단위 구분기호(ThousandsSeparator), 음수일 경우 마이너스 표기 여부(TrailingMinusNumbers)를 결정할 수 있다. 

 

일반셀 중에서 엑셀이 숫자로 판단한 셀을 표기하기 위한 방법을 지정하는 파라미터이다. 

 

03. 예제 및 설명

 

A열에 있는 텍스트 데이터를 구분기호(/)를 이용해서 나누는 매크로를 작성해보자.

   - 구분기호: 슬래시 / 사용

   - 연속된 구분기호는 하나로 간주하지 않음

   - 나머지는 기본값을 사용

   - H1부터 나눈 텍스트를 표기

 

Sub textToColumns_slash()
'텍스트 나누기할 영역 저장을 위한 Range 변수 선언
Dim section As Range
'텍스트 나누기한 결과를 입력할 첫 셀 지정을 위한 변수 선언
Dim startSection As Range
'텍스트 나누기할 영역의 행 길이를 저장할 변수 선언
Dim rowNum As Long
'텍스트 나누기할 영역의 데이터가 몇행에 걸쳐 있는지 계산
rowNum = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
'데이터가 있는 영역 지정
Set section = ActiveSheet.Range(Cells(1, 1), Cells(rowNum, 1))
'텍스트 나누기한 결과가 저장될 영역 지정
Set startSection = ActiveSheet.Range("H1")
'데이터 영역 선택
section.Select
'선택한 영영에 대한 텍스트 나누기 실행, 구분기호(/)사용
section.TextToColumns Destination:=startSection, Other:=True, OtherChar:="/", DataType:=xlDelimited
 
End Sub
 

 

실행 결과는 다음과 같다.

 

의도한 대로 텍스트 나누기가 수행되는 것을 확인할 수 있다.