엑셀 함수 활용예제여러 줄로 입력된 텍스트 분리하기 예제

여러 줄로 입력된 텍스트를 나눠서 각각의 셀로 분리하기 | 엑셀 함수 공식 

함수공식

[첫번째 줄]
= LEFT (셀, FIND(CHAR(10), 셀))
[가운데 줄]
=MID (셀, FIND("|", SUBSTITUTE(셀, CHAR(10), "|", [@]-1)), FIND("|", SUBSTITUTE(셀, CHAR(10), "|", [@])) – FIND("|", SUBSTITUTE(셀, CHAR(10), "|", [@]-1)))
[마지막 줄]
= RIGHT (셀, LEN(셀) – FIND("|", SUBSTITUTE(셀, CHAR(10), "|", [@]-1)))
'// [@] : 줄번호 (예: 두번째줄: 2, 세번째줄: 3, 네번쨰줄: 4, …)
'// "|" : 만약 셀 안에 입력된 텍스트 중, "|" 문자가 사용중일 경우 다른 문자로 대체 가능. ("#", "^", 등..)

공식에 대한 설명

하나의 셀 안에 여러줄로 입력된 텍스트를 줄마다 나누어 각각의 셀에 출력합니다. 만약 여러 줄이 아닌 두 줄로만 나눠진 경우 LEFT 함수RIGHT 함수를 이용하여 보다 간단한 공식을 작성할 수 있습니다. 텍스트 줄바꿈으로 입력하는 방법을 모르실 경우 관련 오빠두엑셀 링크를 참조하세요.

예제파일 다운로드 및 응용방법

실습파일

영상강의

본 포스트의 영상강의는 준비중입니다.
영상강의가 필요하신가요? 아래에 댓글로 남겨주세요! 여러분의 소중한 댓글은 저에게 큰 힘이 됩니다!

사용된 함수

[첫번째 줄]
= LEFT($A$2, FIND(CHAR(10), $A$2))
[가운데 줄]
= MID($A$2, FIND("|", SUBSTITUTE($A$2, CHAR(10), "|", C3-1)), FIND("|", SUBSTITUTE($A$2, CHAR(10), "|", C3)) – FIND("|", SUBSTITUTE($A$2, CHAR(10), "|", C3-1)))
[마지막 줄]
= RIGHT($A$2, LEN($A$2) – FIND("|", SUBSTITUTE($A$2, CHAR(10), "|", C9-1)))

함수의 동작 원리

CHAR함수는 숫자를 지정하여 그에 해당하는 문자를 반환하는 함수입니다. 그 중 CHAR(10) 텍스트 줄바꿈을 반환합니다. 세개의 공식 중 [가운데 줄]을 추출하는 공식을 예로 설명합니다.

MID 함수의 인수는 아래와 같습니다. 자세한 내용은 관련 링크를 참조하세요.

= MID ( 문자열, 시작위치, 추출할_문자수)

[시작위치 구하기]
FIND 함수를 통해 A2셀에서 출력하고자 하는 줄번호 이전 마지막으로 위치한 '텍스트 줄바꿈(=CHAR(10))'의 위치를 구합니다.
그 전에 앞서 SUBSTITUTE 함수를 이용하여 A2셀에서 여러개의 '텍스트 줄바꿈' 중 원하는 순서의 '텍스트 줄바꿈'을 특정 문자("|")로 변환한 뒤, 변환된 텍스트 문자열이 FIND 함수의 인수로 입력됩니다.

= MID($A$2, FIND("|", SUBSTITUTE($A$2, CHAR(10), "|", C3-1)), FIND("|", SUBSTITUTE($A$2, CHAR(10), "|", C3)) – FIND("|", SUBSTITUTE($A$2, CHAR(10), "|", C3-1)))
'// SUBSTITUTE 함수를 통해 A2셀에서 3번째(예: C3이 4일 경우, 3)로 위치한 텍스트 줄바꿈을 "|"로 변환 한 뒤 그 문자열을 반환합니다.
'// 그럴 경우 SUBSTITUTE 함수를 통해 출력된 문자열에서는 "|" 문자가 하나밖에 없므로 FIND 함수로 "|" 를 찾게되면 3번째로 위치해있던 텍스트 줄바꿈의 위치값을 구할 수 있습니다.

[추출할_문자수 구하기]
비슷한 원리로 FIND 함수SUBSTITUTE 함수를 이용하여 A2셀에서 추출할 문자수를 구합니다.
추출할 문자수는 각각의 '텍스트 줄바꿈' 사이에 있는 글자수입니다. 예를 들어 4번째 줄에 있는 문자열을 추출하고 싶을 경우, 3번째에 위치한 '텍스트 줄바꿈'과 4번째에 위치한 '텍스트 줄바꿈' 사이의 글자수가 바로 추출할 문자수가 됩니다.

= MID($A$2, FIND("|", SUBSTITUTE($A$2, CHAR(10), "|", C3-1)), FIND("|", SUBSTITUTE($A$2, CHAR(10), "|", C3))FIND("|", SUBSTITUTE($A$2, CHAR(10), "|", C3-1)))
'// 파란색으로 쓰여진 FIND 함수는 4번째 (예: C3이 4일 경우, 4)에 위치한 텍스트 줄바꿈의 위치를 반환합니다.
'// 분홍색으로 쓰여진 FIND 함수는 3번째에 위치한 텍스트 줄바꿈의 위치를 반환합니다. 따라서 두 FIND 함수의 결과값을 빼주면 A2셀에서 추출할 문자수가 계산됩니다.

기초함수 설명 | by 오빠두엑셀

엑셀 FIND 함수 예제
엑셀 FIND 함수 사용법 (예제파일) – 텍스트 함수
실습파일 선택한 셀에서 특정문자가 시작하는 위치를 숫자로 반환합니다. FIND 함수는 기존 텍스트 문자열에서 특정 문자를 검색한 뒤 기존 텍스트 문자열에서 ...
엑셀 MID 함수 사용법 (예제파일) :: 텍스트 함수
엑셀 MID 함수 사용법 (예제파일) :: 텍스트 함수
실습파일 선택한 문자열의 지정한 위치부터 원하는 문자수만큼 문자열을 반환합니다. MID 함수는 선택된 문자열 또는 셀에 포함된 문자열에서 사용자가 지정한 위치로부터 ...

관련된 함수공식 

글자수 상관없이 특정문자 뒤의 문자열을 추출합니다.
글자수 상관없이 두개의 특정문자 사이의 값을 추출합니다.
줄바꿈(Alt+Enter)로 입력된 문자열을 각 셀에 분리하여 추출합니다.
여러줄(줄바꿈)로 입력된 문자열을 각각의 셀에 나눠 출력합니다.
줄바꿈(Alt+Enter)를 띄어쓰기 등으로 대체하여 한줄의 문자열로 변환합니다.
문자와 숫자가 섞인 문자열에서 숫자만 추출합니다.

댓글 남기기

avatar
  현재 페이지 댓글알림 신청  
알림 설정