Excel에서 셀 분할 (자세한 단계가 포함된 전체 가이드)

Excel에서 셀 데이터를 분할해야 하는 다양한 이유가 있습니다. 예를 들어, 원시 데이터에 전체 이름이나 주소와 같이 여러 정보가 하나의 셀에 묶여 있을 수 있습니다. 이러한 셀을 분할하면 서로 다른 유형의 정보를 분리할 수 있어 데이터를 더 쉽게 정리하고 분석할 수 있습니다. 이 기사는 특정 구분 기호를 기준으로 셀을 행 또는 열로 분할하는 다양한 방법을 보여주는 포괄적인 가이드 역할을 할 것입니다.

Excel에서 셀을 여러 열로 분할

• 텍스트를 열로 변환 마법사와 함께
• Kutools로 쉽게
• 플래시 채우기 사용 (Excel2013 및 이후 버전)
• 수식을 사용하여

Excel에서 셀을 여러 행으로 분할

• TEXTSPLIT 함수 사용 (Excel365)
• Kutools로 쉽게
• VBA 코드 사용
• Power Query (Excel2016 및 이후 버전) 사용

비디오

Excel에서 셀을 여러 열로 분할

다음 스크린샷과 같이 전체 이름 목록이 있다고 가정하고, 각 전체 이름을 별도의 이름과 성으로 분할하여 분할된 데이터를 별도의 열에 배치하고자 합니다. 이 섹션에서는 이 작업을 수행하는 데 도움이 되는 네 가지 방법을 보여드리겠습니다.

셀을 텍스트를 열로 마법사를 사용하여 여러 열로 분할

특정 구분 기호를 기준으로 셀을 여러 열로 분할하려면, Excel에서 자주 사용하는 방법 중 하나는 텍스트를 열로 변환 마법사를 사용하는 것입니다. 여기서는 원하는 결과를 얻기 위해 이 마법사를 단계별로 사용하는 방법을 보여드리겠습니다.

단계1: 분할하려는 셀을 선택하고 데이터 분할 마법사를 엽니다

이 경우, 전체 이름이 포함된 범위 A2:A8을 선택합니다. 그런 다음 데이터 탭으로 이동하여 텍스트를 열로 변환을 클릭하여 텍스트를 열로 변환 마법사를 엽니다.

단계2: 마법사에서 단계를 하나씩 구성합니다

1. 다음에서 3단계 중1단계 마법사, 선택 구분 기호 옵션을 선택한 다음 클릭하십시오 다음 버튼입니다.
   

   

2. 다음에서 3단계 중2단계 마법사에서 데이터의 구분 기호를 선택한 후 클릭하세요 다음 계속 버튼입니다.
   
   이 경우, 전체 이름을 공백을 기준으로 이름과 성으로 분할해야 하므로, 구분 기호 섹션에서 공백 체크박스만 선택합니다.

   

   참고:

   • 필요한 구분 기호가 이 섹션에 표시되지 않으면, '기타' 체크박스를 선택하고 텍스트 상자에 직접 구분 기호를 입력할 수 있습니다.
   • 셀을 줄 바꿈으로 분할하려면, 선택할 수 있습니다. 기타 체크박스를 선택하고 누르세요 Ctrl + J 키를 함께 누르세요.
     

     

3. 마지막 마법사에서 다음과 같이 구성해야 합니다:
   
   1) '목적지' 상자에서 분할 데이터를 배치할 셀을 선택합니다. 여기서는 C2 셀을 선택합니다.

   2) 완료 버튼을 클릭합니다.

   

결과

선택된 셀의 전체 이름이 이름과 성으로 분리되어 다른 열에 위치합니다.

Kutools를 사용하여 셀을 여러 열로 편리하게 분할하기

보시다시피, 텍스트를 열로 변환 마법사는 작업을 완료하기 위해 여러 단계를 필요로 합니다. 더 간단한 방법이 필요하다면, Kutools for Excel의 셀 분할 기능을 강력히 추천합니다. 이 기능을 사용하면 특정 구분 기호를 기준으로 셀을 여러 열이나 행으로 편리하게 분할할 수 있으며, 단일 대화 상자에서 옵션을 완료할 수 있습니다.

Kutools for Excel을 설치한 후, Kutools > 병합 & 셀 분할 > 셀 분할을 선택하여 셀 분할 대화 상자를 엽니다.

1. 분할하려는 텍스트가 포함된 셀의 범위를 선택하세요.
2. 데이터 분할 옵션을 선택하세요.
3. 선택 공백 (또는 필요한 구분 기호)를 선택하고 클릭하세요 확인.
   

   

4. 목적지 셀을 선택하고 클릭하세요 확인 모든 데이터 분할을 가져오기 위해.
   

플래시 채우기를 사용하여 셀을 여러 열로 분할

이제 세 번째 방법인 Flash Fill로 넘어가겠습니다. Excel2013에 도입된 Flash Fill은 패턴을 감지하면 데이터를 자동으로 채우도록 설계되었습니다. 이 섹션에서는 Flash Fill 기능을 사용하여 한 열에서 전체 이름에서 이름과 성을 분리하는 방법을 보여드리겠습니다.

단계1: 원래 열 옆의 셀에 첫 번째 데이터 분할을 수동으로 입력합니다

이 경우, 열 A에 있는 전체 이름을 별도의 이름과 성으로 분할하려고 합니다. 첫 번째 전체 이름은 셀 A2에 있으므로, 인접한 셀 B2를 선택하고 이름을 입력합니다. 스크린샷을 참조하세요:

단계2: 자동으로 모든 이름을 채우기 위해 플래시 채우기를 적용합니다

B2 아래 셀(B3)에 두 번째 이름을 입력하기 시작하면 Excel이 패턴을 인식하고 나머지 이름의 미리보기를 생성합니다. 미리보기를 수락하려면 Enter 키를 눌러야 합니다.

이제 열 A의 전체 이름에서 모든 이름이 열 B에 분리되었습니다.

단계3: 전체 이름에서 성을 다른 열에 가져오기

열 A의 전체 이름에서 성을 첫 번째 이름 열 옆의 열로 분할하려면 위의 단계1과2를 반복해야 합니다.

결과

수식을 사용하여 셀을 여러 열로 분할

위의 방법들은 동적이지 않기 때문에, 원본 데이터가 변경되면 동일한 과정을 다시 실행해야 합니다. 위의 예와 같이, 열 A에 있는 전체 이름 목록을 별도의 이름과 성으로 분할하고, 원본 데이터의 변경 사항에 따라 분할된 데이터가 자동으로 업데이트되도록 하려면, 다음 수식 중 하나를 시도해 보세요.

TEXT 함수를 사용하여 특정 구분 기호로 셀을 열로 분할합니다

이 섹션에서 제공하는 수식은 모든 Excel 버전에서 사용할 수 있습니다. 수식을 적용하려면 다음과 같이 하십시오.

단계1: 첫 번째 구분 기호 앞의 텍스트를 추출합니다 (이 경우 이름)

1. 이름을 출력할 셀(C2)을 선택하고, 다음 수식을 입력한 후 누르세요. 입력 A2에서 이름을 가져오기.
   

   =LEFT(A2,SEARCH(" ",A2)-1)

   

2. 이 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 나머지 이름을 가져옵니다.
   

   

단계2: 첫 번째 구분 기호 뒤의 텍스트를 추출합니다 (이 경우 성).

1. 성 출력 위치로 셀(D2)을 선택하고, 다음 수식을 입력한 후 누르세요. 입력 A2에서 성을 가져옵니다.
   

   =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

   

2. 이 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 나머지 성을 얻으세요.
   

   

TEXTSPLIT 함수를 사용하여 특정 구분 기호로 셀을 열로 분할합니다

Microsoft365용 Excel을 사용하는 경우, TEXTSPLIT 함수가 더 권장됩니다. 다음과 같이 진행하세요.

단계1: 결과를 출력할 셀을 선택합니다. 여기서는 셀 C2를 선택합니다.

단계2: 아래 수식을 입력하고 Enter 키를 누르세요

=TEXTSPLIT(A2," ")

A2의 모든 텍스트가 공백으로 분리되어 서로 다른 열로 분할된 것을 볼 수 있습니다.

단계3: 모든 결과를 얻기 위해 수식을 드래그하세요

같은 행에 있는 결과 셀을 선택한 다음, 자동 채우기 핸들을 아래로 드래그하여 모든 결과를 얻으세요.

Excel에서 셀을 여러 행으로 분할

아래 스크린샷에 표시된 것처럼, 범위 A2:A4에 주문 세부 정보 목록이 있으며, 항목, 수량, 단가 및 날짜와 같은 다양한 유형의 정보를 추출하기 위해 슬래시를 사용하여 데이터를 분할해야 합니다. 이 작업을 수행하기 위해 이 섹션에서는3가지 방법을 설명합니다.

TEXTSPLIT 함수를 사용하여 셀을 여러 행으로 분할

Excel for Microsoft365를 사용하는 경우, TEXTSPLIT 함수 방법이 쉽게 도와줄 수 있습니다. 다음과 같이 하세요.

단계1: 결과를 출력할 셀을 선택합니다. 여기서는 셀 B6을 선택합니다.

단계2: 아래 수식을 입력하고 Enter를 누르세요

=TEXTSPLIT(A2,,"/")

A2의 모든 텍스트는 '슬래시' 구분 기호를 기준으로 별도의 행으로 분할됩니다.

셀 A3 및 A4의 데이터를 슬래시를 기준으로 개별 행으로 분할하려면, 아래의 적절한 수식을 사용하여 단계1과2를 반복하십시오.

C6의 수식:

=TEXTSPLIT(A3,,"/")

D6 셀의 수식:

=TEXTSPLIT(A4,,"/")

결과

Kutools를 사용하여 셀을 여러 행으로 편리하게 분할하세요

Excel의 TEXTSPLIT 기능은 매우 유용하지만, Microsoft365 사용자에게만 제한됩니다. 게다가, 열에 여러 셀이 있는 경우, 각 셀에 개별적으로 다른 수식을 적용해야 결과를 얻을 수 있습니다. 반면에, Kutools for Excel의 셀 분할 기능은 모든 Excel 버전에서 작동합니다. 이 기능은 몇 번의 클릭만으로 셀을 여러 행이나 열로 한 번에 분할할 수 있는 간단하고 효율적인 솔루션을 제공합니다.

Kutools for Excel을 설치한 후, Kutools > 병합 & 셀 분할 > 셀 분할을 클릭하여 셀 분할 대화 상자를 엽니다.

1. 분할하려는 텍스트가 포함된 셀의 범위를 선택하세요.
2. 행으로 분할 옵션을 선택하세요.
3. 필요한 구분 기호를 선택하세요 (여기서는 기타 옵션을 선택하고 슬래시를 입력한 다음, 클릭하세요 확인.
   

   

4. 목적지를 선택하고 클릭하세요 확인 모든 데이터 분할을 가져오기 위해
   

VBA 코드를 사용하여 셀을 여러 행으로 분할

이 섹션에서는 Excel에서 셀을 여러 행으로 쉽게 분할할 수 있는 VBA 코드를 제공합니다. 다음과 같이 수행하세요.

1단계: Microsoft Visual Basic for Applications 창 열기

Alt + F11 키를 눌러 이 창을 엽니다.

단계2: 모듈을 삽입하고 VBA 코드를 입력합니다

삽입 > 모듈을 클릭한 다음, 아래의 VBA 코드를 모듈 (코드) 창에 복사하여 붙여넣습니다.

VBA 코드: 셀을 여러 행으로 분할하기

Option Explicit

Sub SplitCellsToRows()
'Updated by Extendoffice 20230727
    Dim inputRng As Range
    Dim outputRng As Range
    Dim cell As Range
    Dim splitValues() As String
    Dim delimiter As String
    Dim i As Long
    Dim columnOffset As Long
    On Error Resume Next
    
    Set inputRng = Application.InputBox("Please select the input range", "Kutools for Excel", Type:=8) ' Ask user to select input range
    If inputRng Is Nothing Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    Set outputRng = Application.InputBox("Please select the output range", "Kutools for Excel", Type:=8) ' Ask user to select output range
    If outputRng Is Nothing Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    delimiter = Application.InputBox("Please enter the delimiter to split the cell contents", "Kutools for Excel", Type:=2) ' Ask user for delimiter
    If delimiter = "" Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    If delimiter = "" Or delimiter = "False" Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    
    Application.ScreenUpdating = False
    
    columnOffset = 0
    For Each cell In inputRng
        If InStr(cell.Value, delimiter) > 0 Then
            splitValues = Split(cell.Value, delimiter)
            For i = LBound(splitValues) To UBound(splitValues)
                outputRng.Offset(i, columnOffset).Value = splitValues(i)
            Next i
            columnOffset = columnOffset + 1
        Else
            outputRng.Offset(0, columnOffset).Value = cell.Value
            columnOffset = columnOffset + 1
        End If
    Next cell
    
    Application.ScreenUpdating = True
End Sub

단계3: VBA 코드 실행

코드를 실행하려면 F5 키를 누르세요. 그런 다음 다음 설정을 수행해야 합니다.

1. 데이터를 분할할 셀을 선택하라는 대화 상자가 나타납니다 (여기서는 범위 A2:A4를 선택합니다). 선택을 완료한 후, 클릭하세요. 확인.
   

   

2. 두 번째로 나타나는 대화 상자에서 출력 위치를 선택해야 합니다(여기서는 셀 B6을 선택합니다), 그런 다음 클릭합니다. 확인.
   

   

3. 마지막 대화 상자에서 셀 텍스트를 분할하는 데 사용된 구분 기호를 입력합니다(여기서는 슬래시를 입력합니다) 그런 다음 클릭합니다. 확인 버튼입니다.
   

   

결과

선택한 범위의 셀은 동시에 여러 행으로 분할됩니다.

Power Query를 사용하여 셀을 여러 행으로 분할

특정 구분 기호로 셀을 여러 행으로 분할하는 또 다른 방법은 Power Query를 사용하는 것입니다. 이 방법은 원본 데이터와 함께 데이터 분할이 동적으로 변경될 수 있습니다. 이 방법의 단점은 완료하는 데 여러 단계가 필요하다는 것입니다. 작동 방식을 자세히 살펴보겠습니다.

단계1: 여러 행으로 분할하려는 셀을 선택하고, 데이터 > 테이블/범위에서 선택

단계2: 선택한 셀을 참조 테이블로 변환

선택한 셀이 Excel 표 형식이 아닌 경우, 참조 테이블 생성 대화 상자가 나타납니다. 이 대화 상자에서 Excel이 선택한 셀 범위를 올바르게 선택했는지 확인하고, 테이블에 헤더가 있는지 표시한 후 클릭하십시오. 확인 버튼입니다.
선택한 셀이 Excel 참조 테이블인 경우, 단계3으로 이동합니다.

단계3: 구분 기호로 열 분할 선택

테이블 – Power Query 편집기 창이 나타나면, 홈 탭에서 열 분할 > 구분 기호로 분할을 클릭합니다.

단계4: 구분 기호로 열 분할 대화 상자 구성

1. 구분 기호 선택 또는 입력 섹션에서 텍스트를 분할할 구분 기호를 지정합니다 (여기서는 사용자 정의를 선택하고 텍스트 상자에 슬래시 /를 입력합니다).
2. 기본적으로 접혀 있는 고급 옵션 섹션을 확장하고 행 옵션을 선택하세요.
3. 인용 문자 섹션에서 드롭다운 목록에서 없음 을 선택합니다;
4. 클릭 확인.
   

   

단계5: 데이터 분할을 저장하고 불러오기

1. 이 경우, 데이터 분할을 위한 사용자 정의 목적지를 지정해야 하므로 클릭합니다. 닫기 및 불러오기 > 닫기 및 로드 위치.
   

   

   팁: 데이터 분할을 새 워크시트에 로드하려면 닫기 및 로드 옵션을 선택하세요.
2. 에서 데이터 가져오기 대화 상자에서 선택하십시오 기존 워크시트 옵션을 선택하고, 데이터를 분할할 셀을 선택한 다음 클릭합니다. 확인.
   

   

결과

그런 다음 선택한 범위의 모든 셀이 지정된 구분 기호에 따라 동일한 열 내에서 서로 다른 행으로 분할됩니다.

결론적으로, 이 글에서는 Excel에서 셀을 여러 열이나 행으로 분할하는 다양한 방법을 탐구했습니다. 어떤 방법을 선택하든, 이러한 기술을 숙달하면 Excel에서 데이터를 처리할 때 효율성을 크게 향상시킬 수 있습니다. 계속 탐구하면 자신에게 가장 적합한 방법을 찾을 수 있을 것입니다.

관련 기사