엑셀 한 셀에 여러줄 나누기 공식 총정리 :: 엑셀 함수 공식

한 셀에 여러줄로 입력된 텍스트를 각각의 셀에 나눠서 출력하는 공식과 동작원리를 알아봅니다.

홈페이지 » 엑셀 한 셀에 여러줄 나누기 공식 총정리 :: 엑셀 함수 공식

한셀에 여러줄 나누기 공식 총정리 :: 함수공식 

여러줄 나누기 공식 목차 바로가기
사용 예제

한셀에 여러줄 나누기 공식 사용 예제

함수 공식
  • 첫째 줄
    = LEFT(셀, FIND(CHAR(10), 셀))
  • 중간 줄
    =MID(셀, FIND("|", SUBSTITUTE(셀, CHAR(10), "|", nr-1))+1, FIND("|", SUBSTITUTE(셀, CHAR(10), "|", nr)) - FIND("|", SUBSTITUTE(셀, CHAR(10), "|", nr-1))-1)
  • 마지막 줄
    = RIGHT(셀, LEN(셀) - FIND("|", SUBSTITUTE(셀, CHAR(10), "|", nr-1)))
  • 참고사항
    nr : 줄번호 입니다. (두번째줄=2, 세번째줄=3, ... )
    "|" : 만약 문자열에 "|" 문자가 사용되었을시, 다른 문자로 대체합니다. ("#", "^", 등..)
  • 사용방법

    공식을 복사/붙여넣기 한 뒤, '셀'은 실제 셀주소로, 'nr'은 해당 순번으로 찾아 바꾸기합니다.
공식 설명

엑셀 한셀에 여러줄 나누기 공식입니다. 하나의 셀에 여러줄로 입력된 텍스트를 각 셀에 나눠서 뿌려줍니다. 한셀에 두줄로 입력된 텍스트는 더욱 간단한 공식으로 줄바꿈 나누기를 할 수 있습니다.

홈페이지에서 제공해드리는 TEXTSPLIT 함수 추가기능을 사용하면 더욱 편리하게 원하는 구분자를 지정하여 실시간으로 텍스트 나누기를 할 수 있습니다. 자세한 내용은 아래 TEXTSPLIT 추가기능 포스트를 확인하세요.

예제파일 다운로드

관련 기초함수
텍스트 문자열의 처음부터 지정한 개수의 문자를 반환합니다.
문자열의 특정 위치부터 원하는 문자수만큼의 텍스트를 반환합니다.

엑셀 여러줄 나누기 공식 알아보기

공식의 동작원리

첫째줄과 마지막줄을 구하는 공식은 비교적 간단합니다. 따라서 '가운데 줄'을 구하는 공식을 예제로 하나씩 알아보겠습니다.

  1. CHAR 함수는 입력한 숫자코드에 해당하는 문자를 반환합니다. 그중 "=CHAR(10)"은 텍스트 줄바꿈을 반환합니다.
    = "안녕하세요" & CHAR(10) & "오빠두입니다."
    = "안녕하세요."
    = "오빠두입니다." '// 줄바꿈 텍스트가 반환됩니다.
  2. SUBSTITUTE 함수'몇번째'에 위치한 '줄바꿈'을 "|" 기호로 변경합니다. 예를들어 두번째 줄바꿈을 "|" 기호로 변경할 경우 공식은 아래와 같습니다.
    = SUBSTITUTE($A$2, CHAR(10), "|", 2)
    = "안녕하세요<줄바꿈>오빠두입니다<줄바꿈>세번째줄<줄바꿈>네번째줄<줄바꿈>마지막줄"
    = "안녕하세요<줄바꿈>오빠두입니다|세번째줄<줄바꿈>네번째줄<줄바꿈>마지막줄"
  3. FIND 함수 "|" 기호의 위치를 찾습니다. 예를 들어 셋째줄을 출력할 경우, 문자열에서 "|" 기호의 두번째, 세번째 위치를 검색합니다.
    = "안녕하세요|오빠두입니다|세번째줄|네번째줄|마지막줄"

    즉, 두번째 기호와 세번째 기호  사이의 문자열을 추출하면 셋째 줄의 문자열이 됩니다.

    = "안녕하세요|오빠두입니다|세번째줄|네번째줄|마지막줄"

    따라서 각 FIND 함수를 사용하여 각 두번재, 세번째 위치한 줄바꿈 위치를 검색합니다.

    = FIND("|", SUBSTITUTE($A$2, CHAR(10), "|", 2))
    = FIND("|", "안녕하세요<줄바꿈>오빠두입니다|세번째줄<줄바꿈>네번째줄<줄바꿈>마지막줄")
    = 13 '// 두번째 줄바꿈은 13번째에 위치합니다.
    = FIND("|", SUBSTITUTE($A$2, CHAR(10), "|", 3))
    = FIND("|", "안녕하세요<줄바꿈>오빠두입니다<줄바꿈>세번째줄|네번째줄<줄바꿈>마지막줄")
    = 18 '// 세번째 줄바꿈은 19번째에 위치합니다.
  4. MID 함수를 사용해 여러줄 나누기 공식을 마무리합니다.
    = MID($A$2, FIND("|", SUBSTITUTE($A$2, CHAR(10), "|", 2))+1, FIND("|", SUBSTITUTE($A$2, CHAR(10), "|", 3)) - FIND("|", SUBSTITUTE($A$2, CHAR(10), "|", 2))-1)
    = MID($A$2, 13+1, 18 - 13 -1)
    = MID($A$2, 14, 4)
    = MID("안녕하세요<줄바꿈>오빠두입니다<줄바꿈>세번째줄<줄바꿈>네번째줄<줄바꿈>마지막줄", 14, 5)
    = "세번째줄"
현재 페이지 댓글알림 신청
알림 설정
guest
0 Comments
Inline Feedbacks
View all comments
0
여러분의 생각을 댓글로 남겨주세요.x