VBA Split 함수 – 텍스트 문자열을 배열로 분할하기

VBA Split 함수

VBA 분할 함수는 특정 구분 문자(예: 쉼표, 공백 또는 콜론 등)를 기준으로 텍스트 문자열을 하위 문자열로 분할합니다. 코드를 직접 작성해서 문자열에서 구분 기호를 검색하고 각각의 값을 추출하는 것보다 Split 함수를 사용하는 것이 더 간편합니다.

쉼표로 구분된 값(CSV 파일)에서 개별 항목을 표시하거나 한 줄에 모두 있는 메일 주소를 각각 구분하여 여러 줄로 표시하려는 경우에 이 방법을 사용할 수 있습니다.

구문은 다음과 같습니다:

Split expression, delimiter[선택사항], limit[선택사항], compare[선택사항]

VBA Split 함수에는 네 가지 매개 변수가 있습니다:

• Expression – 여러 부분으로 분할하려는 텍스트 문자열입니다.
• Delimiter (선택사항)– 문자열 또는 표시할수 없는 문자(예: 탭) – 분할에 사용할 구분 문자를 정의합니다. 구분 문자를 지정하지 않으면 기본값인 공백이 사용됩니다.
• Limit (선택사항) – 숫자 – 분할 횟수를 정의합니다. 비워두면 사용 가능한 모든 분할이 문자열 내에서 이루어집니다. 1로 설정하면 분할이 수행되지 않습니다. 기본적으로 문자열이 매우 길어서 처음 세 개의 분할만 필요한 경우와 같이 문자열의 시작 부분부터 특정 개수로 분리할 수 있습니다.
• Compare (선택사항) – 구분 기호가 텍스트 문자인 경우 구분 기호의 대소문자 구분 여부를 설정하는데 사용됩니다. 값은 대/소문자를 구분하는 vbBinaryCompare와 대/소문자를 구분하지 않는 vbTextCompare입니다.

Split 함수는 항상 배열을 반환합니다.

Split 함수의 간단한 예제

Sub SplitExample()
'변수들을 정의합니다
Dim MyArray() As String, MyString As String, I As Variant
'공백으로 구분된 샘플 문자열입니다
MyString = "하나 둘 셋 넷"
'Split 함수를 사용하여 문자열의 구성요소를 분할합니다
MyArray = Split(MyString)
'배열의 항목들을 반복하여 각 값을 표시합니다
For Each I In MyArray
    MsgBox I
Next I
End Sub

이 예제에서는 모든 단어 사이에 공백이 있습니다. 구분 기호가 지정되지 않았으므로 기본으로 설정된 구분 기호(공백)가 사용되었습니다.

생성된 배열은 단일 배열이며 데이터 유형은 문자열로 설정됩니다. For…Next 루프에서 사용되는 변수 I는 Variant로 데이터 유형이 지정되어야 합니다.

이 코드가 실행되면 네 개의 메시지 박스가 표시되며, 분할된 각 항목들이 하나씩 표시됩니다(이 예제에서는 하나, 둘, 셋, 넷 의 값이 각각 메세지 박스에 하나씩 표시됩니다).

문자열의 단어 사이에 공백이 두 번 있는 경우 아무 내용이 없어도 구분된다는 점에 유의하세요. 이는 원하는 결과가 아닐 수도 있습니다.

Replace 함수를 사용하여 이중 공백을 단일 공백으로 바꾸면 이 문제를 해결할 수 있습니다:

MyString = Replace(MyString, "  ", " ")

드문 경우이긴 하지만 문자열의 맨 앞이나 맨 뒤에 있는 공백도 빈 분할을 생성하여 문제를 일으킬 수 있습니다. Trim 함수를 사용하여 이러한 불필요한 공백을 제거할 수 있습니다:

MyString = Trim(MyString)

구분 문자로 Split 함수 사용하기

예를들면 세미콜론(;) 구분 기호를 사용할 수 있습니다. 이는 이메일 주소 여러개 있는 문자열에서 주소를 구분하기 위해 자주 사용됩니다. 받는사람이 여러명으로 지정된 이메일을 받았을때 받는사람 목록을 워크시트에 나열하고 싶은 경우가 있습니다. 이메일의 ‘받는사람’ 이나 ‘참조’ 박스에서 이메일 주소를 코드에 쉽게 복사할 수 있습니다.

Sub SplitBySemicolonExample()
'변수들을 정의합니다
Dim MyArray() As String, MyString As String, I As Variant, N As Integer
'세미콜론으로 구분된 문자열 샘플을 정의합니다
MyString = "john@myco.com;jane@myco.com;bill@myco.com;james@myco.com"
'Split 함수를 사용하여 문자열을 분할합니다
MyArray = Split(MyString, ";")
'워크시트의 사용영역을 초기화합니다
ActiveSheet.UsedRange.Clear
'배열을 반복합니다
For N = 0 To UBound(MyArray)
'워크시트의 첫 번째열에 이메일 주소를 입력합니다
    Range("A" & N + 1).Value = MyArray(N)
Next N
End Sub

For…Next 루프는 배열을 반복하는 데 사용됩니다. 배열의 첫 번째 요소는 항상 0에서 시작하며, Upper Bound 함수는 마지막 항목의 인덱스를 구하는 데 사용됩니다.

위 코드를 실행하면 워크시트에 다음과 같이 표시됩니다:

Split 함수에서 Limit 파라미터 사용하기

Limit 매개 변수를 사용하면 문자열의 시작부터 특정 횟수만큼의 분할을 수행할 수 있습니다. Split 함수에서는 시작 위치나 분할 범위는 지정할 수 없지만. 이 작업을 수행하는 함수를 직접 만들 수 있습니다. 이에 대해서는 이 글의 뒷부분에서 설명합니다.

Sub SplitWithLimitExample()
'변수를 생성합니다
Dim MyArray() As String, MyString As String, I As Variant, N As Integer
'쉼표 구분 기호로 문자열 샘플 만듭니다
MyString = "One,Two,Three,Four,Five,Six"
'Split 함수를 사용하여 문자열의 구성 요소를 분할합니다.
MyArray = Split(MyString, ",", 4)
'워크시트의 사용 영역을 초기화합니다
ActiveSheet.UsedRange.Clear
'배열을 반복합니다.
For N = 0 To UBound(MyArray)
    '워크시트의 첫 번째열에 분할된 각 항목을 입력합니다
    Range("A" & N + 1).Value = MyArray(N)
Next N
End Sub

위 코드를 실행하면 워크시트에 다음과 같이 표시됩니다:

처음 세 개의 값만 각 행에 표시됩니다. 이후 세개의 값은 하나의 긴 문자열로 표시되며 분할되지 않습니다.

문자열 내의 구분 기호 개수보다 큰 값을 Limit 매개변수의 값으로 설정해도 오류가 발생하지 않습니다. Limit 값을 지정하지 않은 것처럼 문자열이 구분 기호로 분리된 만큼 분할됩니다.

 

Split 함수에서 Compare 매개변수 사용하기

Compare 매개변수는 구분 기호에 대소문자를 구분할지 여부를 결정합니다. 구분 기호가 쉼표, 세미콜론 또는 콜론인 경우에는 이 매개변수가 적용되지 않습니다.

참고: 대안으로, 모듈 상단에 Option Compare Text를 입력하여 전체 모듈의 대소문자 구분을 옵션을 제거할 수 있습니다.

Sub SplitByCompareExample()
'변수를 생성합니다
Dim MyArray() As String, MyString As String, I As Variant, N As Integer
'X 문자열로 구분된 문자열 샘플 만듭니다
MyString = "OneXTwoXThreexFourXFivexSix"
'Split 함수를 사용하여 문자열의 구성 요소를 분할합니다.
MyArray = Split(MyString, "X", , vbBinaryCompare)
'워크시트의 사용 영역을 초기화합니다
ActiveSheet.UsedRange.Clear
'배열을 반복합니다.
For N = 0 To UBound(MyArray)
    '워크시트의 첫 번째열에 분할된 각 항목을 입력합니다
    Range("A" & N + 1).Value = MyArray(N)
Next N
End Sub

이 예제에서는  ‘X’ 문자를 구분 기호로 사용합니다. 이 문자열에는 대문자와 소문자 ‘X’가 혼합되어 있습니다. Split 함수의 Compare 매개 변수는 대문자 ‘X’를 사용하도록 설정되어있습니다.

Compare 매개 변수가 vbBinaryCompare로 설정된 경우 소문자 ‘x’ 문자는 무시되고 워크시트에는 다음과 같이 표시됩니다:

Compare 매개 변수가 vbTextCompare로 설정된 경우 소문자 ‘x’도 구분 기호로 사용되며 워크시트는 다음과 같이 표시됩니다:

A6셀의 값은 소문자 ‘x’를 포함하므로 잘립니다. 분할은 대소문자를 구분하지 않으므로 하위 문자열의 일부를 구성하는 모든 구분기호는 문자열을 구분하는데 사용됩니다.

이것은 텍스트 구분 기호와 vbTextCompare를 사용할 때 염두에 두어야 할 중요한 사항입니다. 잘못 사용하는 경우가 많으므로 주의가 필요합니다.

표시할수 없는 문자를 구분 기호로 사용하기

캐리지 리턴(줄 바꿈)과 같이 표시할 수 없는 문자를 구분 기호로 사용할 수 있습니다.

여기서는 vbCr을 사용하여 캐리지 리턴을 추가합니다.

Sub SplitByNonPrintableExample()
'변수를 생성합니다
Dim MyArray() As String, MyString As String, I As Variant, N As Integer
'캐리지리턴으로 구분된 문자열 샘플 만듭니다
MyString = "One" & vbCr & "Two" & vbCr & "Three" & vbCr & "Four" & vbCr & "Five" & vbCr & "Six"
'Split 함수를 사용하여 문자열의 구성 요소를 분할합니다.
MyArray = Split(MyString, vbCr, , vbTextCompare)
'워크시트의 사용 영역을 초기화합니다
ActiveSheet.UsedRange.Clear
'배열을 반복합니다.
For N = 0 To UBound(MyArray)
    '워크시트의 첫 번째열에 분할된 각 항목을 입력합니다
    Range("A" & N + 1).Value = MyArray(N)
Next N
End Sub

이 예제에서는 구분 기호로 vbCr(캐리지 리턴 문자)을 사용하여 문자열을 구분했습니다.

위 코드를 실행하면 워크시트에 다음과 같이 표시됩니다:

Join 함수를 사용하여 Split 문자열 되돌리기

Join 함수는 배열의 모든 요소를 지정된 구분 기호를 사용하여 합칩니다. 구분 문자를 지정하지 않으면 공백이 사용됩니다.

Sub JoinExample()
'변수를 생성합니다
Dim MyArray() As String, MyString As String, I As Variant, N As Integer
Dim Target As String
'콤마로 구분된 문자열 샘플 만듭니다
MyString = "One,Two,Three,Four,Five,Six"
'MyString 변수의 값을 A1 셀에 입력합니다
Range("A1").Value = MyString
'Split 함수를 사용하여 문자열의 구성 요소를 분할합니다.
MyArray = Split(MyString, ",")
'Join 함수를 사용하여 세미콜론 구분 기호를 사용하여 원래 문자열을 다시 만듭니다.
Target = Join(MyArray,”;”)
'A2셀에 만들어진 문자열을 입력합니다
Range("A2").Value = Target
End Sub

이 코드는 쉼표 구분 기호로 문자열을 배열로 분할한 다음 세미콜론 구분 기호를 사용하여 다시 합칩니다.

위 코드를 실행하면 워크시트에 다음과 같이 표시됩니다:

A1 셀의 값은 쉼표 구분 기호가 있는 원래 문자열이고, A2 셀의 값은 구분 기호로 세미콜론을 사용한 새로 합쳐진 문자열입니다.

 

Split 함수 사용하여 단어 개수 계산하기

Excel VBA에서 문자열 변수의 길이는 최대 2Gb까지 가능하다는 점을 유의해야합니다. 이 점을 고려하여 Split 함수를 사용하여 텍스트에서 단어 수를 계산할 수 있습니다. 물론 Microsoft Word에서 이 기능을 자동으로 수행하지만 간단한 텍스트 파일이나 다른 애플리케이션에서 복사한 텍스트의 단어 수를 계산할때 Excel VBA에서 Split 함수가 유용할 수 있습니다.

Sub NumberOfWordsExample()
'변수를 생성합니다
Dim MyArray() As String, MyString As String
'공백으로 구분된 문자열 샘플 만듭니다
MyString = "One Two Three Four Five Six"
'이중 공백을 제거합니다
MyString = Replace(MyString, "  ", " ")
'문자열의 맨 앞과 맨 뒤에있는 공백을 제거합니다
MyString = Trim(MyString)
'Split 함수를 사용하여 문자열의 구성 요소를 분할합니다
MyArray = Split(MyString)
'UBound 함수를 사용하여 단어 수를 표시합니다.
MsgBox "Number of Words " & UBound(MyArray) + 1
End Sub

단어 수를 계산하는 코드의 잠재적인 위험성은 이중 공백과 문자열의 맨 앞과 맨 뒤에있는 공백으로 인해 단어 수가 증가할 수 있다는 점입니다. 이러한 공백이 있으면 추가 단어로 계산되어 단어 수가 부정확하게 계산됩니다.

위 코드에서는 Replace와 Trim 함수를 사용하여 단어 수와 상관없는 공백을 제거합니다.

마지막 줄에는 UBound 함수를 사용하여 배열의 마지막 인덱스 번호를 구한 다음 1을 더해서 찾은 단어의 개수를 표시합니다. 첫 번째 배열 인덱스가 0에서 시작하기 때문에 1을 더해줘야합니다.

 

주소를 워크시트 셀로 분할하기

주소는 쉼표 구분 기호로 구성된 긴 텍스트 문자열인 경우가 많습니다. 주소의 각 부분을 별도의 셀로 분할하는 것이 보기에 좋습니다.

Sub AddressExample()
'변수를 생성합니다
Dim MyArray() As String, MyString As String, N As Integer
'Microsoft Corporation의 주소를 문자열로 생성합니다
MyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"
'Split 함수를 사용하여 문자열을 쉼표 구분기호로 분할합니다
MyArray = Split(MyString, ",")
'워크시트의 사용 영역을 초기화합니다
ActiveSheet.UsedRange.Clear
'배열을 반복합니다
For N = 0 To UBound(MyArray)
     '워크시트의 첫 번째열에 분할된 각 항목을 입력합니다
    Range("A" & N + 1).Value = MyArray(N)
Next N
End Sub

위 코드를 실행하면 쉼표 구분 기호를 사용하여 주소의 분할된 부분을 각각의 셀에 입력합니다:

우편 번호(마지막 배열 요소)만 반환하려는 경우 아래 코드를 참고해 주세요:

Sub AddressZipCodeExample()
'변수를 생성합니다
Dim MyArray() As String, MyString As String, N As Integer, Temp As String
'Microsoft Corporation의 주소를 문자열로 생성합니다
MyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"
'Split 함수를 사용하여 문자열을 쉼표 구분기호로 분할합니다
MyArray = Split(MyString, ",")
'워크시트의 사용 영역을 초기화합니다
ActiveSheet.UsedRange.Clear
'우편번호(배열의 마지막 인덱스에 위치한 값)를 A1셀에 입력합니다
Range("A1").Value = MyArray(UBound(MyArray))
End Sub

위 코드에서는 UBound 함수로 배열의 마지막 요소만 사용하였습니다.

어떤 경우에는 주소 라벨로 인쇄할 수 있도록 하나의 셀에 모든 주소를 표시하고 싶을 수도 있습니다:

Sub AddressExample()
'변수를 생성합니다
Dim MyArray() As String, MyString As String, N As Integer, Temp As String
'Microsoft Corporation의 주소를 문자열로 생성합니다
MyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"
'Split 함수를 사용하여 문자열을 쉼표 구분기호로 분할합니다
MyArray = Split(MyString, ",")
'워크시트의 사용 영역을 초기화합니다
ActiveSheet.UsedRange.Clear
'배열을 반복합니다
For N = 0 To UBound(MyArray)
     '줄바꿈 문자를 이용해서 배열의 각 요소를 줄을 바꿔서 합칩니다.
    Temp = Temp & MyArray(N) & vbLf
Next N
'워크시트에 합쳐진 문자열을 입력합니다
Range("A1") = Temp
End Sub

이 예제는 모든 배열 요소를 합치기 위해임시 문자열을 생성하고 줄바꿈 문자를 사용한다는 점을 제외하면 앞의 예제와 동일한 방식으로 작동합니다.

위 코드를 실행하면 워크시트에 다음과 같이 표시됩니다:

문자열을 워크시트 셀에 분할하기

하나의 명령어로 분할된 배열을 워크시트 셀에 복사할 수 있습니다:

Sub CopyToRange()
'변수를 생성합니다
Dim MyArray() As String, MyString As String
'공백으로 구분된 문자열 샘플을 만듭니다
MyString = "One,Two,Three,Four,Five,Six"
'Split 함수를 사용하여 문자열 요소를 분할합니다
MyArray = Split(MyString, ",")
'배열을 워크시트로 복사합니다
Range("A1:A" & UBound(MyArray) + 1).Value = WorksheetFunction.Transpose(MyArray)
End Sub

위 코드를 실행하면 워크시트에 다음과 같이 표시됩니다:

사용자가 지정한 지점에서 문자열을 분할하는 새로운 함수 만들기

분할 함수의 Limit 매개 변수를 사용하면 분할을 중지할 위치만 지정할 수 있습니다. 이 함수는 항상 문자열의 시작 부분 부터 시작합니다.

문자열 내에서 분할의 시작 지점과 그 지점부터 표시할 분할 횟수를 지정할 수 있는 함수가 있으면 매우 유용할 것입니다. 또한 기존 Split 함수는 배열의 마지막 요소로 구분기호로 연결된 긴 문자열 값을 갖지만 새로운 함수에서는 사용자가 지정한 분할만 배열로 추출합니다.

이 작업을 수행하는 함수(SplitSlicer)를 VBA에서 직접 쉽게 작성할 수 있습니다:

Function SplitSlicer(Target As String, Del As String, Start As Integer, N As Integer)
'변수를 생성합니다
Dim MyArray() As String
'구분문자와 start 변수를 사용하여 문자열을 분할합니다
MyArray = Split(Target, Del, Start)
‘Start 매개변수가 분할된 배열의 수보다 큰지 확인합니다 – 클 경우 문제가 발생할 수 있습니다
If Start > UBound(MyArray) + 1 Then
	‘오류를 표시하고 함수를 종료합니다
    MsgBox "Start 매개변수가 최대 분할 가능 숫자보다 큽니다"
    SplitSlicer = MyArray
    Exit Function
End If
'마지막 배열 요소를 변수에 저장합니다
Target = MyArray(UBound(MyArray))
    '문자열을 Limit 파라미터(N)만큼 분할합니다
 MyArray = Split(Target, Del, N)
‘마지막 요소를 제거하기 위해 배열의 최대 인덱스가 0보다 큰지 확인합니다
If UBound(MyArray) > 0 Then
'ReDim 문을 사용하여 배열의 마지막 요소를 삭제합니다
ReDim Preserve MyArray(UBound(MyArray) - 1)
End If
'새로운 배열을 반환합니다
SplitSlicer = MyArray
End Function

이 함수는 네 개의 매개변수로 구성됩니다:

• Target – 문자열 – 분할하려는 문자열입니다.
• Del – 문자열 또는 표시할 수 없는 문자 – 쉼표, 콜론과 같이 사용하는 구분 기호 문자입니다.
• Start – 숫자 – 분할을 시작할 인덱스(위치)입니다.
• N – 숫자 – 분할 횟수입니다.

이 매개변수들은 선택적이거나 기본값이 설정되어 있지 않습니다. 하지만 코드를 추가하여 이러한 값들을 설정할 수 있습니다.

이 함수는 Split 함수를 사용하여 Start 매개변수를 Limit으로 사용하여 배열을 만듭니다. 즉, 배열 요소는 시작 매개변수까지 분할을 유지하지만 문자열의 나머지 부분은 마지막 요소가 되어 분할되지 않습니다.

배열의 마지막 요소는 UBound 함수를 사용하여 문자열로 다시 저장됩니다.

그런 다음 N을 제한 변수로 사용하여 문자열을 배열로 다시 분할합니다. 즉, 문자열에 대해 N 위치까지 분할이 수행되고 그 이후 문자열의 나머지 부분이 배열의 마지막 요소가 됩니다.

배열에 특정 요소만 남기고 싶기 때문에 마지막 요소를 제거하는 데 ReDim 문이 사용됩니다. Preserve 매개변수가 사용되지 않으면 배열의 모든 데이터가 손실된다는 점에 유의하세요.

마지막으로 새로 생성된 배열이 호출된 코드로 반환됩니다.

이 코드는 ‘오류 처리’가 되어 있다는 점에 주목해 주세요. 사용자는 코드를 만들때 고려하지 못했던 행동을 할 수 있습니다. 예를 들어, 문자열에서 사용 가능한 분할 수보다 큰 Start 또는 N 매개 변수로 함수를 사용하려고 하면 함수가 제대로 실행되지 못할 수 있습니다.

이를 방지하기 위해 Start 값을 확인하는 코드가 포함되어 있으며, 배열에서 ReDim 문을 사용할 때 제거할 수 있는 요소가 있는지 확인하는 코드도 포함되어 있습니다.

다음은 함수를 테스트하는 코드입니다:

Sub TestSplitSlicer()
'변수를 생성합니다
Dim MyArray() As String, MyString As String
'콤마로 구분된 문자열 샘플을 만듭니다
MyString = "One,Two,Three,Four,Five,Six,Seven,Eight,Nine,Ten"
'Splitslicer 함수를 사용하여 새로운 배열을 정의합니다
MyArray = SplitSlicer(MyString, ",", 4, 3)
'워크시트의 사용 영역을 초기화합니다
ActiveSheet.UsedRange.Clear
'배열을 워크시트로 복사합니다
Range("A1:A" & UBound(MyArray) + 1).Value = WorksheetFunction.Transpose(MyArray)
End Sub

위 코드를 실행하면 워크시트에 다음과 같이 표시됩니다: