업무를 하다 보면 텍스트 데이터를 특정 열로 나누어 정리해야 할 때가 있습니다. 이런 경우 Excel의 텍스트 나누기(Text to Columns) 기능을 사용하거나, 다양한 수식을 적용하거나, VBA 코드를 활용할 수 있습니다. 이번 글에서는 Excel에서 수식으로 텍스트를 열로 자동 분할하는 방법을 익힐 수 있는 간단한 3가지 수식을 소개합니다. 텍스트를 열로 분할하는 수식을 찾고 계셨다면 이 글이 유용한 가이드가 될 것입니다.
Excel 수식으로 텍스트를 열로 자동 분할하는 3가지 방법
Excel에서 수식을 사용해 텍스트를 열로 자동 분할하는 방법은 크게 3가지가 있습니다. 아래의 절차만 그대로 따라 하면 누구나 쉽게 원하는 결과를 얻을 수 있습니다.
방법 1: LEFT, MID, RIGHT 함수 조합으로 텍스트를 열로 자동 분할하기
LEFT, MID, RIGHT 함수를 결합한 수식을 사용하면 텍스트를 손쉽게 열로 분할할 수 있습니다. 다음 절차를 따라 해 보세요.
단계:
- 먼저 정돈된 데이터셋을 준비합니다. 여기서는 IP 주소(IP Address) 열에 IP 주소를 입력했으며, 앞의 2자리는 도시 코드, 그다음 3자리는 고객 번호, 마지막 3자리는 부서 코드를 의미합니다.
- 앞의 2자리(도시 코드)를 분리하려면 아래 수식을 입력합니다.
=LEFT(B5,2)
- Enter 키를 눌러 결과를 확인합니다.
- 채우기 핸들(Fill Handle)을 사용해 나머지 셀까지 자동 채우기(AutoFill) 합니다.
- 같은 방식으로 중간 3자리를 추출하려면 아래 수식을 적용합니다.
=MID(B5,4,3)
- Enter 키를 눌러 결과를 확인합니다.
- 나머지 셀도 자동 채우기 합니다.
- 마지막 3자리를 추출하려면 아래 수식을 입력합니다.
=RIGHT(B5,3)
- Enter 키를 눌러 결과를 출력합니다.
- 마지막으로 나머지 셀까지 자동 채우기하면 모든 데이터 추출이 완료됩니다.
함께 읽으면 좋은 글: Excel에서 줄바꿈 문자로 텍스트 나누기 기능 사용하는 방법
방법 2: TRANSPOSE, FILTERXML, SUBSTITUTE 함수 조합으로 텍스트를 열로 자동 분할하기
TRANSPOSE, FILTERXML, SUBSTITUTE 함수를 조합한 수식으로도 텍스트를 열로 자동 분할할 수 있습니다. 다음 단계를 따라 해 보세요.
단계:
- 먼저 정돈된 데이터셋을 만듭니다. 여기서는 정보(Information) 열에 상세 정보를 담고 있으며, 이를 순서대로 이름(First Name), 성(Last Name), 성별(Gender), 도시(City), 회사(Company) 열로 분할하려고 합니다.
- 다음으로 아래 수식을 입력해 텍스트를 열로 분할합니다.
=TRANSPOSE(FILTERXML("<t><s>" &SUBSTITUTE(B5,",","</s><s>") & "</s></t>","//s"))
- Enter 키를 눌러 결과를 확인합니다.
- 마지막으로 나머지 셀도 자동 채우기 합니다.
함께 읽으면 좋은 글: Excel에서 기존 데이터를 덮어쓰지 않고 텍스트를 열로 변환하는 방법
방법 3: TRIM, MID, SUBSTITUTE 함수 조합으로 텍스트를 열로 자동 분할하기
텍스트를 열로 자동 분할하는 또 다른 효과적인 방법은 TRIM, MID, SUBSTITUTE 함수를 조합한 수식을 활용하는 것입니다. 다음 단계를 따라 해 보세요.
단계:
- 먼저 정돈된 데이터셋을 만듭니다. 여기서는 정보(Information) 열에 상세 정보를 담고 있으며, 이를 순서대로 이름, 성, 성별, 도시, 회사 열로 분할하려고 합니다.
- 셀 C5의 값을 구하기 위해 아래 수식을 입력합니다.
=TRIM(MID(SUBSTITUTE($B5,",",REPT(" ",LEN($B5))),COLUMNS($B:B)*LEN($B5)-(LEN($B5)-1),LEN($B5)))
- Enter 키를 눌러 셀 값을 확인합니다.
- 이제 오른쪽 방향으로 자동 채우기하여 텍스트를 열로 분할합니다.
- 마지막으로 채우기 핸들을 이용해 나머지 셀까지 자동 채우기 합니다.
함께 읽으면 좋은 글: Excel에서 여러 구분 기호로 텍스트를 열로 변환하는 방법
연습 영역
실력을 더욱 향상시키고 싶다면 아래 연습 영역에서 직접 실습해 보세요.
결론
지금까지 Excel에서 수식으로 텍스트를 열로 자동 분할하는 방법을 익힐 수 있는 간단한 3가지 수식을 살펴보았습니다. 이 글이 Excel 사용자 여러분께 조금이라도 도움이 되었다면 기쁘겠습니다. 추가로 궁금한 점이 있다면 댓글로 남겨 주세요. Excel 활용과 관련된 더 많은 글은 저희 사이트에서 확인하실 수 있습니다.
관련 글
- Excel 텍스트 나누기에서 줄바꿈을 구분 기호로 사용하는 방법
- [해결됨!] Excel 텍스트 나누기 시 데이터가 삭제되는 문제
- Excel에서 날짜에 텍스트 나누기를 활용하는 방법 (쉬운 단계별 가이드)