엑셀에서 다양한 형식의 텍스트 데이터를 다루는 일은 매우 흔합니다. 하지만 이름과 주소가 한 셀에 섞여 있거나, 쉼표로 구분된 데이터를 그대로 두면 분석이나 정리가 어렵죠. 이럴 때 텍스트를 적절히 나누어 별도의 열로 분할하면 데이터 활용도가 크게 높아집니다.
이 글에서는 엑셀에서 텍스트를 분할하는 8가지 실용적인 방법을 예시와 함께 자세히 소개합니다. 각 방법의 장단점을 이해하고 상황에 맞는 최적의 기능을 선택해 보세요.
예시 데이터셋 소개
설명을 위해 5명의 이름과 주소 정보가 담긴 아래 데이터셋을 사용하겠습니다. 각 셀에는 '이름, 도시, 국가' 순으로 쉼표로 구분된 텍스트가 입력되어 있습니다.
이제 아래 예제를 하나씩 따라 하며 텍스트 분할 방법을 익혀보겠습니다.
1. 텍스트 마법사(Text to Columns)로 텍스트 분할하기
가장 기본적이고 널리 사용되는 방법은 텍스트 마법사를 활용하는 것입니다. 구분 기호(Delimiter)를 기준으로 텍스트를 손쉽게 여러 열로 나눌 수 있습니다.
- 데이터 탭으로 이동한 뒤, 데이터 도구 그룹에서 텍스트 나누기를 선택합니다.
- 텍스트 마법사 첫 번째 단계에서 구분 기호로 분리됨(Delimited) 옵션을 선택하고 다음을 누릅니다.
- 두 번째 단계에서 구분 기호로 쉼표(Comma)를 선택한 후 다음을 클릭합니다.
- 마지막 단계에서 결과가 출력될 위치(대상 셀)를 C5로 지정하고 마침을 누릅니다.
- 완료! 아래 이미지처럼 텍스트가 깔끔하게 분할됩니다.
참고: 원본 데이터가 덮어써지지 않도록 대상 셀을 반드시 지정하는 것이 좋습니다.
2. 빠른 채우기(Flash Fill)로 텍스트 분할하기
빠른 채우기는 텍스트 마법사를 보완하는 강력한 기능입니다. 사용자가 보여준 패턴을 학습해 나머지 데이터를 자동으로 채워 줍니다.
- 먼저 C5 셀에 분할하려는 텍스트의 첫 번째 부분(이름)을 직접 입력합니다.
- 데이터 탭 → 데이터 도구 그룹 → 빠른 채우기를 선택합니다. 단축키 Ctrl + E를 사용해도 됩니다.
- 그러면 C6:C9 범위가 자동으로 채워지는 것을 확인할 수 있습니다.
- 같은 방식으로 두 번째, 세 번째 부분도 추출하면 최종 결과를 얻을 수 있습니다.
3. LEFT, RIGHT, FIND, LEN 함수 조합으로 분할하기
함수를 활용하면 수식 기반으로 동적으로 텍스트를 분할할 수 있습니다. 원본이 변경되면 결과도 자동으로 업데이트되는 것이 큰 장점입니다.
먼저 왼쪽 부분(이름)을 추출해 보겠습니다.
- C5 셀에 아래 수식을 입력한 후 Enter를 누릅니다.
=LEFT(B5,FIND(",",B5)-1)
- 채우기 핸들(AutoFill)을 드래그하면 아래와 같은 결과가 나타납니다.
여기서 LEFT 함수는 셀의 왼쪽부터 문자를 가져오고, FIND 함수는 B5 셀에서 쉼표의 위치를 찾아내는 역할을 합니다. 쉼표 바로 앞까지의 문자만 남기기 위해 FIND 결과에서 1을 빼준 것입니다.
다음으로 오른쪽 부분(주소)을 추출합니다.
- D5 셀에 아래 수식을 입력하고 Enter를 누릅니다.
=RIGHT(B5,LEN(B5)-FIND(",",B5))
- 마찬가지로 AutoFill을 사용해 D5:D9 범위에 분할된 텍스트를 채웁니다.
RIGHT 함수는 오른쪽 끝에서 문자를 추출하고, LEN 함수는 전체 문자열의 길이를 구하며, FIND 함수는 특정 문자의 위치를 찾습니다. 세 함수를 조합하면 쉼표 뒤의 모든 텍스트를 깔끔하게 얻을 수 있습니다.
4. TRANSPOSE 함수로 한 행에 분할하기
TRANSPOSE 함수를 활용하면 하나의 셀에 있는 텍스트를 여러 열로 한 번에 분할할 수 있습니다.
- C5 셀에 아래 수식을 입력합니다.
=TRANSPOSE(FILTERXML(""&SUBSTITUTE(B5,",","")&"
- Enter를 누르면 텍스트가 자동으로 C5, D5, E5 셀로 나뉩니다.
수식의 작동 원리를 살펴보면, SUBSTITUTE 함수가 쉼표를 XML 태그 형식으로 변환하고, FILTERXML 함수가 각 항목을 개별적으로 추출한 뒤, TRANSPOSE 함수가 이를 가로 배열로 배치합니다.
- 마지막으로 채우기 핸들(Fill Handle)을 아래 셀로 드래그하면 모든 행에 적용됩니다.
추가 팁: TRANSPOSE 없이 세로 방향 분할이 필요하다면 아래 수식만으로도 가능합니다.=FILTERXML("","//s") "&SUBSTITUTE(B3,",","")&"
참고: FILTERXML 함수는 Windows용 Excel 2013 이상에서만 사용할 수 있으며 Mac 버전에서는 지원되지 않습니다.
5. VBA 매크로로 텍스트 분할하기
반복 작업이 많거나 대량의 데이터를 처리해야 한다면 Excel VBA가 훌륭한 해결책입니다. 몇 줄의 코드로 자동화할 수 있습니다.
- 개발 도구 탭 → 코드 그룹 → Visual Basic을 선택합니다. 단축키 Alt + F11도 사용할 수 있습니다.
- 삽입 메뉴에서 모듈(Module)을 선택합니다.
- 빈 모듈 창에 아래 코드를 붙여넣습니다.
Sub SplitCellText()
Dim MyAry() As String, Cnt As Long, j As Variant
For n = 5 To 9
MyAry = Split(Cells(n, 2), ",")
Cnt = 3
For Each j In MyAry
Cells(n, Cnt) = j
Cnt = Cnt + 1
Next j
Next n
End Sub
- 실행(Run Sub) 버튼을 클릭하거나 키보드의 F5 키를 누릅니다.
- 매크로(Macros) 창이 나타나면 해당 매크로를 선택하고 실행을 클릭합니다.
- 이제 엑셀에서 텍스트 분할이 성공적으로 완료됩니다.
코드 설명: 이 매크로는 5행~9행(B열)의 데이터를 쉼표를 기준으로 Split 함수로 나눈 뒤, C열부터 차례대로 값을 입력합니다. 데이터 범위가 다르면 For n = 5 To 9 부분의 숫자를 조정하세요.6. 파워 쿼리(Power Query)로 텍스트 분할하기
파워 쿼리는 외부 통합 문서를 가져오고 연결하여 데이터를 원하는 형태로 변형할 수 있는 강력한 도구입니다. 대용량 데이터나 정기적으로 갱신되는 데이터를 다룰 때 특히 유용합니다.
- 데이터 탭 → 데이터 가져오기(Get Data)를 선택합니다.
- 파일에서(From File) → Excel 통합 문서에서(From Excel Workbook)를 차례로 선택합니다.
- 탐색기(Navigator) 창에서 대상 워크시트를 선택한 뒤 데이터 변환(Transform Data)을 클릭합니다.
- 파워 쿼리 편집기 창에서 변환(Transform) 탭 → 열 분할(Split Column)을 클릭합니다.
- 드롭다운 목록에서 구분 기호 기준(By Delimiter)을 선택합니다.
- 구분 기호 기준 열 분할 대화상자가 열리면 구분 기호로 쉼표(Comma)를 선택합니다.
- 분할 기준은 각 구분 기호 발생 시점(Each occurrence of the delimiter)으로 설정합니다.
- 확인을 누른 뒤, 닫기 및 로드(Close & Load)를 클릭합니다.
- 새 워크시트에 텍스트가 분할된 테이블이 생성됩니다.
- 이후 필요에 따라 테이블 서식과 구조를 자유롭게 수정할 수 있습니다.
7. TEXTBEFORE & TEXTAFTER 함수로 텍스트 분할하기
Microsoft 365 최신 버전에서 제공되는 TEXTBEFORE 및 TEXTAFTER 함수를 사용하면 훨씬 직관적으로 텍스트를 분할할 수 있습니다. 이번에는 성(full name) 데이터가 담긴 새로운 데이터셋(B5:B9)으로 성·중간 이름·이름을 분리해 보겠습니다.
- C5 셀에 아래 수식을 입력하고 Enter를 누릅니다.
=TEXTBEFORE(B5," ")
TEXTBEFORE 함수는 지정한 구분 기호(여기서는 공백)보다 앞에 있는 텍스트를 반환합니다.
- 다음으로 D5 셀에 아래 수식을 입력하고 Enter를 누릅니다.
=IFERROR(TEXTBEFORE(TEXTAFTER(B5," ")," ",-1),"")
중간 이름이 없는 경우 오류가 발생할 수 있으므로 IFERROR 함수를 함께 사용해 오류 시 빈 값을 반환하도록 처리했습니다.
- 마지막으로 E5 셀에 아래 수식을 입력합니다.
=TEXTAFTER(B5," ",-1)
TEXTAFTER 함수는 지정한 구분 기호 뒤의 텍스트를 반환하며, 인수 -1을 넣으면 뒤에서부터 첫 번째 구분 기호를 기준으로 합니다.
- 첫 행의 결과가 정상적으로 출력되는지 확인합니다.
- 채우기 핸들(Fill Handle)을 드래그하면 최종 결과를 얻을 수 있습니다.
참고: TEXTBEFORE와 TEXTAFTER 함수는 Microsoft 365(Excel 365) 이상 버전에서만 사용할 수 있습니다. 구버전 Excel이라면 위에서 소개한 LEFT, RIGHT, MID 함수 조합을 활용하세요.
8. 줄 바꿈(Line Break) 기준으로 텍스트 분할하기
지금까지는 쉼표나 공백 같은 일반 구분 기호를 다뤘지만, 실무에서는 한 셀 안에 줄 바꿈(Alt + Enter)으로 구분된 데이터도 자주 만납니다. CHAR(10)은 줄 바꿈 문자를 의미하며, 이를 활용해 분할할 수 있습니다.
- 첫 번째 줄을 추출하려면 C5 셀에 아래 수식을 입력합니다.
=LEFT(B5, SEARCH(CHAR(10),B5,1)-1)
LEFT 함수로 왼쪽 값을 추출하고, SEARCH 함수로 줄 바꿈 문자의 위치를 찾으며, CHAR 함수는 코드 번호 10에 해당하는 줄 바꿈 문자를 반환합니다.
- 두 번째 줄을 추출하려면 D5 셀에 아래 수식을 입력합니다.
=MID(B5, SEARCH(CHAR(10),B5) + 1, SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5)+1) - SEARCH(CHAR(10),B5) - 1)
MID 함수는 문자열의 중간에서 값을 가져오는 역할을 하며, SEARCH 함수로 계산한 시작 위치와 길이를 이용해 정확히 두 번째 줄의 텍스트만 추출합니다.
- 세 번째 줄을 추출하려면 E5 셀에 아래 수식을 입력합니다.
=RIGHT(B5,LEN(B5) - SEARCH(CHAR(10), B5, SEARCH(CHAR(10),B5) + 1))
RIGHT 함수로 오른쪽 값을 추출하고, SEARCH로 두 번째 줄 바꿈 위치를, LEN으로 전체 문자열 길이를 구해 마지막 줄의 텍스트를 정확히 분리합니다.
- 지금까지의 결과는 아래와 같습니다.
- 같은 절차를 나머지 행에 적용하면 최종 분할 결과를 얻을 수 있습니다.
응용: 엑셀에서 텍스트에서 숫자만 분리하기
텍스트와 숫자가 한 열에 섞여 있는 경우도 많습니다. 아래 예시는 숫자 부분만 골라 별도의 열로 추출하는 과정을 보여줍니다. 이번에도 빠른 채우기(Flash Fill)를 활용합니다.
- 먼저 B5 셀의 숫자 부분을 C5 셀에 직접 입력합니다.
- 바로 아래 셀을 선택한 뒤, 데이터 탭 → 빠른 채우기를 클릭합니다.
- 완료! 나머지 숫자들이 한 번에 모두 추출됩니다.
마치며
지금까지 엑셀에서 텍스트를 분할하는 8가지 실용적인 방법과 숫자 추출 응용 사례까지 살펴보았습니다.
각 방법의 특징을 간단히 정리하면 다음과 같습니다.
- 간편한 일회성 작업 → 텍스트 마법사, 빠른 채우기
- 수식 기반 동적 처리 → LEFT/RIGHT/FIND/LEN, TEXTBEFORE/TEXTAFTER
- 대량 데이터 자동화 → VBA, 파워 쿼리
상황과 데이터 규모에 맞는 방법을 선택해 활용해 보시기 바랍니다. 더 많은 엑셀 활용 팁이 궁금하다면 ExcelDemy의 다른 게시물도 확인해 보세요!