엑셀 텍스트 나누기, 함수로 완전 자동화 하는 공식 사용법
엑셀 텍스트 나누기 공식 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀고급] 엑셀 텍스트 나누기 자동화 공식예제파일
M365 최신 버전의 엑셀을 사용중이라면, TEXTSPLIT 함수를 사용해보세요! 텍스트 나누기를 포함한 여러 데이터 가공 업무가 더욱 수월해집니다!
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
함수 공식
기본 공식 (구분자가 쉼표(,)일 경우)
=TRIM(MID(SUBSTITUTE(","&셀,",",REPT(" ",LEN(셀)+1)),ROW(INDIRECT("A1:A"&LEN(셀)-LEN(SUBSTITUTE(셀,",",""))+1))*LEN(셀)+1,LEN(셀)+1))응용 공식 (구분자가 다른 기호일 경우)
=TRIM(MID(SUBSTITUTE("기호"&셀,"기호",REPT(" ",LEN(셀)+1)),ROW(INDIRECT("A1:A"&(LEN(셀)-LEN(SUBSTITUTE(셀,"기호","")))/LEN("기호")+1))*LEN(셀)+1,LEN(셀)+1))인수 설명
엑셀 텍스트 나누기 자동화 공식 설명 인수 설명 셀 단어를 나눌 문장이 입력된 셀 주소입니다. 기호
[선택사항]각 단어를 나누는 구분자입니다. 구분자가 쉼표(,)가 아닌 다른 기호일 경우, 응용 공식을 사용합니다. 엑셀 텍스트 나누기 자동화 공식은 사용 중인 엑셀 버전에 따라 사용법이 다릅니다. 동적배열을 지원하는 엑셀 2021과 M365 버전은 복사한 공식을 붙여넣기하면 바로 텍스트가 나눠지며, 엑셀 2019 이전 버전은 복사한 공식을 배열수식(Ctrl + Shift + Enter)로 한번 더 변환해주면 됩니다.
- 텍스트 나누기 공식 입력하기 : 텍스트 나누기 공식을 복사합니다.
게시글에 적어드린 텍스트 나누기 공식을 복사합니다. 오빠두Tip : 구분자가 쉼표(,)일 경우 첫번째 기본 공식을, 구분자가 쉼표(,) 외 다른 기호일 경우 응용공식을 복사합니다. - 복사한 공식을 나눠진 단어를 출력할 셀에 붙여넣기 합니다. 공식을 붙여넣기 하면 #NAME? 오류가 출력됩니다.
공식을 붙여넣기 후 입력하면 #NAME? 오류가 표시됩니다. - 공식에 사용된 인수 변경하기 : 공식이 입력된 셀과 옆에 비어있는 셀을 범위로 선택 후, 단축키 Ctrl + H 를 눌러 찾기 및 바꾸기를 실행합니다. [찾기 및 바꾸기] 대화상자가 실행되면 찾을 내용은 '셀', 바꿀 내용은 나눌 텍스트가 입력된 '셀주소'를 입력합니다. 모두 입력하였으면 [모두 바꾸기] 버튼을 클릭합니다.
찾기 및 바꾸기에서 찾을내용은 '셀', 바꿀내용은 '셀주소'를 입력후 모두바꾸기를 클릭합니다. 오빠두Tip : 만약 응용 공식을 사용할 경우, 찾기 및 바꾸기를 한번 더 실행하여 '기호'를 해당 구분자로 변경합니다. - 텍스트 나누기가 완료되었습니다.
텍스트 나누기가 완료되었습니다.
- 텍스트 나누기 공식 입력(2019 이전) : 엑셀 2019 이전 버전을 사용 중일 경우 입력한 공식을 배열수식으로 수정해야합니다. M365/엑셀2021 버전에 소개해드린 단계를 모두 진행하면 엑셀 2019 이전 버전 사용자는 #VALUE! 오류가 표시됩니다.
엑셀 2019 이전 버전 사용자는 #VALUE! 오류가 표시됩니다. - 텍스트 나누기로 출력할 범위를 넓게 선택한 상태에서 키보드 F2키를 누르면 셀 안에 입력된 수식을 편집할 수 있습니다. 그 상태에서 Ctrl + Shift + Enter를 누르면 배열수식으로 입력합니다.
범위를 넓게 선택 후, F2키를 누르면 수식을 편집할 수 있습니다. - 텍스트 나누기가 완료됩니다.
Ctrl + Shift + Enter를 눌러 배열수식으로 입력합니다. - #N/A 오류 숨기기 : 배열수식으로 출력되면서 표시된 #N/A 오류를 숨겨주겠습니다. 범위를 선택한 상태에서 [홈] 탭 - [조건부서식] - [새 규칙]을 선택 후, 마지막 옵션인 '수식을 사용하여 서식을 지정할 셀 결정'을 선택합니다.
범위 선택 후 새로운 조건부서식을 추가합니다. - 엑셀 화면 왼쪽 위 이름상자에 현재 활성화된 셀 주소를 확인 후, 아래 수식을 입력합니다. 예제에서는 B5이므로 =ISERROR(B5)를 입력했습니다.
=ISERROR(셀주소)
ISERROR 함수로 오류를 숨겨줍니다. - [서식] 버튼을 클릭 후, 글꼴 색상을 흰색으로 변경한 뒤 [확인] 버튼을 클릭하여 마무리 합니다.
조건을 만족할 경우 글씨 색상을 흰색으로 변경합니다. - #N/A 오류가 흰색으로 바뀌면서 오류표시가 사라집니다.
#N/A 오류가 사라지며 텍스트 나누기가 완료됩니다. 오빠두Tip : 셀 왼쪽 위 나타나는 초록색 표식은 엑셀 옵션에서 표시설정을 변경할 수 있습니다. [파일] 탭 - [옵션] - [수식] - [오류 검사] 옵션에서 '다른 작업을 수행하면서 오류검사' 를 체크해제합니다. 오류검사에 대한 자세한 설명은 아래 기초입문 강의를 참고해주세요.
텍스트 나누기 추가기능(TextSplit 함수)
오빠두엑셀 홈페이지에서 제공해드리는 TextSplit 함수 추가기능을 사용하면 여러 구분자를 지정하여 텍스트 나누기를 빠르고 편리하게 사용할 수 있습니다. TextSplit 함수 추가기능은 관련 게시글에서 다운로드 받을 수 있습니다.
엑셀 TEXTSPLIT 함수 추가기능 다운로드 자주 묻는 질문
Q1. 텍스트를 가로 방향으로 나눌 수 있나요?
아래 수식을 사용하면 텍스트를 가로 방향으로 나눌 수 있습니다. 엑셀 2019 이전 버전 사용자는 배열수식으로 입력하기 전, 가로 방향으로 넓게 선택 후 배열수식으로 입력합니다.
=TRANSPOSE(TRIM(MID(SUBSTITUTE(","&셀,",",REPT(" ",LEN(셀)+1)),ROW(INDIRECT("A1:A"&LEN(셀)-LEN(SUBSTITUTE(셀,",",""))+1))*LEN(셀)+1,LEN(셀)+1)))Q2. 텍스트를 줄바꿈 기준으로 나눌 수 있나요?
아래 수식을 사용하면 줄바꿈 기준으로 텍스트를 나눌 수 있습니다. CHAR 함수는 각 번호에 해당하는 기호를 출력하며, CHAR(10)은 줄 바꿈을 출력합니다.
=TRIM(MID(SUBSTITUTE(CHAR(10)&셀,CHAR(10),REPT(" ",LEN(셀)+1)),ROW(INDIRECT("A1:A"&(LEN(셀)-LEN(SUBSTITUTE(셀,CHAR(10),"")))/LEN(CHAR(10))+1))*LEN(셀)+1,LEN(셀)+1)) - 텍스트 나누기 공식 입력하기 : 텍스트 나누기 공식을 복사합니다.
