구분 기호를 기준으로 텍스트 문자열을 여러 행으로 분할 - 3 빠른 팁

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

영상： 구분 기호를 기준으로 텍스트 문자열을 여러 행으로 분할하기

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

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

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

쉼표， 공백， 세미콜론， 슬래시 등 일반적인 구분 기호로 분리된 텍스트 문자열을 분할하려면 다음 코드를 사용하면 됩니다。 아래 단계를 따라주세요：

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

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

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

2. 열린 창에서 「삽입(Insert)」 > 「모듈(Module)」을 클릭하여 새 빈 모듈을 만듭니다。

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. 그런 다음 「OK」를 클릭하면 데이터를 분할할 구분 기호를 입력하라는 또 다른 메시지 상자가 나타납니다。 여기에서는 쉼표와 공백(， )을 입력했습니다。 스크린샷 참조：

3. 마지막으로 「OK」 버튼을 클릭합니다。 이제 선택한 텍스트 문자열이 쉼표를 기준으로 여러 행으로 분할되고 다른 관련 열의 데이터도 아래 스크린샷과 같이 반복되는 것을 확인할 수 있습니다：

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

셀 내용이 줄 바꿈으로 구분되어 있고 이를 여러 행으로 분할하려는 경우， 이를 도와줄 또 다른 VBA 코드가 있습니다。

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

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

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

2. 열린 창에서 「삽입(Insert)」 > 「모듈(Module)」을 클릭하여 새 빈 모듈을 만듭니다。

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. 그런 다음 「OK」 버튼을 클릭하면 선택한 셀의 데이터가 아래 스크린샷과 같이 여러 행으로 분할됩니다：

방법 B: Kutools for Excel 로 10 초 만에 구분 기호가 있는 텍스트를 여러 행으로 분할하기

「Kutools for Excel」을 설치한 경우， 「여러 줄로 분할」 기능을 사용해 지정한 구분 기호를 기준으로 텍스트 문자열을 여러 행으로 쉽게 분할할 수 있습니다。 다음 단계를 따라 진행하세요：

단계 1: 여러 줄로 분할 기능 선택하기

「Kutools」 > 「병합 및 분할(Merge & Split)」 > 「여러 줄로 분할」을 클릭하세요。 스크린샷 참조：

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

나타난 대화 상자에서 다음 옵션을 사용하세요：

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

이제 구분 기호가 포함된 선택한 셀이 지정한 구분 기호를 기준으로 여러 행으로 변환되었습니다。 스크린샷 참조：

팁: 원본 데이터를 복원하려면 Ctrl + Z 를 눌러 실행 취소(undo)만 하면 됩니다。

사용이 간편한가요？ 이 기능이 궁금하다면，30 일 무료 체험판을 다운로드하세요.

방법 C: Power Query 로 구분 기호가 있는 텍스트를 여러 행으로 분할하기

Office 365 또는 Excel 2016 이상 버전을 사용 중이라면 「Power Query」를 활용해 구분 기호가 있는 텍스트를 여러 행이나 열로 분할할 수 있습니다。 원본 데이터가 변경될 때마다 데이터 분할를 새로 고치고 싶을 때 유용합니다。 다음 단계를 따라 진행하세요：

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

1. 사용하려는 데이터 범위를 선택한 후 「데이터(Data)」 > 「테이블에서(From Table)」를 클릭하세요。 스크린샷 참조：

팁: Excel 2019 및 Office 365 에서는 데이터(Data) > 테이블/범위에서(From Table/Range)를 클릭하세요。

2. 나타난 「테이블 만들기(Create Table)」 대화 상자에서 「확인(OK)」 버튼을 클릭하여 테이블을 만드세요。 스크린샷 참조：

3. 이제 데이터와 함께 「Power Query 편집기(Power Query Editor)」 창이 표시됩니다。 스크린샷 참조：

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

1. 분할하려는 열을 선택한 후 「홈(Home)」 > 「열 분할(Split Column)」 > 「구분 기호별(By Delimiter)」를 클릭하세요。 스크린샷 참조：

2. 「구분 기호별 열 분할(Split Column by Delimiter)」 대화 상자에서：

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

• 1)。 「구분 기호 선택 또는 입력」 드롭다운 목록에서 데이터가 구분된 방식의 구분 기호를 선택합니다；
• 2)。 「분할 위치」 섹션에서 「구분 기호가 나타날 때마다」를 선택합니다；
• 3)。 그런 다음 「고급 옵션」를 클릭하여 섹션을 확장하고 「분할 대상」에서 「행」을 선택합니다；
• 4)。 마지막으로 이 대화 상자를 닫기 위해 「확인」을 클릭합니다。

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

• 1)。 「구분 기호 선택 또는 입력」 드롭다운 목록에서 「사용자 지정」을 선택합니다；
• 2)。 「특수 문자 삽입」 드롭다운에서 「줄 바꿈 기호」을 클릭하면 「사용자 지정」 아래 텍스트 상자에 자동으로 「#(lf)」 문자가 삽입됩니다；
• 3)。 「분할 위치」 섹션에서 「구분 기호가 나타날 때마다」를 선택합니다；
• 4)。 그런 다음 「고급 옵션」을 클릭하여 해당 섹션을 확장하고 「Split into」 아래에서 「Rows」를 선택하세요；
• 5)。 마지막으로 「OK」를 클릭하여 이 대화 상자를 닫으세요。

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

단계 3: Power Query 결과를 Excel 테이블로 내보내기

1. 그런 다음 결과 데이터를 워크시트로 내보내야 합니다。 「홈(Home)」 > 「닫기 및 로드(Close & Load)」 > 「닫기 및 로드 / 닫기 및 로드 위치 지정(Close & Load To)」을 클릭하세요。 (여기에서는 「닫기 및 로드(Close & Load)」를 클릭하겠습니다。) 스크린샷 참조：

팁： 「닫기 및 로드(Close & Load)」를 클릭하면 데이터가 새 워크시트로 출력됩니다。 「닫기 및 로드 위치 지정(Close & Load To)」 옵션을 클릭하면 데이터를 원하는 다른 시트로 출력할 수 있습니다。

2. 최종적으로 데이터가 새 워크시트로 로드됩니다。 스크린샷 참조：

팁： 원본 테이블의 데이터를 자주 업데이트해야 한다면 걱정하지 마세요。 결과 테이블을 마우스 오른쪽 버튼으로 클릭하고 「새로 고침(Refresh)」을 선택하면 새 결과가 동적으로 반영됩니다。

관련 문서：