특정 구분 기호를 사용하여 텍스트 문자열을 여러 행으로 분할하기 - 3가지 빠른 방법

일반적으로 특정 구분 기호(예: 쉼표, 점, 세미콜론, 슬래시 등)를 사용하여 셀 내용을 여러 열로 분할하는 '텍스트 나누기' 기능을 사용할 수 있습니다. 하지만 때때로 구분된 셀 내용을 여러 행으로 분할하고 다른 열의 데이터를 반복해야 할 수도 있습니다(아래 스크린샷 참조). 이 작업을 Excel에서 처리하는 좋은 방법이 있을까요? 이 튜토리얼에서는 Excel에서 이 작업을 완료하는 몇 가지 효과적인 방법을 소개합니다.

비디오: 특정 구분 기호를 사용하여 텍스트 문자열을 여러 행으로 분할하기

방법 A: VBA 코드를 사용하여 구분된 텍스트를 여러 행으로 분할하기

이 섹션에서는 특정 구분 기호로 분리된 셀 내용을 분할하는 데 도움이 되는 두 가지 VBA 코드를 소개하겠습니다.

쉼표, 공백, 세미콜론 등으로 구분된 텍스트 분할하기

쉼표, 공백, 세미콜론, 슬래시 등과 같은 일반적인 구분 기호로 분리된 텍스트 문자열을 분할하려면 다음 코드를 사용할 수 있습니다. 아래 단계를 따르세요:

 참고: 이 코드는 실행 취소(Undo)를 지원하지 않으므로, 코드를 적용하기 전에 데이터를 백업하는 것이 좋습니다.

1단계: VBA 모듈 편집기를 열고 코드 복사하기

1. 사용하려는 시트를 활성화한 후 "Alt" + "F11" 키를 눌러 "Microsoft Visual Basic for Applications" 창을 엽니다.

2. 열린 창에서 "삽입" > "모듈"을 클릭하여 새 빈 모듈을 생성합니다.

3. 그런 다음 아래 코드를 빈 모듈에 복사하여 붙여넣습니다.

VBA 코드: 특정 구분 기호(쉼표, 점, 공백 등)로 텍스트 분할하기

Sub SplitTextIntoRows()
'UpdatebyExtendoffice
    Dim xSRg, xIptRg, xCrRg, xRg As Range
    Dim xSplitChar As String
    Dim xArr As Variant
    Dim xFNum, xFFNum, xRow, xColumn, xNum As Integer
    Dim xWSh As Worksheet
    Set xSRg = Application.InputBox("Select a range:", "Kutools for Excel", , , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xSplitChar = Application.InputBox("Type delimiter:", "Kutools for Excel", , , , , , 2)
    If xSplitChar = "" Then Exit Sub
    Application.ScreenUpdating = False
    xRow = xSRg.Row
    xColumn = xSRg.Column
    Set xWSh = xSRg.Worksheet
    For xFNum = xSRg.Rows.Count To 1 Step -1
        Set xRg = xWSh.Cells.Item(xRow + xFNum - 1, xColumn)
        xArr = Split(xRg, xSplitChar)
        xIndex = UBound(xArr)
        For xFFNum = LBound(xArr) To UBound(xArr)
            xRg.EntireRow.Copy
            xRg.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown
            xRg.Worksheet.Cells(xRow + xFNum, xColumn) = xArr(xIndex)
            xIndex = xIndex - 1
        Next
        xRg.EntireRow.Delete
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub   

2단계: 코드 실행하여 결과 얻기

1. 코드를 붙여넣은 후 "F5" 키를 눌러 코드를 실행합니다. 그러면 분할하려는 구분된 텍스트가 포함된 셀을 선택하라는 메시지 상자가 나타납니다. 스크린샷 참고:

2. 그런 다음 "확인"을 클릭하면 데이터를 분할할 구분 기호를 입력하라는 또 다른 메시지 상자가 나타납니다. 여기서 쉼표와 공백(, )을 입력합니다. 스크린샷 참고:

3. 마지막으로 "확인" 버튼을 클릭합니다. 이제 선택한 텍스트 문자열이 쉼표를 기준으로 행으로 분할되고, 관련 열 데이터는 아래 스크린샷과 같이 반복됩니다:

줄 바꿈으로 구분된 텍스트 분할하기

셀 내용이 줄 바꿈으로 구분되어 있다면 이를 여러 행으로 분할하려면 여기에 도움이 될 수 있는 또 다른 VBA 코드가 있습니다.

 참고: 이 코드는 실행 취소(Undo)를 지원하지 않으므로, 코드를 적용하기 전에 데이터를 백업하는 것이 좋습니다.

1단계: VBA 모듈 편집기를 열고 코드 복사하기

1. "Alt" + "F11" 키를 눌러 "Microsoft Visual Basic for Applications" 창을 엽니다.

2. 열린 창에서 "삽입" > "모듈"을 클릭하여 새 빈 모듈을 생성합니다.

3. 그런 다음 아래 코드를 빈 모듈에 복사하여 붙여넣습니다.

VBA 코드: 줄 바꿈으로 텍스트 분할하기

Sub SplitTextIntoRows()
'UpdatebyExtendoffice
    Dim xSRg, xIptRg, xCrRg, xRg As Range
    Dim xSplitChar As String
    Dim xArr As Variant
    Dim xFNum, xFFNum, xRow, xColumn, xNum As Integer
    Dim xWSh As Worksheet
    Set xSRg = Application.InputBox("Select a range:", "Kutools for Excel", , , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xSplitChar = Chr(10)
    Application.ScreenUpdating = False
    xRow = xSRg.Row
    xColumn = xSRg.Column
    Set xWSh = xSRg.Worksheet
    For xFNum = xSRg.Rows.Count To 1 Step -1
        Set xRg = xWSh.Cells.Item(xRow + xFNum - 1, xColumn)
        xArr = Split(xRg, xSplitChar)
        xIndex = UBound(xArr)
        For xFFNum = LBound(xArr) To UBound(xArr)
            xRg.EntireRow.Copy
            xRg.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown
            xRg.Worksheet.Cells(xRow + xFNum, xColumn) = xArr(xIndex)
            xIndex = xIndex - 1
        Next
        xRg.EntireRow.Delete
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub 

2단계: 코드 실행하여 결과 얻기

1. 코드를 붙여넣은 후 "F5" 키를 눌러 코드를 실행합니다. 팝업된 상자에서 분할하려는 셀을 선택하세요. 스크린샷 참고:

2. 그런 다음 "확인" 버튼을 클릭하면 선택한 셀의 데이터가 아래 스크린샷과 같이 행으로 분할됩니다:

방법 B: Kutools for Excel을 사용하여 10초 만에 구분된 텍스트를 여러 행으로 분할하기

"Kutools for Excel"을 설치했다면 "Split Data to Rows" 기능을 사용하여 지정한 구분 기호로 텍스트 문자열을 여러 행으로 분할할 수 있습니다. 다음 단계를 따라 진행하세요:

1단계: Split Data to Rows 기능 선택하기

"Kutools" > "병합 및 분할" > "Split Data to Rows"를 클릭하세요. 스크린샷 참고:

2단계: 분할할 데이터 셀 및 구분 기호 지정하기

팝업된 대화 상자에서 다음 옵션을 사용하세요:

• 1). "범위 (단일 열)" 텍스트 상자에서 분할하려는 셀 목록을 선택합니다;
• 2). 그런 다음 데이터를 구분하는 구분 기호를 선택합니다. 여기서는 "기타"를 선택하고 쉼표와 공백(, )을 텍스트 상자에 입력합니다;
• 3). 마지막으로 "확인" 버튼을 클릭합니다.

이제 선택한 셀의 구분된 텍스트 문자열이 특정 구분 기호를 기준으로 여러 행으로 변환되었습니다. 스크린샷 참고:

 팁: 원래 데이터를 복원하려면 Ctrl + Z를 눌러 실행 취소하면 됩니다.

사용하기 쉽나요? 이 기능에 관심이 있다면 30일 무료 체험판을 다운로드해 보세요.

방법 C: Power Query를 사용하여 구분된 텍스트를 여러 행으로 분할하기

Office 365 또는 Excel 2016 이상 버전을 사용 중이라면 "Power Query"는 구분된 텍스트를 여러 행이나 열로 분할하는 강력한 도구입니다. 원본 데이터가 변경될 때 분할된 데이터를 새로 고칠 수 있어 유용합니다. 다음 단계를 따라 작업을 완료하세요:

1단계: 데이터 테이블을 Power Query로 가져오기

1. 사용하려는 데이터 범위를 선택한 후 "데이터" > "테이블로부터"를 클릭합니다. 스크린샷 참고:

 팁: Excel 2019 및 Office 365에서는 데이터 > 테이블/범위로부터 클릭하세요.

2. 팝업된 "테이블 생성" 대화 상자에서 "확인" 버튼을 클릭하여 테이블을 생성합니다. 스크린샷 참고:

3. 이제 "Power Query 편집기" 창에 데이터가 표시됩니다. 스크린샷 참고:

2단계: Power Query에서 변환 작업 수행하기

1. 분할하려는 열을 선택한 후 "홈" > "열 분할" > "구분 기호별로"를 클릭합니다. 스크린샷 참고:

2. "구분 기호별로 열 분할" 대화 상자에서:

쉼표, 공백, 세미콜론 등으로 텍스트 문자열을 분할하려면 다음과 같이 하세요:

• 1). "구분 기호 선택 또는 입력" 드롭다운 목록에서 데이터를 구분하는 구분 기호를 선택합니다;
• 2). "분할 위치" 섹션에서 "구분 기호의 각 발생 위치"를 선택합니다;
• 3). 그런 다음 "고급 옵션"을 클릭하여 섹션을 확장하고 "분할 대상"에서 "행"을 선택합니다;
• 4). 마지막으로 "확인"을 클릭하여 대화 상자를 닫습니다.

줄 바꿈으로 텍스트 문자열을 여러 행으로 분할하려면 다음과 같이 하세요:

• 1). "구분 기호 선택 또는 입력" 드롭다운 목록에서 "사용자 정의"를 선택합니다;
• 2). "특수 문자 삽입" 드롭다운에서 "줄 바꿈"을 클릭하면 "#(lf)" 문자가 "사용자 정의" 아래의 텍스트 상자에 자동으로 삽입됩니다;
• 3). "분할 위치" 섹션에서 "구분 기호의 각 발생 위치"를 선택합니다;
• 4). 그런 다음 "고급 옵션"을 클릭하여 섹션을 확장하고 "분할 대상"에서 "행"을 선택합니다;
• 5). 마지막으로 "확인"을 클릭하여 대화 상자를 닫습니다.

3. 이제 선택한 데이터가 아래 스크린샷과 같이 여러 행으로 분할되었습니다:

3단계: Power Query를 Excel 테이블로 출력하기

1. 그리고 데이터를 워크시트로 출력해야 합니다. "홈" > "닫기 및 로드" > "닫기 및 로드 / 닫기 및 로드 대상"을 클릭하세요(여기서는 "닫기 및 로드"를 클릭합니다). 스크린샷 참고:

 팁: "닫기 및 로드"를 클릭하면 데이터가 새 워크시트로 출력됩니다. "닫기 및 로드 대상" 옵션을 클릭하면 데이터가 필요한 다른 시트로 출력됩니다.

2. 마지막으로 데이터가 새 워크시트로 로드됩니다. 스크린샷 참고:

 팁: 원본 테이블의 데이터를 자주 업데이트해야 한다면 걱정하지 마세요. 결과 테이블을 마우스 오른쪽 버튼으로 클릭하고 "새로 고침"을 클릭하면 동적으로 새로운 결과를 얻을 수 있습니다.

관련 기사: