엑셀이 수식과 표를 위주로 다루는 프로그램이긴 하지만 문자열을 다루는 데도 매우 뛰어난 기능을 제공합니다. 실무에서 엑셀 작업을 하다보면 문자열과 조합해서 사용하는 경우가 많기 때문에 고급 스프레드시트를 작성하기 위해서는 문자열 관련 기능들을 필수적으로 알고 있어야 합니다. 엑셀이 문자열 관련 함수를 많이 제공하고 있는 것도 그만큼 중요하기 때문이랍니다.
Tip - 한 셀에 입력할 수 있는 글자 수?
엑셀 2010에서는 한 셀에 최대 32,000자를 입력할 수 있습니다. 사실 의미없는 숫자겠죠? ^^
문자 코드(Character Code)
화면에 출력되는 모든 문자는 코드 번호가 할당되어 있습니다. 엑셀이 실행되는 MS윈도우 시스템에서는 기본적으로 표준 ANSI 문자코드를 사용하게 되는데, ANSI 문자코드는 1에서 255까지 모두 255 문자가 포함되어 있습니다. (사용하는 폰트의 종류에 따라서 코드표에 해당하는 문자는 모두 틀릴 수 있습니다.) 이러한 문자 코드를 다루는 함수는 CODE와 CHAR 두 함수가 있습니다. CODE 함수는 입력한 문자열에 대한 코드 번호를 찾아주고, CHAR는 문자코드를 입력해서 해당 문자를 표시해주는 함수입니다. “가”는 현재 윈도 시스템에서 어떤 코드 번호를 가지고 있는 한번 찾아보겠습니다.
“가”라는 글자는 시스템 코드 번호가 “45217”이 할당되어 있는 것을 알 수 있습니다. 그렇다면 =CHAR(45217) 이라는 수식을 입력하면 “가”가 표시되겠죠?
당장은 필요없어 보일 지도 모르지만, 셀 주소를 참조로 해서 코드표를 만든다던지, 일정한 규칙을 갖는 코드표의 경우 다양하게 변형해서 문자열을 출력할 필요가 있을 때 사용된답니다.
특수 문자의 입력
함수에 관련된 내용은 아니지만 엑셀에서 특수 문자를 입력하는 방법은 크게 3가지가 있습니다.
- 기호 대화 상자 이용
- 자음에 해당하는 특수 문자 입력
- 엑셀 내장 특수 문자로 자동 변환
기호 대화상자는 [삽입-기호]를 선택하면 표시되는 대화상자에서 원하는 특수문자를 선택하면 쉽게 입력할 수 있습니다.
자음을 이용해서 입력하는 방법은 키보드로 ㄱ 부터 ㅎ 사이의 자음을 입력한 후 키보드 [한자]키를 눌러 특수 문자를 삽입하는 것입니다. (기초 강좌에서 다뤘던 내용입니다.)
마지막으로 엑셀은 사용자가 입력했을 때 자동적으로 특수기호로 변경해주는 문자들이 있는데, 예를 들어 (c) 라고 입력하면 자동으로 ⓒ로 변경을 해준답니다. 마찬가지로 ™을 입력하면 ™ 기호로 자동 변환해줍니다. 이렇게 자동 변환해주는 문자열이나 기호는 [파일-Excel 옵션-언어교정-자동 고침 옵션]을 선택하면 볼 수 있습니다. (여러분들이 직접 추가하거나 삭제할 수도 있습니다.)
문자열 합치기 – &
기초 강좌에서 간단히 다뤘듯이 엑셀에서 &기호는 수식에서 문자열을 합치는 연산자입니다. &를 이용해서 기초 과정에서 간단히 배운 내용인데 다음 그림은 CHAR 함수를 조합해서 문자열을 합친 결과입니다.
수식만 보고도 바로 이해되시죠? 아니 이게 왜 이래? 하시는 분들은… 음… Back 하셔서 다시 기초부터! ^^
문자 반복해서 효과내기 – REPT
REPT 함수는 지정한 글자를 원하는 횟수만큼 반복해서 표시할 때 사용합니다. =REPT(“하”, 5) 를 입력하면 “하하하하하”가 표시된다는거죠. 아래 예제를 보시죠.
C2셀의 평가 점수를 반복회수 인수로 잡아주고, REPT 함수로 *를 채우게 만들었습니다. 간단한 차트의 효과가 나오죠? 그런데 단순히 상대적 비교를 위한 반복에서 저렇게 점수 대로 반복을 시키면 화면이 비효율적일 수 있습니다. 이럴 경우 최소 점수인 20을 10으로 나눠서 반복시키면 *의 표시회수를 줄이면서도 효과적인 비교 자료를 만들 수 있을겁니다.
잠깐!
아직도 함수 수식 외울려고 애쓰시는 분들은 안계시겠죠? 엑셀의 기법이나 많이 사용하는 방법 들에 대해서는 제가 안내를 해드릴테니까 여러분들은 실무에 다양하게 활용할 수 있도록 이해!! 를 하셔야 합니다.
문자열 길이 구하기 – LEN
LEN 함수는 길이를 뜻한 Length 에서 이름을 따온 함수입니다. 셀에 포함된 문자열의 길이를 반환해주는데요, A1셀에 “가나다”가 입력되어 있다면 =LEN(A1)은 3을 반환하게 되는겁니다.
문자열 추출하기 – LEFT/RIGHT/MID
셀에 입력된 문자열의 일부를 추출하는 작업은 비교적 자주 일어납니다. 이런 기능을 할 수 있는 함수가 LEFT/RIGHT/MID 인데 함수명에서도 알 수 있듯이 왼쪽/오른쪽에서 몇번째까지 추출, 그리고 MID 함수는 몇번째에서 시작해서 몇번째까지의 문자열을 추출하는 것입니다.
문자열 함수는 직관적인 것들이 많아서 어렵지 않으시죠?
주민번호로 성별 구하기
문자열 함수를 공부할 때 국내에서 자주 사용하는 예제가 바로 주민번호로 성별/나이 구하기입니다. 그래서 안해볼 수가 없겠죠? ^^ 지금까지 배운 것만으로도 충분히 여러분들 스스로 수식을 만들 수 있습니다. 수식을 만들기 전에 먼저 생각을 해봐야겠죠? 주민번호는 형식이 정해져 있고, 남녀는 -뒤의 숫자가 1인지 2인지로 구분할 수 있습니다. 2000년 이후에 출생한 꼬마들은 3,4로 구분을 하게 되죠. 어떤 방법이 있을까요?
만약 주민번호 자릿수가 정해져 있지 않다면 조금 더 복잡해질겁니다. 문자열에서 “-“를 찾아(FIND, SEARCH 함수)내고, 그 바로 다음 숫자를 판단해서 성별을 표시할 수 있겠죠. 그런데 주민번호는 자릿수가 정해져 있어서 매우 간단하게 처리를 할 수 있을겁니다. 성별을 나타내는 1, 2가 입력된 문자열 위치는 8번째입니다. 그렇다면 MID 함수를 사용해서 1인지 2인지를 찾을 수 있겠죠? 그 후 IF 함수를 사용해서 1이면 남자, 아니면 여자를 반환하면 간단하게 해결할 수 있습니다. (물론 3, 4의 경우도 따지는 함수식도 만들 수 있겠죠.)
나이는 어떻게 구할 수 있을까요? 더 간단하죠? 현재 년도를 구해서, 주민번호 앞 2자리만 빼주면 끝입니다!
반드시 직접 수식을 만들어 보세요.
감 잡고 계신거 맞죠?!
