엑셀 텍스트 나누기, 함수로 완전 자동화 하는 방법
엑셀 텍스트 나누기를 100% 완벽 자동화하는 함수 공식의 사용법과 동작원리 완벽 정리!
엑셀에서 텍스트 나누기 메뉴를 매번 실행하다 보면 데이터가 갱신될 때마다 같은 작업을 반복해야 합니다. 이 강의에서는 함수 공식 한 줄로 텍스트를 자동 분리하는 방법을 다룹니다. M365부터 엑셀 2019 이전 버전까지 모두 동작하도록 기본·응용 공식의 사용법과 동작 원리, 그리고 #N/A 오류를 숨기는 후처리까지 단계별로 정리합니다.
실습자료를 준비했어요
수업에서 사용한 예제 파일과 보충 자료를 한 곳에 정리했습니다!👇
목차 보기
실습 가이드
M365 최신 버전의 엑셀을 사용 중이라면, TEXTSPLIT 함수를 사용해 보세요. 텍스트 나누기를 비롯한 여러 데이터 가공 업무를 한층 더 수월하게 처리할 수 있습니다.
라이브 강의 전체 영상도 함께 확인해 보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브 강의 풀영상을 확인하실 수 있습니다.
함수 공식
기본 공식 (구분자가 쉼표(,)일 경우)
응용 공식 (구분자가 다른 기호일 경우)
인수 설명
| 인수 | 설명 |
| 셀 | 단어를 나눌 문장이 입력된 셀의 주소입니다. |
| 기호 [선택사항] |
각 단어를 구분하는 구분자입니다. 구분자가 쉼표(,)가 아닌 다른 기호인 경우 응용 공식을 사용합니다. |
엑셀 텍스트 나누기 자동화 공식은 사용 중인 엑셀 버전에 따라 적용 방법이 달라집니다. 동적 배열을 지원하는 엑셀 2021과 M365 버전에서는 복사한 공식을 붙여 넣는 것만으로 텍스트가 자동으로 나뉘며, 엑셀 2019 이전 버전에서는 복사한 공식을 배열 수식(Ctrl + Shift + Enter)으로 한 번 더 변환해 주면 됩니다.
- 텍스트 나누기 공식 입력하기 : 앞서 안내해 드린 텍스트 나누기 공식을 복사합니다.
오빠두Tip : 구분자가 쉼표(,)일 경우 첫 번째 기본 공식을, 구분자가 쉼표(,)가 아닌 다른 기호일 경우 응용 공식을 복사합니다. - 복사한 공식을 나뉜 단어를 출력할 셀에 붙여 넣습니다. 공식을 붙여 넣으면 #NAME? 오류가 표시됩니다.
- 공식에 사용된 인수 변경하기 : 공식이 입력된 셀과 옆쪽 빈 셀을 함께 범위로 선택한 뒤, 단축키 Ctrl + H를 눌러 [찾기 및 바꾸기] 대화상자를 실행합니다. 대화상자가 열리면 찾을 내용에는 '셀', 바꿀 내용에는 나눌 텍스트가 입력된 '셀 주소'를 입력합니다. 입력을 마쳤으면 [모두 바꾸기] 버튼을 클릭합니다.
오빠두Tip : 응용 공식을 사용하는 경우, 찾기 및 바꾸기를 한 번 더 실행하여 '기호'를 실제 구분자로 변경합니다. - 텍스트 나누기가 완료됩니다.
- 텍스트 나누기 공식 입력(2019 이전) : 엑셀 2019 이전 버전을 사용 중이라면 입력한 공식을 배열 수식으로 수정해야 합니다. M365·엑셀 2021 버전 단계에서 안내한 절차를 동일하게 진행하면 엑셀 2019 이전 버전 사용자에게는 #VALUE! 오류가 표시됩니다.
- 텍스트 나누기 결과를 출력할 범위를 넓게 선택한 상태에서 F2 키를 누르면 셀에 입력된 수식을 편집할 수 있는 상태로 전환됩니다. 이 상태에서 Ctrl + Shift + Enter를 누르면 배열 수식으로 입력됩니다.
- 텍스트 나누기가 완료됩니다.
- #N/A 오류 숨기기 : 배열 수식 결과 빈 셀에 표시된 #N/A 오류를 깔끔하게 숨겨 보겠습니다. 결과 범위를 선택한 상태에서 [홈] 탭 - [조건부 서식] - [새 규칙]을 선택한 뒤, 마지막 옵션인 '수식을 사용하여 서식을 지정할 셀 결정'을 선택합니다.
- 엑셀 화면 왼쪽 위 이름 상자에서 현재 활성화된 셀 주소를 확인한 뒤 아래 수식을 입력합니다. 예제에서는 활성 셀이 B5이므로 =ISERROR(B5)를 입력합니다.
=ISERROR(셀주소)
- 이어서 [서식] 버튼을 클릭한 뒤 글꼴 색상을 흰색으로 변경하고 [확인] 버튼을 눌러 규칙을 적용합니다.
- #N/A 오류가 흰색으로 처리되어 화면에서 자연스럽게 사라집니다.
오빠두Tip : 셀 왼쪽 위에 표시되는 초록색 표식은 엑셀 옵션에서 표시 여부를 변경할 수 있습니다. [파일] 탭 - [옵션] - [수식] - [오류 검사] 항목에서 '다른 작업을 수행하면서 오류 검사' 옵션을 체크 해제하면 됩니다. 오류 검사에 대한 자세한 설명은 아래 기초 입문 강의를 참고해 주세요.
[related_posts postid="50834"]
텍스트 나누기 추가기능(TextSplit 함수)
오빠두엑셀 홈페이지에서 제공하는 TextSplit 함수 추가기능을 사용하면 여러 구분자를 한 번에 지정하여 텍스트를 손쉽게 나눌 수 있습니다. TextSplit 함수 추가기능은 아래 관련 게시글에서 다운로드 받을 수 있습니다.
자주 묻는 질문
Q1. 텍스트를 가로 방향으로 나눌 수 있나요?
아래 수식을 사용하면 텍스트를 가로 방향으로 나눌 수 있습니다. 엑셀 2019 이전 버전 사용자는 가로 방향으로 출력할 범위를 미리 넓게 선택한 뒤 배열 수식으로 입력하면 됩니다.
Q2. 텍스트를 줄 바꿈 기준으로 나눌 수 있나요?
아래 수식을 사용하면 줄 바꿈 기준으로 텍스트를 나눌 수 있습니다. CHAR 함수는 각 번호에 해당하는 문자를 출력하며, CHAR(10)은 줄 바꿈 문자를 의미합니다.
=TRANSPOSE(TRIM(MID(SUBSTITUTE(","&A1,",",REPT(" ",LEN(A1)+1)),ROW(INDIRECT("A1:A"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))*LEN(A1)+1,LEN(A1)+1)))
선생님께서 올려두신 강의영상 및 추가 사항 보고 적용을 해보네 나누기는 잘되는데
철근콘크리트구조, 14개동, 4/12층,대지면적 27756.7㎡, 건축면적 6376.5849㎡, 연면적(합) 84395.1945㎡
이렇게 입력을 했을때
글자가 같이 입력이 됨 - 예를들어 14개동, 4/12층
2. 큰면적 100단위 구분기호 - 대지면적 1, 450 이런식으로 짤림
3. ㎡ 가 사라지지 않음
건축물의 구조는 제외하고 글자 및 기호는 안나타나게 할수 없나요??
선생님 꼭 답변 부탁드립니다.!!
공식에서 '셀' 을 'SUBSTITUTE(셀,"㎡","") 로 바꿔서 입력해보세요. 그러면 ㎡을 제외한 숫자만 표시할 수 있습니다.
결과값에서 숫자만 추출하려면 보조열에 아래 공식을 추가로 사용해서 한번 해결해보세요.
https://www.oppadu.com/%ec%85%80-%ec%88%ab%ec%9e%90-%ec%b6%94%ec%b6%9c/
len(셀)+1이 3번 들어가는데 여기서 +1은 왜 해주는건가요? 그냥 len(셀)로만 해도 제대로 텍스트나누기가 됩니다만 ㅠ
뒤에 +1 을 하지 않으면 마지막 나누기 된 문자가 잘리는 상황이 발생합니다.
한번 확인해보세요 ^^
=MID(SUBSTITUTE(","&B3,",",REPT(" ",LEN(B3))),ROW(INDIRECT("A1:A"&LEN(B3)-LEN(SUBSTITUTE(B3,",",""))+1))*LEN(B3),LEN(B3))
어떻게하나요 ?
빨리 출판됐으면 좋겠네요 ㅎㅎ
여기서 사과,배,포도
나누기는 영상 및 함수올려주신거 보고 활용중인데.
만약 사과 배 포도
이렇게 ","는없는 상황에서
사과
배
포도를 나누려면 어떻게면좋을까요
쉼표 없이 띄어쓰기로만 구분되어 있는 상황인가요?
그렇다면 두번째 "응용 공식" 에서 구분기호를 띄어쓰기로 바꿔서 한번 사용해보세요 :)
예제 파일 다운 받아서 연습하는데 공식 함수 복사 후 (' 제거 완료)
텍스트 바꾸기에서 셀주소로 변경 했는데 '사과' 1개 단어만 노출되고 그 외 단어는 아래로 노출이 안되어 알려주신 ctrl + shift + ent 눌러보았는데도 노출이 안됩니다ㅠㅠ
어떻게 해야할까요??ㅠㅠ
범위를 미리 넓게 선택한 상태에서 수식을 입력해보세요.
말씀주신데로 했었는데 안되더라구여ㅠㅠ