Excel 튜토리얼: 텍스트, 숫자, 날짜 셀 분할 (여러 열로 분리)

Excel을 사용할 때, 특정 목적으로 하나의 셀에서 여러 셀로 텍스트를 분할해야 할 수 있습니다. 이 튜토리얼은 세 부분으로 나뉩니다: 텍스트 셀 분할, 숫자 셀 분할, 날짜 셀 분할. 각 부분은 동일한 문제를 만났을 때 분할 작업을 처리하는 방법을 이해할 수 있도록 다양한 예제를 제공합니다.

1 텍스트 셀 분할

이 부분에서는 텍스트 셀을 여러 열로 분할할 때 직면하게 될 상황을 수집하고, 이에 대한 적절한 방법을 제공합니다.

예시 #1 셀을 쉼표, 공백 또는 다른 구분 기호로 분할

쉼표, 공백, 대시 등 특정 구분 기호로 텍스트 셀을 여러 열로 분할하려면 아래 방법 중 하나를 적용할 수 있습니다.

텍스트를 열로 분할 기능을 사용하여 셀을 구분 기호로 분할

텍스트를 열로 분할 기능은 Excel 내장 기능으로, 셀을 분할할 때 자주 사용됩니다. 아래 스크린샷에 표시된 것처럼, 쉼표로 텍스트 문자열 열의 셀을 분할하려면, 텍스트를 열로 분할 기능을 다음과 같이 적용할 수 있습니다.

1. 쉼표로 분할할 열 범위를 선택하고, 데이터 > 데이터 분할을 클릭합니다.

2. 데이터 분할 마법사 -3단계 중1단계 대화 상자에서 구분 기호 라디오 버튼을 선택한 상태로 유지하고, 다음 버튼을 클릭합니다.

3. 데이터 분할 마법사 -3단계 중2단계 대화 상자에서 필요에 따라 구분 기호를 지정합니다 (이 경우 쉼표 체크박스만 선택합니다), 그런 다음 다음 버튼을 클릭합니다.

4. 마지막 단계 대화 상자에서, 클릭하세요 분할된 텍스트를 출력할 셀을 선택하는 버튼을 클릭한 다음, 완료 버튼입니다.

선택한 범위의 텍스트는 쉼표로 분할되어 아래와 같이 다른 열에 배치됩니다.

수식을 사용하여 셀을 구분 기호로 분할

Excel에서 지정된 구분 기호로 셀의 텍스트를 분할하려면 아래 수식을 적용할 수 있습니다.

일반 수식

=TRIM(MID(SUBSTITUTE(A1,delim,REPT(" ",LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1)))

인수

그런 다음 이 수식을 적용하세요.

1. 아래 스크린샷과 같이, 먼저1,2,3...의 숫자가 다른 셀에 위치하도록 도우미 행을 생성해야 합니다.

참고: 여기서1,2,3...은 문자열의 첫 번째, 두 번째, 세 번째 부분 문자열을 나타냅니다.

2.1번 셀 아래의 셀을 선택하고, 아래 수식을 복사하거나 입력한 후 Enter 키를 눌러 문자열의 첫 번째 부분 문자열을 얻습니다. 결과 셀을 선택하고, 자동 채우기 핸들을 오른쪽과 아래로 드래그하여 다른 부분 문자열을 얻습니다. 스크린샷을 참조하세요:

=TRIM(MID(SUBSTITUTE($B5,",",REPT(" ",LEN($B5))),(D$4-1)*LEN($B5)+1,LEN($B5)))

참고: 수식에서 쉼표는 B5의 문자열을 분할하는 데 사용되는 구분 기호입니다. 필요에 따라 변경할 수 있습니다.

놀라운 도구로 셀을 구분 기호로 분할하기

여기에서는 Excel에서 특정 구분 기호로 셀을 별도의 열 또는 행으로 쉽게 분할할 수 있도록 돕는 Kutools for Excel의 셀 분할 기능을 추천합니다.

1. Kutools for Excel을 설치한 후, 문자열을 분할할 범위를 선택하고 Kutools > 병합 & 분할 > 셀 분할을 클릭합니다.

2. 셀 분할 대화 상자에서 다음과 같이 구성해야 합니다.

3. 다음 셀 분할 대화 상자에서 문자열을 출력할 셀을 선택한 후, 확인을 클릭합니다.

그런 다음 위의 단계2에서 지정한 기준에 따라 문자열이 다른 열 또는 행으로 분할됩니다.

데이터 분할:

행으로 분할:

예제 #2 특정 길이로 셀 분할

텍스트 문자열을 특정 길이로 분할하려면, 다음 방법들이 도움이 될 수 있습니다.

데이터 분할 기능을 사용하여 특정 길이로 셀 분할

데이터 분할 기능은 Excel에서 선택된 셀의 텍스트 문자열을 특정 길이로 분할할 수 있도록 고정 너비 옵션을 제공합니다.

아래 스크린샷에 표시된 것처럼, 범위 B5:B9의 텍스트를3문자마다 열로 분할하려면 다음과 같이 수행할 수 있습니다.

1. 문자열을 분할할 셀을 선택합니다.

2. 데이터 > 데이터 분할을 클릭합니다.

3. 데이터 분할 마법사 -3단계 중1단계 대화 상자에서 고정 너비 라디오 버튼을 선택하고 다음을 클릭합니다.

4. 그런 다음 3단계 중 2단계 대화 상자가 나타납니다. 데이터 미리보기 섹션에서 축의 원하는 위치를 클릭하여 분할선을 생성합니다 (화살표가 있는 선). 모든 분할선을 생성한 후 다음 버튼을 클릭하여 진행합니다.

이 경우, 문자열에서 매3문자마다 줄 바꿈을 생성합니다.

5. 마지막 단계 마법사에서 분리된 텍스트를 출력할 셀을 선택한 후 완료 버튼을 클릭하세요.

이제 선택한 셀의 문자열이 아래 스크린샷에 표시된 대로3자마다 분할됩니다.

놀라운 도구로 셀을 특정 길이로 분할하기

Excel에서 특정 길이로 셀을 분할하려면 Kutools for Excel의 셀 분할 기능 을 사용하여 쉽게 수행할 수 있습니다.

1. 특정 길이로 분할하려는 문자열 셀을 선택하고 Kutools > 병합 & 분할 > 셀 분할을 클릭합니다.

2. 셀 분할 대화 상자에서 다음과 같이 구성해야 합니다.

3. 다음 셀 분할 대화 상자에서 분할된 텍스트를 배치할 셀을 선택한 후 확인을 클릭합니다.

그런 다음 선택한 셀의 문자열이 특정 길이로 분할되어 다른 열에 배치됩니다.

예제 #3 특정 단어로 셀 분할

아래 스크린샷에 표시된 것처럼, 범위 B5:B9의 문자열을 전체 단어 "sales"로 분할하려면 이 섹션에서 제공된 수식을 적용할 수 있습니다.

셀에서 특정 단어 이전의 문자열을 가져오기

LEFT 함수와 FIND 함수를 기반으로 수식을 적용하면 문자열에서 특정 단어 이전의 부분 문자열을 얻는 데 도움이 됩니다.

일반 수식

=LEFT(A1,FIND("certain_word", A1)-1)

인수

1. 빈 셀을 선택하고, 아래 수식을 복사하거나 입력한 후 Enter 키를 눌러 특정 단어 앞의 문자열을 가져옵니다. 이 결과 셀을 선택한 다음, 자동 채우기 핸들을 아래로 드래그하여 다른 셀에 수식을 적용합니다.

=LEFT(B5,FIND("sales",B5)-1)

셀에서 단어 뒤의 문자열 가져오기

단어 앞의 문자열을 얻은 후, 그 뒤의 문자열을 얻기 위해 아래 수식을 적용해야 합니다.

일반 수식

=TRIM(MID(A1,SEARCH("certain_word",A1)+LEN("certain_word"),255))

인수

1. 첫 번째 결과 셀(D5) 옆의 빈 셀을 선택합니다.

2. 아래 수식을 복사하거나 입력하고 Enter 키를 누릅니다. 이 결과 셀을 선택하고, 다른 결과를 얻기 위해 자동 채우기 핸들을 아래로 드래그합니다.

=TRIM(MID(B5,SEARCH("sales",B5)+LEN("sales"),255))

이제 전체 단어를 기준으로 텍스트 문자열을 다른 열로 분할했습니다.

예제 #4 줄 바꿈으로 셀 분할

이 섹션에서는 Excel에서 줄 바꿈으로 텍스트 셀을 분할하는 다양한 방법을 설명합니다.

텍스트와 숫자로 분할 기능을 사용하여 셀을 새 줄 시작으로 분할

Excel에서 데이터 분할 기능을 사용하여 셀을 줄 바꿈으로 분할할 수 있습니다. 다음과 같이 진행할 수 있습니다.

1. 새 줄 시작으로 텍스트를 분할하려는 셀의 범위를 선택합니다.

2. 데이터 > 데이터 분할을 클릭합니다.

3. 데이터 분할 마법사 –3단계 중1단계 대화 상자에서 구분 기호 라디오 버튼을 선택한 후 다음을 클릭합니다.

4.3단계 중2단계 대화 상자에서 기존의 구분 기호 선택을 모두 해제하고, '기타' 체크박스를 선택한 후 Ctrl + J 단축키를 누릅니다. 텍스트 상자에 작은 점만 표시되는 것을 볼 수 있으며, 데이터 미리보기 상자에서는 텍스트가 줄 바꿈으로 분할됩니다. '다음' 버튼을 클릭하세요.

5. 마지막 단계 마법사에서 분리된 텍스트를 출력할 셀을 선택한 후 완료 버튼을 클릭하세요.

선택한 셀의 텍스트는 줄 바꿈으로 인해 다른 열로 분할됩니다.

수식을 사용하여 셀을 줄 바꿈으로 분할

다음 수식은 Excel에서 셀을 새 줄 시작으로 분할하는 데 도움이 될 수 있습니다.

위의 예와 동일하게 분할한 후, 서로 다른 열에 세 개의 문자열을 얻게 됩니다.

첫 번째 새 줄 시작 전의 문자열을 가져옵니다

먼저, 셀에서 첫 번째 새 줄 시작 전의 문자열을 분할하기 위해 왼쪽과 검색 함수를 기반으로 수식을 적용할 수 있습니다.

일반 수식

=왼쪽(셀, 검색(문자(10),셀,1)-1)

인수

1. 빈 셀을 선택합니다 (이 경우 D5). 아래 수식을 복사하거나 입력한 후 Enter 키를 누릅니다. 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 다른 셀의 첫 번째 새 줄 시작 전까지의 문자열을 가져옵니다.

=왼쪽(B5, 검색(문자(10),B5,1)-1)

첫 번째와 두 번째 줄 바꿈 사이의 문자열을 가져오기

셀에서 첫 번째와 두 번째 줄 바꿈 사이의 문자열을 얻으려면, 다음 수식을 사용할 수 있습니다.

일반 수식

=MID(셀,SEARCH(CHAR(10),셀)+1,SEARCH(CHAR(10),셀,SEARCH(CHAR(10),셀)+1)-SEARCH(CHAR(10),셀)-1)

인수

1. D5 옆의 셀(E5)을 선택하고 아래 수식을 복사하거나 입력한 후 Enter 키를 누르세요. 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 다른 셀의 첫 번째와 두 번째 줄 사이의 문자열을 얻으세요.

=MID(B5, 검색(문자(10),B5) +1, 검색(문자(10),B5,검색(문자(10),B5)+1) - 검색(문자(10),B5) -1)

두 번째 새 줄 시작 후 문자열을 가져오기

첫 번째 단계는 아래 수식을 사용하여 두 번째 줄 바꿈 이후의 문자열을 얻는 것입니다.

일반 수식

=RIGHT(셀,LEN(셀) - SEARCH(CHAR(10),셀, SEARCH(CHAR(10), 셀) +1))

인수

1. 셀(F5)을 선택하고, 아래 수식을 복사하거나 입력한 후 Enter 키를 누릅니다. 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 다른 셀의 두 번째 줄 바꿈 이후의 문자열을 가져옵니다.

=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10), B5) +1))

VBA를 사용하여 셀을 새 줄 시작으로 분할

이 섹션에서는 Excel에서 선택한 셀의 텍스트를 줄 바꿈으로 쉽게 분할할 수 있도록 돕는 VBA 코드를 제공합니다.

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

2. Microsoft Visual Basic for Applications 창에서 삽입 > 모듈을 클릭합니다. 그런 다음 아래 VBA 코드를 코드 창에 복사합니다.

VBA 코드: Excel에서 줄 바꿈으로 셀 분할

Sub ExtendOffice_SplitRangeTex()
'Updated by Extendoffice 20211116
Dim xStr() As String
Dim xRg As Range
Dim xCell As Range
Dim xI As Integer
Set xRg = Application.InputBox("Please select the range of cells where you want to split by line break:", "Kutools for Excel", "", , , , , 8)
If xRg Is Nothing Then Exit Sub
For xI = 1 To xRg.Count
    Set xCell = xRg.Item(xI)
    xStr = VBA.Split(xCell.Value, vbLf)
    xCell.Resize(1, UBound(xStr) + 1).Offset(0, 1) = xStr
Next
End Sub

3. F5 키를 눌러 코드를 실행합니다. 그러면 Kutools for Excel 대화 상자가 나타나며, 줄 바꿈으로 분할할 셀의 범위를 선택한 후, 마지막으로 OK 버튼을 클릭해야 합니다.

선택한 셀의 텍스트는 줄 바꿈을 기준으로 즉시 다른 열로 분할됩니다.

놀라운 도구로 셀을 줄 바꿈으로 분할하기

여기에서는 Kutools for Excel의 유용한 도구인 셀 분할 유틸리티를 소개합니다. 이 도구를 사용하면 선택한 셀의 텍스트를 줄 바꿈으로 대량 분할할 수 있으며, 몇 번의 클릭만으로 가능합니다.

1. 새 줄 시작으로 텍스트를 분할하려는 셀의 범위를 선택합니다.

2. Kutools > 병합 & 분할 > 셀 분할을 클릭하여 기능을 활성화합니다.

3. 셀 분할 대화 상자에서 다음 옵션을 설정해야 합니다.

4. 다음으로 나타나는 셀 분할 대화 상자에서 분할된 텍스트를 놓을 빈 셀을 선택하고 확인을 클릭합니다.

선택한 셀의 텍스트는 아래 스크린샷에 표시된 것처럼 줄 바꿈으로 인해 다른 열로 분할됩니다.

예제 #5 첫 번째 또는 마지막 구분 기호로 셀 분할

때때로 셀을 첫 번째 또는 마지막 구분 기호로만 두 부분으로 분할해야 할 때가 있습니다. 아래 수식을 시도해 보세요.

수식을 사용하여 첫 번째 구분 기호로 셀 분할

아래 스크린샷에 표시된 것처럼, 범위 B5:B9의 각 셀을 첫 번째 공백을 기준으로 두 부분으로 분할하려면 두 개의 수식을 적용해야 합니다.

첫 번째 공백 앞의 문자열을 분할

첫 번째 공백 이전의 문자열을 분할하려면 LEFT 함수와 FIND 함수를 기반으로 한 수식을 사용할 수 있습니다.

일반 수식

=왼쪽(셀,FIND("구분 기호",셀)-1)

인수

1. 부분 문자열을 출력할 셀(D5)을 선택하고, 아래 수식을 복사하거나 입력한 후 Enter 키를 누르세요. 결과 셀을 선택한 다음, 다른 셀의 부분 문자열을 얻기 위해 자동 채우기 핸들을 아래로 드래그하세요.

=LEFT(B5,FIND(" ",B5)-1)

첫 번째 공백 이후의 문자열을 분할

그런 다음 셀에서 첫 번째 공백 이후의 문자열을 얻기 위해 아래 수식을 적용하세요.

일반 수식

=RIGHT(cell,LEN(cell)-FIND("구분 기호",cell))

인수

1. 아래 수식을 셀 E5에 복사하거나 입력하고 Enter 키를 눌러 결과를 얻습니다. 이 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 다른 셀의 마지막 공백 이후의 문자열을 얻습니다.

=RIGHT(B5,LEN(B5)-FIND(" ",B5))

참고: 수식에서 필요에 따라 셀 참조를 문자열과 구분 기호로 변경할 수 있습니다.

수식을 사용하여 마지막 구분 기호로 셀 분할

스크린샷에 표시된 대로 셀 범위 (B5:B9)에서 텍스트를 마지막 공백을 기준으로 두 부분으로 분할하려면, 이 섹션에서 제공하는 두 가지 수식을 사용하여 작업을 완료할 수 있습니다.

마지막 구분 기호 왼쪽의 텍스트 가져오기

셀에서 마지막 구분 기호 왼쪽의 텍스트를 얻으려면 아래 수식을 적용할 수 있습니다.

일반 수식

=LEFT(B5,SEARCH("^",SUBSTITUTE(cell,"구분 기호","^",LEN(cell)-LEN(SUBSTITUTE(cell,"구분 기호",""))))-1)

인수

1. 왼쪽 문자열을 배치할 셀을 선택하고, 아래 수식을 복사하거나 입력한 후 Enter 키를 누르세요. 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 다른 셀에 수식을 적용하세요.

=왼쪽(B5,검색("^",대체(B5," ","^",길이(B5)-길이(대체(B5," ",""))))-1)

마지막 구분 기호 오른쪽의 텍스트 가져오기

이제 이 경우 마지막 공백 오른쪽의 텍스트를 가져와야 합니다.

일반 수식

=TRIM(RIGHT(SUBSTITUTE(cell,"구분 기호",REPT("구분 기호",LEN(cell))),LEN(cell)))

인수

1. 오른쪽 문자열을 배치할 셀을 선택하고, 아래 수식을 복사하거나 입력한 후 Enter 키를 누르세요. 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 다른 셀에 수식을 적용하세요.

=TRIM(RIGHT(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))),LEN(B5)))

참고: 수식에서 셀 참조를 텍스트 문자열과 구분 기호로 필요에 따라 변경할 수 있습니다.

예제 #6 대문자로 셀 분할

이 섹션에서는 셀의 단어를 대문자로 분할하는 사용자 정의 함수를 소개합니다.

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

2. Microsoft Visual Basic for Applications 창에서 삽입 > 모듈을 클릭한 다음, 아래 VBA 코드를 코드 창에 복사합니다.

VBA 코드: Excel에서 대문자로 셀 분할

Function GetName(s As String, Num As Long) As String
'Updated by Extendoffice 20211116
  With CreateObject("VBSCript.RegExp")
    .Global = True
    .Pattern = "[A-Z][a-z]+|[A-Z]"
    GetName = .Execute(s).Item(Num - 1)
  End With
End Function

3. Alt + Q 키를 눌러 Microsoft Visual Basic for Applications 창을 닫습니다.

4. 첫 번째 대문자 왼쪽의 단어를 출력할 셀(D5)을 선택하고, 아래 수식을 입력한 후 Enter 키를 누릅니다. 이 결과 셀을 선택하고 자동 채우기 핸들을 오른쪽으로, 그런 다음 아래로 드래그하여 다른 단어들을 얻습니다.

=IFERROR(GetName($B5,열($D:D)),"")

참고: 코드에서 $B5는 분할할 셀이고, $D:D는 결과 셀의 열입니다. 자신의 데이터에 맞게 변경하세요.

예제 #7 셀에서 이름 분할

시트에 전체 이름이 포함된 열이 있고, 전체 이름에서 이름과 성을 분할하여 별도의 열로 나누거나, 전체 이름에서 이름, 중간 이름 또는 성을 분할하고자 한다고 가정해 보겠습니다. 이 섹션에서는 이러한 문제를 해결하는 데 도움이 되는 자세한 단계를 나열합니다.

전체 이름을 이름과 성으로 분할

아래 스크린샷에 표시된 것처럼, 이름, 중간 이름, 성은 공백 하나로 구분되어 있습니다. 전체 이름에서 이름과 성만 분리하여 다른 열에 넣으려면 다음 방법 중 하나를 적용할 수 있습니다.

1) 수식을 사용하여 전체 이름을 이름과 성으로 분할

전체 이름을 이름과 성으로 분할하려면 두 개의 수식을 각각 적용해야 합니다.

전체 이름에서 이름 분할

전체 이름에서 이름을 분할하기 위해 LEFT 함수와 SEARCH 함수를 기반으로 수식을 적용할 수 있습니다.

일반 수식

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

1. 이름을 출력할 셀을 선택합니다.

2. 아래 수식을 복사하거나 입력하고 Enter 키를 누릅니다. 이 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 다른 전체 이름에서 이름을 분할합니다.

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

이제 지정된 셀 범위에서 모든 이름을 전체 이름에서 분할했으므로, 아래 수식을 적용하여 성을 분할하십시오.

전체 이름에서 성 분할

일반 수식

=RIGHT(cell, LEN(cell) - SEARCH("^", SUBSTITUTE(cell," ", "^", LEN(cell) - LEN(SUBSTITUTE(cell, " ", "")))))

1. 이름 셀 옆의 셀을 선택합니다.

2. 아래 수식을 복사하거나 입력하고 Enter 키를 누르세요. 이 결과 셀을 선택하고 다른 전체 이름에서 성을 얻기 위해 자동 채우기 핸들을 아래로 드래그하세요.

=RIGHT(B5, LEN(B5) - SEARCH("^", SUBSTITUTE(B5," ", "^", LEN(B5) - LEN(SUBSTITUTE(B5, " ", ""))))))

참고: 수식에서 B5는 분할하려는 전체 이름 셀입니다. 필요에 따라 변경할 수 있습니다.

2) 놀라운 도구로 전체 이름을 이름과 성으로 분할하기

수식은 많은 Excel 사용자에게 기억하기 어렵습니다. 여기서, Kutools for Excel의 이름 분할 기능을 추천합니다. 이 기능을 사용하면 몇 번의 클릭만으로 전체 이름을 쉽게 이름과 성으로 분할할 수 있습니다.

1. 전체 이름 셀의 범위를 선택하고, Kutools > 병합 & 분할 > 이름 분할을 클릭합니다.

2. 이름 분할 대화 상자에서 분할 유형 섹션에서 이름과 성 상자만 선택한 다음 확인을 클릭합니다.

참고: 선택한 범위는 분할 범위 상자에 표시되며, 필요에 따라 변경할 수 있습니다.

3. 그런 다음 다른 이름 분할 대화 상자가 나타납니다. 대상 셀을 선택하고 확인을 클릭하세요.

그러면 선택한 셀의 전체 이름이 아래 스크린샷과 같이 대량으로 이름과 성으로 분할됩니다.

전체 이름을 이름, 중간 이름, 성으로 분할

아래 스크린샷에 표시된 것처럼, 이름, 중간 이름, 성이 공백으로 구분된 전체 이름 열이 있습니다. 전체 이름을 이름, 중간 이름, 성으로 분할하여 다른 열에 넣으려면 아래 방법이 도움이 될 수 있습니다.

1) 텍스트를 열로 분할하여 전체 이름을 이름, 중간 이름 및 성으로 분할

내장 기능인 데이터 분할을 사용하면 Excel에서 전체 이름을 쉽게 이름, 중간 이름, 성으로 분할할 수 있습니다.

위에서 언급한 단계를 따라 데이터 분할 기능을 적용할 수 있습니다.

참고:3단계 마법사의2단계에서 공백 상자만 선택하십시오.

2) 수식을 사용하여 전체 이름을 이름, 중간 이름 및 성으로 분할

Excel에서 전체 이름을 이름, 중간 이름, 성으로 분할하기 위해 수식을 적용할 수도 있습니다.

전체 이름을 분할하는 데 사용되는 일반 수식

이름 분할

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

중간 이름 분할

=MID(cell, SEARCH(" ", cell) +1, SEARCH(" ", cell, SEARCH(" ", cell)+1) - SEARCH(" ", cell)-1)

성을 분할

=RIGHT(cell,LEN(cell) - SEARCH(" ",cell, SEARCH(" ",cell,1)+1))

그런 다음 다른 셀에 수식을 적용하여 이름, 중간 이름 및 성을 얻습니다.

1. D5 셀에 아래 수식을 입력하고 Enter 키를 누릅니다. 이 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 다른 전체 이름의 이름을 가져옵니다.

=왼쪽(B5,검색(" ", B5)-1)

2. 셀 E5에 아래 수식을 입력하고 Enter 키를 눌러 첫 번째 중간 이름을 얻습니다. 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 다른 중간 이름을 얻습니다.

=MID(B5, SEARCH(" ", B5) +1, SEARCH(" ", B5, SEARCH(" ", B5)+1) - SEARCH(" ", B5)-1)

3. 성을 얻으려면, 아래 수식을 셀 F5에 입력하고 Enter 키를 누른 다음, 결과 셀을 선택하고 필요한 셀 위로 자동 채우기 핸들을 드래그하세요.

=RIGHT(B5,LEN(B5) - SEARCH(" ",B5, SEARCH(" ",B5,1)+1))

3) 놀라운 도구로 전체 이름을 이름, 중간 이름, 성으로 분할

여기 Kutools for Excel의 이름 분할 기능에 대한 소개가 있습니다. 이 기능을 사용하면 몇 번의 클릭만으로 전체 이름을 이름, 중간 이름 및 성으로 한 번에 분할할 수 있습니다.

1. 분할하려는 전체 이름 셀을 선택하고, Kutools > 병합 & 분할 > 이름 분할을 클릭합니다.

2. 이름 분할 대화 상자에서 다음과 같이 구성해야 합니다.

3. 다음으로 나타나는 이름 분할 대화 상자에서 분할된 텍스트를 출력할 셀을 선택한 후 확인을 클릭하세요. 아래 데모를 참조하세요.

예제 #8 셀에서 텍스트와 숫자로 분할

아래 스크린샷과 같이 텍스트와 숫자가 혼합된 문자열 목록이 있다고 가정할 때, 텍스트와 숫자를 분리하여 다른 열에 배치하려면 네 가지 방법을 제공합니다.

수식을 사용하여 텍스트와 숫자를 분할

다음 수식을 사용하여 하나의 셀에서 텍스트와 숫자를 두 개의 분리된 셀로 분할할 수 있습니다.

일반 수식

셀에서 텍스트 가져오기

=왼쪽(셀,최소값(찾기({0,1,2,3,4,5,6,7,8,9},셀&"0123456789"))-1)

셀에서 숫자 가져오기

=RIGHT(cell,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789"))+1)

그런 다음 다른 셀에 이 두 수식을 적용하여 텍스트와 숫자를 분리하세요.

1. 목록의 첫 번째 문자열 텍스트를 배치할 빈 셀을 선택하고 아래 수식을 복사하거나 입력한 후 Enter 키를 누릅니다. 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 목록의 다른 문자열 텍스트를 가져옵니다.

=LEFT(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))-1)

2. 첫 번째 결과 셀(D5) 옆의 셀(E5)을 선택하고, 아래 수식을 복사하거나 입력한 후 Enter 키를 누릅니다. 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 목록의 다른 문자열의 수를 얻습니다.

=RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))+1)

플래시 채우기를 사용하여 텍스트와 숫자를 분할하기 (2013 및 이후 버전)

Excel2013 또는 이후 버전을 사용 중인 경우, 셀 하나에서 텍스트와 숫자를 두 열로 분할하기 위해 내장된 Flash Fill 기능을 적용할 수 있습니다.

참고: 플래시 채우기 기능을 작동시키려면 결과 열을 원본 텍스트 문자열 열 옆에 위치시켜야 합니다. 예를 들어, 원본 텍스트 문자열이 열 B에 위치한다면, 분리된 텍스트와 숫자는 열 C와 D에 배치해야 합니다. 스크린샷을 참조하세요:

1. 셀 C5에 첫 번째 문자열 셀(D5)의 텍스트를 수동으로 입력합니다.

2. 셀 C6에 두 번째 문자열 셀(D6)의 텍스트를 계속 입력하세요.

3. 셀 C7을 활성화하고, 데이터 > Flash Fill을 클릭합니다.

그런 다음 다른 문자열의 텍스트가 아래 스크린샷에 표시된 대로 자동으로 셀에 채워집니다.

4. 열 D에서 숫자를 얻기 위해1단계부터3단계를 반복합니다.

참고:

사용자 정의 함수로 텍스트와 숫자를 분할

목록의 문자열이 규칙적이지 않은 경우, 위의 두 방법은 아래 스크린샷과 같이 잘못된 결과를 반환할 수 있습니다.

여기에서는 사용자 정의 함수를 소개하여 셀에서 텍스트와 숫자를 두 열로 분할하는 방법을 설명합니다. 숫자가 문자열의 어디에 위치하든 상관없이 가능합니다. 스크린샷을 참조하세요:

1. Alt + F11 키를 누르세요.

2. 열려 있는 Microsoft Visual Basic for Applications 창에서 삽입 > 모듈을 클릭한 다음, 아래 VBA 코드를 코드 창에 복사합니다.

VBA 코드: 셀에서 텍스트와 숫자를 두 열로 분할

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Update by Extendoffice 20211105
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3. Alt + Q 키를 눌러 Microsoft Visual Basic for Applications 창을 닫습니다.

4. 첫 번째 문자열의 텍스트를 출력할 셀을 선택하고 아래 수식을 입력한 후 Enter 키를 누릅니다. 이 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 동일한 목록의 다른 문자열의 텍스트를 얻습니다.

=SplitText(B5,아니오)

5. 첫 번째 텍스트 결과 셀 옆의 셀을 선택하여 숫자를 출력하고, 아래 수식을 입력한 후 Enter 키를 누르세요. 이 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 다른 텍스트 문자열의 숫자를 얻으세요.

=SplitText(B5,예)

놀라운 도구로 텍스트와 숫자를 분할하기

여기에서는 셀에서 텍스트와 숫자를 한 번에 두 열로 분할하는 가장 쉬운 방법을 추천합니다. Kutools for Excel의 셀 분할 기능을 적용하면 몇 번의 클릭만으로 이 문제를 해결할 수 있습니다.

1. 텍스트와 숫자를 두 열로 분할하려는 문자열 셀을 선택합니다.

2. Kutools > 병합 & 분할 > 셀 분할을 클릭합니다.

3. 셀 분할 대화 상자에서 다음 옵션을 설정해야 합니다.

3.1) 선택한 범위가 목록에 표시됩니다. 분할 범위 상자에서 클릭할 수 있습니다 필요에 따라 새 범위를 선택하는 버튼;

3.2) 유형 섹션에서 행으로 분할 또는 데이터 분할을 선택합니다;

3.3) 구분 기호 섹션에서 텍스트와 숫자로 분할 라디오 버튼을 선택합니다;

3.4) 확인 버튼을 클릭하세요.

4. 다음 셀 분할 대화 상자에서 분할된 텍스트와 숫자를 출력할 셀을 선택한 후, 확인 버튼을 클릭합니다.

그러면 아래 스크린샷과 같이 선택한 셀의 텍스트와 숫자가 즉시 두 열로 분할된 것을 볼 수 있습니다.

2. 숫자 셀 분할

이 부분에서는 두 가지 상황에서 숫자 셀을 분할하는 방법을 설명합니다: 여러 자리 숫자를 개별 숫자로 분할하는 경우와 소수점에서 숫자를 분할하여 정수와 소수 부분을 분리하는 경우입니다.

예시 #1: 숫자로 셀 분할

여러 자리 숫자를 각각의 열에 개별 숫자로 분할하려면, 아래 방법 중 하나를 시도해 보세요.

수식을 사용하여 셀의 숫자를 개별 숫자로 분할하기

아래 수식은 숫자를 개별 숫자로 분할하여 다른 열에 배치하는 데 도움이 됩니다.

일반 수식

=MID($A1, 열($A$1:A$1),1)

인수

1. 첫 번째 숫자를 출력할 빈 셀을 선택하고, 아래 수식을 입력한 후 Enter 키를 누르세요.

=MID($B3, 열($B$3:B$3),1)

2. 이 결과 셀을 선택하고 자동 채우기 핸들을 오른쪽으로 드래그하여 다른 숫자를 얻습니다. 모든 결과 셀을 선택한 상태로 유지한 후 자동 채우기 핸들을 아래로 드래그하여 다른 숫자의 개별 숫자를 얻습니다.

참고: 이 수식에서 B3은 개별 숫자로 분할할 숫자가 들어 있는 셀이며, 필요에 따라 변경할 수 있습니다.

셀의 숫자를 VBA로 개별 숫자로 분할

아래 VBA 코드는 Excel에서 셀의 숫자를 개별 숫자로 분할하는 데 도움이 될 수 있습니다. 다음과 같이 수행할 수 있습니다.

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

2. Microsoft Visual Basic for Applications 창에서 삽입 > 모듈을 클릭한 후 아래 VBA 코드를 코드 창에 복사합니다.

VBA 코드: Excel에서 숫자를 개별 숫자로 분할

Sub SplitNumberIntoDigits()
'Updateby Extendoffice 2021118
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "Kutools for Excel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
 xInt = InputRng.Row

Application.ScreenUpdating = False

For Each Rng In InputRng
    xValue = Rng.Value
    xRow = (Rng.Row - xInt) + 1
    For I = 1 To VBA.Len(xValue)
        OutRng.Cells(xRow, I).Value = VBA.Mid(xValue, I, 1)
    Next
Next
Application.ScreenUpdating = True
End Sub

3. F5 키를 눌러 코드를 실행하면 Kutools for Excel 대화 상자가 나타납니다. 여기서 분할할 숫자 셀의 범위를 선택한 후 OK 버튼을 클릭해야 합니다.

4. 두 번째 Kutools for Excel 대화 상자가 나타나면, 개별 숫자를 출력할 셀을 선택하고 OK를 클릭해야 합니다.

참고: 이 코드는 단어를 개별 문자로도 분할할 수 있습니다.

선택한 셀의 숫자는 개별 숫자로 분할되어 다른 열에 배치됩니다.

놀라운 도구로 숫자를 개별 숫자로 쉽게 분할하세요

Kutools for Excel의 셀 분할 기능은 Excel에서 숫자를 개별 숫자로 쉽게 분할할 수 있도록 도와주는 유용한 도구입니다.

1. Kutools for Excel을 설치한 후, 분할할 숫자 셀의 범위를 선택하고, Kutools > 병합 및 분할 > 셀 분할을 클릭합니다.

2. 셀 분할 대화 상자에서 다음 옵션을 설정합니다.

2.1) 에서 분할 범위 섹션에서 선택한 범위가 텍스트 상자에 표시됩니다. 클릭할 수 있습니다. 필요에 따라 범위를 변경하는 버튼;

2.2) 유형 섹션에서 필요에 따라 행으로 분할 또는 데이터 분할을 선택합니다;

2.3) 구분 기호 섹션에서 너비 지정 라디오 버튼을 선택하고, 텍스트 상자에 숫자1을 입력합니다;

2.4) 확인 버튼을 클릭하세요.

3. 열리는 셀 분할 대화 상자에서 숫자를 출력할 빈 셀을 선택한 다음, 확인 버튼을 클릭합니다.

선택한 셀의 숫자는 즉시 개별 숫자로 분할됩니다.

예제 #2 소수점에서 숫자 분할

이 섹션에서는 Excel에서 숫자를 정수와 소수 부분으로 분할하는 몇 가지 방법에 대해 설명합니다.

수식을 사용하여 소수점에서 숫자 분할

TRUNC 함수는 Excel에서 숫자를 소수점에서 분할하는 데 적용할 수 있습니다. 다음과 같이 할 수 있습니다.

일반 수식

정수 부분 가져오기

=TRUNC(A1)

소수 부분 가져오기

=A1-TRUNC(A1)

인수

이제 Excel에서 소수점에서 숫자를 분할하기 위해 지정된 셀 범위에 이 두 수식을 적용할 수 있습니다.

1. 첫 번째 숫자 셀의 정수 부분을 놓을 셀을 선택하고, 아래 수식을 입력한 후 Enter 키를 누르세요. 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 다른 숫자 셀의 정수를 얻으세요.

=TRUNC(B5)

2. 첫 번째 결과 셀 옆에 있는 셀을 선택하여 소수 부분을 배치하고, 아래 수식을 입력한 후 Enter 키를 누르세요. 이 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 다른 숫자 셀의 소수를 얻으세요.

=B5-TRUNC(B5)

텍스트와 숫자로 분할하여 소수점에서 숫자 분할

Excel에서 소수점에서 숫자를 분할하려면 데이터 분할 기능을 적용할 수 있습니다.

1. 소수점에서 분할할 숫자 셀의 범위를 선택한 다음, 데이터 > 데이터 분할을 클릭합니다.

2. 데이터 분할 마법사 -3단계 중1단계 대화 상자에서 구분 기호 라디오 버튼을 선택하고 다음 버튼을 클릭합니다.

3. 3단계 중2단계 대화 상자에서 '기타' 체크박스만 선택하고, 텍스트 상자에 점을 입력한 후 '다음' 버튼을 클릭합니다.

4. 다음 단계에서 3단계 중3단계 대화 상자에서, 클릭하세요 분할된 정수와 소수를 출력할 대상 셀을 선택하는 버튼을 클릭한 후, 마지막으로 클릭합니다. 완료 버튼을 클릭하세요. 스크린샷을 참조하세요:

그런 다음 아래 스크린샷과 같이 선택한 셀에서 정수 부분과 소수 부분이 분할된 것을 볼 수 있습니다. 그러나 소수 부분은 사용자가 필요로 할 수 있는 음수 부호를 잃게 됩니다.

소수점에서 숫자를 분할하기 위해 플래시 필 사용 (2013 및 이후 버전)

위의 예에서 우리는 셀에서 텍스트와 숫자를 분할하기 위해 Flash Fill 기능을 사용하는 방법을 소개했습니다. 여기서는 같은 방법을 사용하여 소수점에서 숫자를 분할하는 방법을 소개하겠습니다.

1. 몇 가지 예를 입력합니다. 이 경우, B5의 정수 부분을 C5에 입력하고, B6의 정수 부분을 C6에 입력합니다. 스크린샷을 참조하세요:

참고: 음수를 입력할 때는 반드시 마이너스 기호를 함께 입력하세요.

3. 열에 채우고 싶은 예제가 포함된 셀을 선택하고, 데이터 > 플래시 채우기를 클릭하세요.

다음으로, 아래 스크린샷에 표시된 대로 지정된 숫자 셀에서 정수가 추출됩니다.

4. 위의 단계를 반복하여 동일한 숫자 셀에서 소수를 분할합니다.

3. 날짜 셀 분할

또 다른 자주 발생하는 상황은 날짜 셀을 분할하는 것입니다. 날짜 셀을 일, 월, 년으로 분할하거나 날짜 셀을 날짜 및 시간으로 분할해야 하는 경우, 필요에 따라 다음 솔루션을 사용하세요.

예제 #1 날짜 셀을 별도의 일, 월, 연도로 분할

B5:B9 범위에 날짜 목록이 있다고 가정하고, 각 날짜 값을 일, 월, 연도로 나누어 세 개의 별도 열로 분할하려고 합니다. 날짜를 분할하는 결과를 얻기 위한 세 가지 방법이 있습니다.

수식을 사용하여 날짜 셀을 일, 월, 연도로 분할

Excel에서 날짜를 일, 월, 연도로 분할하려면 DATE 함수, MONTH 함수, YEAR 함수를 기반으로 세 가지 수식을 적용할 수 있습니다.

일반 수식

=DATE(A1)

=월(A1)

=YEAR(A1)

인수

1. 별도의 일, 월, 연도를 배치할 세 개의 열을 생성합니다.

2. 일 열의 첫 번째 셀을 선택하고, 아래 수식을 입력한 후 Enter 키를 눌러 첫 번째 날짜 셀(B5)의 일을 구합니다. 이 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 다른 날짜 셀의 일을 구합니다.

=일(B5)

3.1단계와 동일한 계산을 수행하여 월 및 연도 열에 다음 수식을 적용하여 날짜 셀에서 개별 월과 연도를 얻습니다.

날짜의 월 가져오기

=월(B5)

날짜의 연도를 가져오기

=YEAR(B5)

날짜 셀을 일, 월, 연도로 데이터 분할을 사용하여 분할

위의 단계를 따라 Excel에서 날짜 셀을 일, 월, 연도로 분할하기 위해 데이터 분할 기능을 적용할 수 있습니다.

참고: 3단계 중2단계 대화 상자에서 '기타' 상자만 선택하고, 텍스트 상자에 / 기호를 입력하세요.

놀라운 도구로 날짜 셀을 일, 월, 연도로 분할하기

아래 데모는 Kutools for Excel의 셀 분할 기능을 사용하여 날짜를 월, 일, 연도로 분할하는 방법을 보여줍니다.

이 기능은 몇 번의 클릭만으로 날짜를 대량으로 분할하는 결과를 얻을 수 있도록 도와줍니다.

이 기능에 대해 더 알아보려면 클릭하세요.

예제 #2 셀에서 날짜 및 시간 분할

B5:B9에 날짜 및 시간 목록이 있다고 가정하고, 날짜와 시간을 별도의 열로 분할하고자 할 때, 이 섹션에서는 이를 수행하는 다양한 방법을 보여드립니다.

수식을 사용하여 셀에서 날짜 및 시간을 분할하기

Excel에서 날짜 및 시간을 다른 열로 분할하려면 다음 수식을 적용할 수 있습니다.

1. 날짜 및 시간을 배치할 두 개의 열을 준비합니다.

2. 날짜 열의 셀을 날짜 서식으로 설정하고, 시간 열의 셀을 시간 서식으로 설정해야 합니다.

3. 날짜 열의 첫 번째 셀을 선택하고 아래 수식을 입력한 후 Enter 키를 눌러 B5의 날짜를 얻습니다. 이 결과 셀을 선택하고 자동 채우기 핸들을 아래로 드래그하여 다른 날짜를 얻습니다.

=정수(B5)

4. B5:B9 셀에서 시간을 얻기 위해 시간 열에 다음 수식을 적용하세요.

=B5-D5

플래시 채우기를 사용하여 셀의 날짜 및 시간을 분할하기 (2013 및 이후 버전)

Excel2013 및 이후 버전을 사용 중인 경우, 내장된 플래시 채우기 기능을 적용하여 날짜와 시간을 다른 열로 분할할 수 있습니다.

1. 날짜 및 시간 열을 생성하고 원하는 출력 예시를 몇 가지 입력합니다. 날짜 열에서는 B5의 날짜를 C5에 입력하고, B6의 날짜를 C6에 입력합니다. 시간 열에서는 B5의 시간을 D5에 입력하고, B6의 시간을 D6에 입력합니다. 스크린샷을 참조하세요:

2. 채우고 싶은 날짜 열(예시 포함)을 선택하고, 데이터 > 플래시 채우기를 클릭하세요.

3. 채우고 싶은 시간 열(예시 포함)을 선택한 다음, Flash Fill 기능을 활성화합니다. 그러면 B5:B9의 날짜와 시간이 아래 데모와 같이 별도의 열로 분할됩니다.

이제 다양한 상황에서 Excel에서 셀을 분할하는 여러 방법을 배웠습니다. 특정 시나리오에 따라 어떤 방법을 선택할지 최선의 결정을 내릴 수 있을 것입니다.