엑셀 TEXTSPLIT 함수, 텍스트 나누기 200% 활용법 | 10분 총정리 특강
엑셀 TEXTSPLIT 함수 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- 엑셀 TEXTSPLIT 함수, 실전 예제 총정리 특강예제파일엑셀 TEXTSPLIT 함수, 실전 예제 총정리 특강완성파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
TEXTSPLIT 함수 기초 예제
- 엑셀 이전버전 사용자 : TEXTSPLIT 함수는 현재 M365 버전 사용자에게만 제공됩니다. 만약 엑셀 2021 이전 버전을 사용하고 계실 경우, 이전 강의에서 소개해드린 '텍스트 나누기 공식'을 참고하세요.
오빠두Tip : 엑셀 초보자를 위한 상황별 텍스트 나누기 총정리 영상도 확인해보세요!👇
- TEXTSPLIT 함수 기초 사용법 : 예제파일을 실행한 후, 1번 시트로 이동합니다. 첫번째 예제로, 아래 그림과 같이 작성된 카테고리를 각 항목으로 분류해보겠습니다.
카테고리를 ">" 기호 기준으로 각 단계별 항목으로 구분합니다. - D5셀을 선택한 후, 아래와 같이 TEXTSPLIT 함수를 입력하면 각 단계별 카테고리가 한 번에 나눠집니다.
=TEXTSPLIT(B5,">")
'=TEXTSPLIT(텍스트,열구분자) → 열구분자는 문장을 가로로 구분합니다.
TEXTSPLIT 함수를 사용하면 텍스트를 편리하게 나눌 수 있습니다. - 여러 구분기호로 나누기 : 작성한 수식을 아래로 자동채우기하면, 아래 그림과 같이 인용 기호(">")로 구분되지 않은 카테고리는 올바르게 나눠지지 않게 됩니다. 이러한 경우, 여러개의 구분자를 중괄호("{}")로 묶어서 작성하면 여러 구분자로 문장을 구분할 수 있습니다.
여러 종류의 구분자가 사용된 경우, 중괄호 안에 여러 기호를 입력하여 한 번에 나눌 수 있습니다. - 다시 D5셀을 선택한 후, 아래와 같이 TEXTSPLIT 함수를 입력하고 수식을 자동채우기하면 여러 종류의 기호로 작성된 카테고리를 한 번에 나눌 수 있습니다.
=TEXTSPLIT(B5,{">","-",";"})
'B5셀에 작성된 문장을 인용기호(>), 대시(-), 세미콜론(;)으로 구분합니다.
TEXTSPLIT 함수의 구분자로 중괄호 안에 여러 개의 구분자를 입력합니다. - 문장을 표로 변환하기 : TEXTSPLIT 함수의 '열 구분자'와 '행 구분자'를 적절히 활용하면, 문장을 표로 나누어 효율적으로 관리할 수 있습니다. 아래 '행/열 나누기'에 작성된 문장을 보면, 각 과목과 점수는 등호("=")로 각 과목/점수 그룹은 띄어쓰기(" ")로 작성되어 있습니다.
열 구분자와 행 구분자를 적절히 활용하여 문장을 깔끔한 표로 변환합니다. - D10셀을 선택한 후, 아래와 같이 TEXTSPLIT 함수를 입력 후 실행하면, 각 과목과 점수로 나누어진 깔끔한 한 표가 완성됩니다.
=TEXTSPLIT(B10,"="," ")
과목과 점수가 작성된 문장을 열구분자("="), 행 구분자(" ")로 나눕니다. - D10셀에 작성된 문장에 새로운 과목을 입력하면, 표가 실시간으로 업데이트 되는 것을 확인할 수 있습니다.
각 과목과 점수가 작성된 표가 완성됩니다. - 단어 앞/뒤 공백 제거하기 : 예제파일에서 2번 시트로 이동하면, 아래 그림과 같이 각 카테고리를 나누는 기호의 앞/뒤로 무작위 공백이 들어간 카테고리 목록이 있습니다. 이러한 경우, TRIM 함수를 활용하면 구분기호의 앞/뒤로 공백이 들어간 문제를 간단하게 해결할 수 있습니다.
구분기호 앞 뒤로 무작위 공백이 들어간 경우, TRIM 함수를 사용합니다. - 2번 시트에서 C5셀을 선택한 후, 아래와 같이 TEXTSPLIT 함수를 TRIM 함수로 묶어서 작성합니다. 이렇게 수식을 작성하면, 구분기호의 앞/뒤로 들어간 공백이 제거된 단어를 깔끔하게 출력할 수 있습니다.
=TRIM(TEXTSPLIT(B5,">"))
'TRIM 함수는 문장에서 불필요하게 사용된 공백을 제거합니다.
TEXTSPLIT 함수를 TRIM 함수로 묶어주면, 불필요한 공백이 한 번에 제거됩니다. - 수식을 아래로 자동채우기해서 카테고리 구분을 완료합니다.
수식을 자동채우기하여 텍스트 나누기 작업을 완료합니다.
TEXTSPLIT 함수 고급 활용 예제
- 여러 문장을 한 번에 나누기 : TEXTSPLIT 함수와 같이 '분산 범위'를 반환하는 동적배열 함수는 일반 수식과는 달리, 자동채우기 핸들을 더블클릭해서 자동채우기를 실행할 수 없는 단점이 있습니다.
분산범위를 반환하는 동적배열 함수는 더블클릭으로 자동채우기를 실행할 수 없습니다. 오빠두Tip : 실무에서 자주 사용되는 엑셀 자동채우기의 꿀팁을 포함한 더욱 자세한 설명은 아래 5분 기초 영상강의를 참고하세요!
- 그럴 경우, TEXTJOIN 함수와 TEXTSPLIT 함수를 함께 활용하면 여러 문장을 공식 하나로 편리하게 나눌 수 있습니다. 예제파일에서 3번 시트로 이동한 후, C5셀에 다음과 같이 수식을 작성합니다.
=TEXTJOIN(";",,B5:B19)
'B5:B19 범위의 값을 세미콜론(;)으로 구분하여 한 문장으로 합칩니다. - 수식을 작성하면 B5:B19에 작성된 각 카테고리 목록이 세미콜론(;)으로 구분된 한 문장으로 완성됩니다. 이제 TEXTSPLIT 함수를 사용해서 열 구분자는 인용기호(">")로, 행 구분자는 세미콜론(";")으로 나누어 표를 만들 수 있습니다. C5셀에 작성한 TEXTJOIN 함수를 다음과 같이 TEXTSPLIT 함수로 묶어서 공식을 완성합니다.
=TEXTSPLIT(TEXTJOIN(";",,B5:B19),">",";")
TEXTJOIN + TEXTSPLIT 공식으로 여러 문장들을 한 번에 합치고 나눌 수 있습니다. - TEXTJOIN+TEXTSPLIT 공식 사용시 주의사항 : 단, TEXTJOIN 함수로 문장을 합쳐서 나눌 경우, 합쳐진 문장의 글자 수가 셀 안에 입력 가능한 최대 글자수(32,747자)를 초과하면 안 됩니다. 만약 문장의 글자수가 32,747자를 초과할 경우 #VALUE 오류가 반환되며, 그럴 경우 32,747자를 초과하지 않는 범위 내에서 문장을 합쳐야 합니다.
엑셀에서 한 셀 안에 입력가능한 최대 글자수는 32,767자로 제한됩니다. 오빠두Tip : 엑셀 파워쿼리를 사용하면 텍스트 나누기를 포함하여, 파일 취합, 데이터 정규화 등 다양한 데이터 정제 및 가공 업무를 자동화할 수 있습니다. 파워쿼리 기초-활용 강의는 위캔두 멤버십 회원에게 제공되는 파워쿼리 2주 마스터 특강을 확인해보세요!👇
[related_posts postid="164287"] - TEXTSPLIT 함수 #N/A 오류 해결 : TEXTJOIN + TEXTSPLIT 공식을 작성한 상태에서, 카테고리로 다섯번째 항목을 추가합니다. 이번 강의에서는 첫번째 카테고리의 새 항목으로 "자라"를 추가해보겠습니다.
카테고리의 다섯번째 새로운 항목으로 임의의 데이터를 추가합니다. - 새 항목을 추가하면, 아래 그림과 같이 5번째 항목이 비어있는 카테고리에는 #N/A 오류가 반환됩니다. 이러한 경우, IFERROR 함수를 활용해서 #N/A 오류를 간단하게 해결할 수 있습니다.
새로운 항목을 추가하면, 항목이 비어있는 곳에 #N/A 오류가 표시됩니다. 오빠두Tip : IFERROR 함수 대신 IFNA 함수를 사용해도 괜찮습니다!👍 - C5셀에 작성했던 TEXTJOIN+TEXTSPLIT 함수 공식을 아래와 같이 IFERROR 함수로 묶어서 작성하면, #N/A 오류가 사라진 깔끔한 표가 완성됩니다.
=IFERROR(TEXTSPLIT(TEXTJOIN(";",,B5:B19),">",";"),"")
IFERROR 함수를 활용해 #N/A 오류대신 빈칸을 표시합니다.
TEXTSPLIT 함수 실전 예제
- 여러 문장을 표로 변환하기 : 예제파일의 4번 시트로 이동합니다. 지금까지 배운 내용을 모두 종합하여, 아래 그림과 같이 "메뉴: 가격"으로 작성된 여러 문장을 하나의 깔끔한 표로 변환해보겠습니다. TEXTJOIN + TEXTSPLIT 함수 공식을 활용해 직접 표를 완성해보세요!
메뉴와 가격으로 작성된 여러 문장을 하나의 깔끔한 표로 병합합니다. - 범위에 작성된 여러 문장을 슬래시("/")로 합친 후, TEXTSPLIT 함수를 활용해 콜론(":")과 슬래시("/")로 나누어 줍니다. D5셀에 아래 수식을 입력하면, 여러 범위로 나뉘어 작성된 메뉴/가격 문장이 하나의 깔끔한 표로 완성됩니다.
=TEXTSPLIT(TEXTJOIN("/",,B5:B11),":","/")
슬래시("/")로 여러 문장을 합친 후, 열 구분자(":")와 행 구분자("/")로 나눠줍니다. - 숫자/문자 데이터 정제하기 : 단, 아래 그림과 같이 TEXTJOIN + TEXTSPLIT 함수 공식으로 단순이 텍스트를 나누면, 데이터가 모두 '문자' 형식으로만 반환되어 이후에 데이터를 정렬하거나 집계할 때 치명적인 문제가 발생할 수 있습니다. 엑셀은 문자 데이터는 '좌측', 숫자데이터는 '우측'으로 정렬합니다. 현재 반환된 데이터의 정렬을 보면, 가격의 경우 숫자이지만 '좌측'으로 정렬된 것으로 보아 문자 형식으로 반환된 것을 알 수 있습니다.
TEXTSPLIT으로 나눠진 값은 문자로 반환됩니다. (왼쪽 정렬) 오빠두Tip : 엑셀의 올바른 데이터 분석 작업을 위해 꼭 알아야 할 '데이터 형식'에 대한 자세한 설명은 아래 5분 기초 영상 강의를 참고하세요!
- 이러한 경우, 수식 뒤에 1을 곱해서 문자 형식의 데이터를 숫자로 강제 변경하거나, VALUE 함수를 사용합니다. D5셀에 작성된 수식을 다음와 같이 수정합니다.
=TEXTSPLIT(TEXTJOIN("/",,B5:B11),":","/")*1
'수식에 1을 곱해서, 문자 형식의 숫자 데이터를 올바른 숫자 형식으로 강제 변환합니다.
수식 뒤에 1을 곱해서 문자 형식의 숫자를 올바른 숫자로 변환합니다. - 공식에 1을 곱하게 되면, 문자 형식의 숫자는 올바른 숫자로 변환되지만, 숫자로 계산할 수 없는 문자가 있을 경우 VALUE 오류를 반환합니다. 따라서, 이러한 경우 IFERROR 함수를 활용하면 숫자/문자 데이터를 올바른 형식으로 한 번에 변환할 수 있습니다.
=IFERROR(TEXTSPLIT(TEXTJOIN("/",,B5:B11),":","/")*1,TEXTSPLIT(TEXTJOIN("/",,B5:B11),":","/"))
'1을 곱해서 문자 형식의 숫자는 올바른 숫자로 변환하고, 숫자로 변환할 수 없는 문자는 문자 그대로 표시합니다.
IFERROR 함수를 활용해 올바른 문자/숫자 데이터가 작성된 표를 완성합니다.
로그인
지금 가입하고 댓글에 참여해보세요!
4 댓글
