한 셀에 몇 줄이 입력되었는지 확인(CHAR, SUBSTITUTE 함수)

안녕하세요.

 

먼저 그림을 보고 무슨 일인지 이야기를 하죠.

[B2] 셀에 입력된 내용을 보면 한 셀에 줄바꿈을 해서 3줄의 내용이 입력되어 있습니다.

지금 사용하고 있는 예제 파일은 기능 소개를 위해 모든 입력된 셀이 3줄로 되어 있지만 실제 사용할 때 셀마다 줄 수가 다를 수 있겠죠.

 

입력된 셀이 몇 개인지 헤아리려면 COUNT 함수나 COUNTA 함수를 이용하면 되겠지만, 한 셀 안에서 처리해야 되는 일이라 다른 방법을 찾아 봐야 합니다.

 

이번 강좌는 의식의 흐름에 따라 어떻게 문제 해결 방법을 찾는지 소개합니다.

중간에 멈추지 마시고 강좌 끝까지 다 보시고, 생각하시길 권합니다.

 

실습 파일 다운로드

몇줄인지확인하기_실습파일.xlsx

0.01MB

 

첫번째 아이디어 <Enter>가 몇 번 눌러졌는지 알아보자

 

얼른 떠오르는 아이디어가 줄바꿈을 하려면 <Enter>키를 눌러야 하니까 <Enter>를 몇 번 눌렀는지 확인하면 줄 수를 확인할 수 있다는 것입니다.

 

입력된 글자 중 A를 찾으려면 키보드에서 A를 입력하면 되지만, <Enter>키는 다르죠!

줄바꿈을 나타내겠다고 <Enter>를 누르면 입력 완료로 인식해 버리기 때문입니다.

 

키보드의 모든 키는 코드표로 정리되어 있습니다.

 

영어 대문자 A는 65, 소문자 a는 97번 입니다.

컴퓨터에서 표현되는 모든 것에 코드번호가 붙어 있습니다.

어떤 글자의 코드 번호가 궁금하면 CODE 함수로 확인해 볼 수 있습니다.

'가'의 코드번호는 45217번 입니다.

 

<Enter>는 10번 입니다.

 

코드 번호를 문자로 표현하는 함수는 CHAR 함수입니다.

 

CODE 함수와 CHAR 함수는 서로 반대 역할을 하는거죠.

CODE 함수는 코드 번호를 알려주고, CHAR 함수는 코드 번호에 해당하는 글자를 나타내는 겁니다.

 

CHAR(10)이라고 하면 <Enter>를 나타내죠.

 

이제 문자열에서 특정 글자를 찾는 함수가 필요한데, 이건 FIND 함수입니다.

찾으려는 글자가 몇번째 위치에 있는지 나타내는 함수입니다.

찾는 글자가 몇 개 있는지 알려주는 함수가 있으면 그걸 쓰면 되겠지만, 그런 함수는 없습니다.

 

FIND 함수를 이용해 앞에서부터 <Enter>를 찾고, 다시 두번째 <Enter>를 찾고, 세번째 <Enter>를 찾고, 이런 식으로 계속 찾아서 마지막 <Enter>를 찾은 다음 총 몇 개인지 헤아려보면 됩니다.

 

[F2] 셀에 첫번째 <Enter>가 있는 위치를 찾아보죠.

=FIND(CHAR(10), B2)

Find_text 인수에 찾을 글자인 CHAR(10)을 입력합니다.

Within_text 인수에 [B2] 셀을 지정합니다.

Start_num 인수는 시작할 위치를 나타내고, 생략하면 1부터 시작합니다.

 

첫번째 <Enter>는 12번째 위치에 있습니다.

이제 두번째 <Enter>의 위치를 찾아보죠.

첫번째 <Enter>의 위치가 12번째니까 13번째부터 다시 찾아보면 됩니다.

 

이번 수식은 두번째 위치를 찾는 것 뿐만 아니라 세번째, 네번째 <Enter> 위치를 찾는 수식을 만들 수 있도록 작성합니다.

수식을 만들어 채우기 기능으로 채우면 동작하도록 절대참조, 상대참조를 적절히 사용합니다.

=FIND(CHAR(10), $B$2, F2+1)

두번째 <Enter>는 22번째 위치에 있군요.

이 예제에서는 모든 데이터가 <Enter>를 두 번 입력해 세 줄로 되어 있지만, 다섯줄까지 <Enter>가 있는 경우를 생각해 [G2] 셀의 수식을 [J2] 셀까지 채우겠습니다.

채우기를 하면 오류 표시가 났지만 상관없습니다.

이유를 알고 있기 때문이죠.

오류가 난 이유는 [B2] 셀에 입력된 내용에는 <Enter>가 두 번만 있기 때문이죠.

두번째 위치 후에는 없는 <Enter>를 찾으려고 하기 때문에 나타난 오류입니다.

FIND 함수는 찾기 시작하는 위치 뒤에서 특정 문자가 없으면 오류 표시를 합니다.

<Enter>가 몇 번째 위치에 있는지를 찾았으니 이제 우리가 원래 찾으려고 했던 한 셀에 입력된 내용이 몇 줄인지 구해보죠.

[L2] 셀에 COUNT 함수를 이용해 [F2:J2] 셀 범위에 숫자가 몇 개 있는지 수식을 작성합니다.

=COUNT(F2:J2)

 

COUNT 함수로 구해진 숫자는 <Enter>가 몇 번 입력되었는가를 구한 값이고, 몇 줄인지를 구하려면 1을 더하면 됩니다.

=COUNT(F2:J2) + 1

이렇게 원하는 값을 구하긴 했지만, 이 방법은 좋아 보이지 않습니다.

값을 구하기 위해 여러 셀을 써야 하고, <Enter>가 5개 초과 들어 있는 경우는 구할 수 없고, 오류 메시지가 보여서 보기 싫습니다.

 

두번째 아이디어 <Enter>가 몇 개인지 알아보자

 

<Enter>키가 몇 번 눌러졌는지 알아보는 방법으로 문자열에서 <Enter>를 없애는 겁니다.

<Enter>가 포함된 문자열의 길이에서 <Enter>를 없앤 문자열의 길이를 빼면 <Enter>가 몇 개 있었는지 알 수 있겠죠.

 

LEN 함수를 이용해 문자열의 길이를 확인할 수 있습니다.

SUBSTITUTE 함수를 이용해 <Enter>를 없앨 수 있습니다.

 

한 번에 다 만들어 보죠.

=LEN(B2)-LEN(SUBSTITUTE(B2, CHAR(10), ""))

LEN(B2) 수식은 [B2] 셀에 입력된 전체 문자 길이를 파악합니다.

줄바꿈도 문자 하나로 칩니다.

 

SUBSTITUTE(B2, CHAR(10), "") 수식은 [B2] 셀에 있는 줄바꿈을 없앱니다.

그런 다음 LEN 함수로 길이를 구하고, 앞에서 구한 전체 길이에서 뺍니다.

 

몇 줄인지를 구하려면 구해진 값에 1을 더해야 합니다.

줄바꿈을 한 번 하면 두 줄이 되고, 두 번 했으면 세 줄이 됩니다.

 

=LEN(B2)-LEN(SUBSTITUTE(B2, CHAR(10), "")) + 1

 

 

 

첫번째 아이디어로 구한 방법은 실제 해결책이 되지 않습니다.

한 셀에 몇 줄이 입력되어 있는지 확인할 수 있으니 해결책이라고 할 수도 있지만 모양도 좋지 않고 6줄 미만 확인할 수 있다는 제한 사항이 있죠.

두번째 아이디어가 실제 해결책입니다.

 

첫번째 방법을 생각했기 때문에 두번째 방법도 생각할 수 있었던 겁니다.

 

엑셀의 기능을 이용해 주어진 업무를 처리하려면 한 번에 '짠' 하면서 해결책이 떠오르지 않습니다.

그런 사람들도 있기는 하다고 하더군요.

궁리를 하다 보면 이 방법, 저 방법을 생각하게 되고, 그러다 보면 해결방법을 찾을 수 있습니다.

 

요즘은 해결방법을 찾기 위해 검색 사이트를 이용하는 것이 아주 중요합니다.

 

내가 해결해야 하는 일이라면 다른 사람들도 고민하는 문제일 가능성이 많고, 그래서 해결책이 이미 있는 경우가 많죠.

더구나 그 방법을 정성 들여, 알아보기 쉽게 정리해 둔 블로그도 많습니다.

 

다만 문제는 사람들마다 해야 할 일이 조금씩 달라 딱 맞는 해결책은 없고, 내게 해결하려는 문제에 맞게 바꿔야 한다는 거죠.

 

그래서 엑셀 사용법을 배워야 합니다.

 

필요하신 분들께 도움이 되길 바라며 강좌는 마치겠습니다.

수고하셨습니다.