데이터베이스나 다른 외부 소스에서 데이터를 가져올 때, 하나의 셀에 여러 정보가 뭉쳐 있는 경우가 많습니다. 이럴 때는 셀을 두 개 이상으로 분리해야 깔끔하게 정리할 수 있습니다.
이 글에서는 Excel에서 하나의 셀을 두 개로 나누는 효과적인 방법 5가지를 실제 예제와 함께 자세히 살펴보겠습니다.
Excel에서 셀 하나를 두 개로 나누는 방법
아래 예제 데이터에서 B열에는 전체 이름이 입력되어 있습니다. 이 이름 열을 '이름(first name)'과 '성(last name)' 두 개의 열로 분리해 보겠습니다. 아래에서 소개하는 다양한 방법으로 손쉽게 처리할 수 있습니다.
1. 텍스트 나누기(Text to Columns) 활용
예제 데이터에는 좋아하는 영문 문학 작가의 이름이 들어 있습니다. 텍스트 나누기(Text to Columns) 기능을 사용해 이름과 성으로 분리해 보겠습니다.
텍스트 나누기는 구분 기호(delimiter)를 기준으로 한 셀이나 한 열의 텍스트를 여러 열로 분리해 주는 매우 편리한 Excel 기능입니다.
구분 기호란 텍스트 문자열이나 데이터 스트림을 나누는 문자로, 쉼표, 공백, 세미콜론 등이 대표적입니다. 이 예제에서는 공백(Space)이 구분 기호 역할을 합니다.
다음 단계를 따라 진행하세요.
단계:
- 전체 데이터 범위(B4:B11)를 선택합니다.
- 데이터 탭에서 텍스트 나누기 옵션을 클릭합니다.
- 구분 기호로 분리됨(Delimited) 옵션을 선택합니다.
- 다음을 누릅니다.
- 공백(Space) 옵션을 선택하고 다음을 누릅니다.
- 열 데이터 서식에서 텍스트(Text)를 선택합니다.
- 필요하다면 대상 셀 위치를 조정한 후 마침을 누릅니다.
완료하면 아래와 같은 결과를 얻을 수 있습니다.
2. 빠른 채우기(Flash Fill) 활용
빠른 채우기(Flash Fill)는 데이터의 패턴을 자동으로 인식하여 값을 완성해 주는 특수 도구입니다. Microsoft Excel 2013 버전부터 제공되며, 머신러닝 기법을 활용해 데이터 패턴을 학습하고 그 패턴에 맞게 셀을 채워 줍니다.
이 기능을 사용하면 예제 데이터의 이름도 간단히 이름과 성으로 분리할 수 있습니다.
단계:
- 빈 셀(예: C5)을 선택합니다.
- B5 셀의 첫 번째 이름인 William을 선택한 C5 셀에 직접 입력합니다.
- 셀 오른쪽 하단 모서리를 더블클릭하면 전체 범위가 자동으로 채워집니다. 커서를 마지막 셀까지 드래그하거나, 데이터 > 데이터 도구 > 빠른 채우기 메뉴를 직접 선택할 수도 있습니다.
- 빠른 채우기 옵션을 선택합니다.
도구가 패턴을 인식해 전체 셀 범위에 이름(first name)을 자동으로 채워 줍니다.
같은 방식으로 성(last name)도 추출할 수 있습니다. 이 경우 D5 셀에 B5 셀의 성만 입력한 후 동일한 과정을 반복하면 됩니다. 결과는 다음과 같습니다.
3. 수식 적용으로 셀 나누기
i. 구분 기호로 나누기
Excel 함수와 구분 기호를 조합해서도 이름을 이름과 성으로 분리할 수 있습니다. 이 예제에서는 이름 중간에 있는 공백이 구분 기호입니다.
먼저 사용될 함수들을 간단히 살펴보겠습니다.
LEFT 함수는 문자열 왼쪽에서부터 지정한 개수만큼 텍스트를 추출합니다. 구문은 다음과 같습니다.
=LEFT(text, [num_chars])
text – 추출 대상이 되는 텍스트입니다.
num_chars – [선택 사항] 왼쪽부터 추출할 문자 수입니다.
RIGHT 함수는 문자열 오른쪽에서부터 지정한 개수만큼 텍스트를 추출합니다. 구문은 다음과 같습니다.
=RIGHT(text, [num_chars])
text – 추출 대상이 되는 텍스트입니다.
num_chars – [선택 사항] 오른쪽부터 추출할 문자 수입니다.
SEARCH 함수는 한 텍스트 안에서 다른 텍스트가 시작되는 위치를 반환합니다.
=SEARCH(find_text, within_text, [start_num])
find_text – 찾으려는 텍스트입니다.
within_text – 검색 대상 텍스트입니다.
start_num – [선택 사항] 검색을 시작할 위치입니다.
FIND 함수도 SEARCH와 마찬가지로 한 텍스트 안에서 다른 텍스트의 위치(숫자)를 반환합니다.
=FIND(find_text, within_text, [start_num])
find_text – 찾으려는 텍스트입니다.
within_text – 검색 대상 텍스트입니다.
start_num – [선택 사항] 검색을 시작할 위치입니다.
LEN 함수는 텍스트의 길이를 구합니다.
=LEN(text)
text – 길이를 계산할 텍스트입니다.
SUBSTITUTE 함수는 문자열 내에서 특정 텍스트를 다른 텍스트로 교체합니다.
=SUBSTITUTE(text, old_text, new_text, [instance])
text – 변경할 대상 텍스트입니다.
old_text – 교체할 기존 텍스트입니다.
new_text – 새로 넣을 텍스트입니다.
instance – [선택 사항] 교체할 특정 항목 번호입니다.
이제 LEFT와 SEARCH 함수를 조합해 이름(first name)을 추출해 보겠습니다.
빈 셀(예: C3)을 선택하고 =LEFT(B5,SEARCH(" ",B5)) 수식을 입력합니다. 여기서 B5는 이름을 분리할 셀입니다. Enter 키를 누른 후 채우기 핸들(Fill Handle)을 사용해 열 전체에 수식을 복사합니다.
같은 원리로 아래 수식을 사용하면 성(last name)을 추출할 수 있습니다.
=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5," ","*",LEN(B5)-LEN(SUBSTITUTE(B5," ","")))))
여기서 B5는 분리할 텍스트가 있는 셀이며, 공백(" ")이 구분 기호입니다.
ii. 줄 바꿈으로 나누기
이 방법도 앞선 방법과 유사하지만 CHAR 함수를 추가로 사용합니다.
CHAR 함수는 코드 번호(ASCII 코드)에 해당하는 문자를 반환합니다.
=CHAR(number)
number – 1~255 사이의 숫자입니다.
빈 셀에 아래 수식을 입력하고 채우기 핸들 도구로 나머지 셀에 복사하면 이름(first name)을 얻을 수 있습니다.
=LEFT(B5,SEARCH(CHAR(10),B5,1)-1)
여기서 B5는 대상 텍스트 셀이고, 10은 줄 바꿈(Line feed)의 ASCII 코드입니다.
참고: 1을 빼는 이유는 구분 기호인 공백 자체를 추출하지 않기 위함입니다.
마찬가지로 빈 셀에 아래 수식을 입력하면 성(last name)을 추출할 수 있습니다.
=RIGHT(B5,LEN(B5)-SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5)))
Enter 키를 누르고 같은 열의 다른 셀에도 채우기 핸들 도구를 사용해 수식을 복사합니다.
4. 텍스트+숫자 패턴 셀 나누기
때로는 텍스트+숫자 형태로 결합된 셀을 분리해야 할 수도 있습니다. 이 경우 SUM 함수를 포함한 여러 함수를 함께 사용합니다.
SUM 함수는 지정된 값들의 합계를 반환합니다. 값은 범위, 배열, 숫자 등이 될 수 있습니다.
=SUM(number1, [number2], [number3], …)
number1 – 합산할 첫 번째 값입니다.
number2 – [선택 사항] 합산할 두 번째 값입니다.
number3 – [선택 사항] 합산할 세 번째 값입니다.
이런 패턴의 셀을 나누는 가장 쉬운 방법은 먼저 숫자 부분을 분리한 후 텍스트 부분을 찾는 것입니다. 빈 셀을 선택하고 아래 수식을 입력합니다.
=RIGHT(B5,SUM(LEN(B5) - LEN(SUBSTITUTE(B5, {"0","1","2","3","4","5","6","7","8","9"},""))))
여기서 B5는 분리할 셀입니다. Enter 키를 누른 후 채우기 핸들 도구로 다른 셀에도 적용합니다.
텍스트 부분을 찾으려면 빈 셀에 아래 수식을 입력합니다.
=LEFT(B5,LEN(B5)-LEN(D5))
B5는 분리할 셀이며, D5는 앞선 수식으로 구한 숫자 값입니다.
5. 파워 쿼리(Power Query) 활용
파워 쿼리(Power Query)를 사용해서도 이름을 이름과 성으로 분리할 수 있습니다. 파워 쿼리는 다양한 소스에서 데이터를 가져오고, 필요에 따라 정리·변환·재구성할 수 있게 해주는 Microsoft 무료 추가 기능입니다.
단계:
- 전체 셀 범위 B4:B11을 선택합니다.
- 데이터 탭에서 테이블에서(From Table)을 클릭합니다.
- 확인을 누릅니다.
파워 쿼리 편집기가 열리면 홈 > 열 분할 > 구분 기호 기준을 선택합니다.
아래 화면에서 구분 기호로 공백(Space)을 선택하고 확인을 누릅니다.
이제 분석 작업은 완료되었습니다. 남은 일은 결과를 기존 워크시트로 불러오는 것입니다. 홈 > 닫기 및 로드 > 닫기 및 로드 대상을 선택합니다.
대상 위치를 지정한 후 로드를 누릅니다.
최종적으로 아래와 같은 결과를 확인할 수 있습니다.
유의사항
수식 입력줄에 수식을 입력할 때는 오타가 없도록 꼼꼼히 확인하세요.
또한 파일 이름, 저장 위치, Excel 파일 확장자도 신중하게 관리하는 것이 좋습니다.
결론
지금까지 가장 효과적이고 실용적인 셀 분리 방법들을 살펴보았습니다. 데이터의 특성과 요구 사항에 맞는 방법을 골라 활용해 보세요. 추가 제안이나 궁금한 점이 있다면 언제든 알려주세요.
추가 학습 자료
- Excel에서 셀 하나를 반으로 나누는 방법(대각선 및 수평)
- 쉼표로 문자열을 나누는 Excel 수식(5가지 예제)
- Excel VBA로 문자열을 문자 단위로 나누기(6가지 유용한 예제)
- Excel에서 한 셀에 두 줄 입력하기(4가지 방법)
- Excel VBA로 문자열을 셀로 분리하기(4가지 응용)
- Excel VBA로 문자 수 기준 문자열 나누기(2가지 쉬운 방법)