들어가며
지난 시간에 엑셀의 수식과 함수, 숫자를 다루는 대표적인 함수 5가지에 대해 살펴보았습니다. 숫자와 관련된 함수를 알아보았으니 텍스트(문자열)를 다루는 함수도 보아야겠죠? 이번 시간 내용을 잘 익혀두면 문자열 중에서 일부를 추출하고, 텍스트를 나누거나 합치는 등 텍스트를 자유자재로 다룰 수 있게 됩니다. 기대되시죠? 출발합니다.
텍스트 함수 삼형제
숫자를 다룰 때 맨 먼저 배웠던 함수가 SUM, AVERAGE 등이었다면, 텍스트(문자열)를 다룰 때 가장 기본이면서 중요한 함수가 LEFT, RIGHT, MID 함수입니다. 이름에서 짐작할 수 있듯, 문자열의 일부를 추출하는 함수입니다. 어디서부터(왼쪽이나 오른쪽이냐 중간이냐) 추출할 것인지가 다를 뿐이죠.
기본 사용 방법
LEFT, RIGHT, MID 함수의 기본 사용법은 다음과 같습니다.
=LEFT(문자열, 숫자) : '문자열' 왼쪽으로부터 지정한 '숫자'만큼 추출
=RIGHT(문자열, 숫자) : '문자열' 오른쪽으로부터 지정한 '숫자'만큼 추출
=MID(문자열, 시작, 숫자):'문자열'의 '시작' 위치로부터 지정한 '숫자'만큼 추출
주민등록번호 앞자리 추출하기
예를 들어, 주민등록번호에서 년월일 부분을 추출하려면 어떻게 하면 될까요? '앞'이니까 '왼쪽'부터 시작하면 되겠고, 년월일이니까 6글자를 가져오면 되겠죠? 이것을 엑셀이 알아들을 수 있도록 함수로 표현하면 다음과 같이 됩니다.
=LEFT(B3,6)
F3 셀의 채우기 핸들을 아래로 드래그하여 수식을 채워넣기 합니다. 여기서 한 가지 유의할 점! 수식의 결과는 숫자 형태지만 실제로는 문자열 데이터입니다(왼쪽 정렬되어 있음에 주목). 문자열 함수로 어떤 작업을 하면 그 결과는 문자열이다, 라는 사실을 기억해 두세요.
주민등록번호 뒷자리 추출하기
주민등록번호에서 뒷 자리를 추출해 볼까요? 주민등록번호의 특징을 잘 생각해보면 쉽게 답을 떠올릴 수 있습니다. 이번에는 '뒷자리'니까 '오론쪽'부터 시작하면 되겠고, 뒷자리는 7글자를 가져오면 되겠죠? 함수 이름과 '숫자' 인수만 살짝 바꿔주면 됩니다.
G3 셀의 채우기 핸들을 아래로 드래그하여 수식을 채워넣기 합니다. RIGHT 함수실행 결과도 문자열임을 알 수 있습니다.
주민등록번호에서 성별에 해당되는 문자 추출
주민등록번호를 보면 '성별'을 알 수 있다는 것은 삼척동자도 압니다. 주민등록번호의 뒷자리 첫 번째 숫자를 보면 알 수 있죠. 그렇다면 엑셀에게 "주민등록번호에서 성별 문자를 추출"하도록 일을 시키려면 어떻게 해야 할까요?
패턴이나 규칙을 잘 설명하면 됩니다. 여기에는 어떤 규칙(또는 패턴)이 있나요? 몇 가지를 찾을 수 있겠지만 가장 눈에 띄는 특징은 주민등록번호의 8번째(- 포함) 글자라는 사실입니다. 즉, 주민등록번호의 8번째부터 시작해서 1글자만 추출하면 됩니다. 그렇죠? 이것을 수식으로 표현하면 이렇게 됩니다.
=MID(B3,8,1)
수식을 복사해서 나머지 셀의 결과도 구합니다.
성별 문자를 성별로 바꾸기
탄력이 붙은 김에 한 가지 더 해보도록 합니다. MID 함수를 사용하여 주민등록번호에서 성별에 해당하는 문자를 추출해 보았습니다. 이 숫자를 성별로 바꾸는 겁니다. 가만히 보니 주민등록번호가 이상하군요. 챗GPT를 이용해서 생성한 자료인데 이런 주민등록번호는 있을 수가 없으므로 아래와 같이 수정했습니다. 여담입니다만, 챗GPT의 결과물은 반드시 검증해야 합니다.ㅎㅎ
(1) I3 셀을 선택하고 =choose()라고 입력합니다. 수식 입력줄 왼쪽에 있는 [함수 삽입] 아이콘을 클릭합니다.
(2) [함수 인수] 대화상자가 나타납니다. [index_num] 인수 입력 창을 클릭하고 mid()를 입력합니다. 그러면 수식 입력줄에 =CHOOSE(mid())라는 수식이 작성됩니다. 함수명 뒤에 빈 괄호 한 쌍 ()을 반드시 입력해야 합니다.
(3) 수식 입력줄에서 MID 함수의 괄호 안을 클릭하면 MID 함수의 인수를 지정할 수 있는 상태가 됩니다. 함수의 각 인수를 다음과 같이 지정합니다. 주민등록번호의 8번째 글자부터 1글자를 추출하기 위한 수식입니다.
(4) 수식 입력줄에서 CHOOSE 함수를 선택하면 CHOOSE 함수 인수 대화상자가 나타납니다. MID 함수로 추출한 값이 1이나 3이면 "남자", 2나 4이면 "여자"가 되도록 Value1 ~ Value4 인수를 입력합니다. 문자열 앞뒤의 따옴표는 입력하지 않아도 자동으로 붙습니다. 짝을 맞춰서 인수를 잘 입력하세요.
(5) [확인] 버튼을 클릭하면 결과가 표시됩니다. 수식 입력줄을 보면 이런 수식이 입력되어 있습니다.
=CHOOSE(MID(B3,8,1),"남자","여자","남자","여자")
[참고] 어떤 방식으로 수식을 작성하는 것이 좋을까?
함수에 익숙하지 않다면 각 함수의 인수가 생소하고, 여러 함수를 중첩해서 사용하는 것이 부담스럽습니다. 하지만 [함수 삽입] 아이콘을 적절히 이용하면 길고 복잡한 수식도 편리하게 작성할 수 있습니다. 다만, 경력(?)이 좀 더 쌓이고 함수에 익숙해지면 셀이나 수식 입력줄에서 직접 작성하는 것이 더 편해지는 단계가 옵니다. 그때까지는 편리한 방법을 사용하세요.
흩어져 있는 텍스트 합치기
만남이 있으면 헤어짐이 있고, 블랙홀이 있으면 화이트홀도 예상할 수 있는 것처럼, 뭔가를 추출하는 함수가 있으면 합치는 함수도 당연히 있어야겠죠? 2가지 방식으로 해결할 수 있습니다.
방법 1: CONCATENATE 함수 사용
엑셀에서 무언가를 연결하는 대표적인 함수로 CONCATENATE가 있습니다.
=CONCATENATE(text1, text2, text3,...)
합치려는 문자열(또는 셀)을 쉼표로 구분하며, 255개까지 지정할 수 있습니다. 앞자리와 뒷자리로 나누었던 주민등록정보를 이번에는 하나로 합쳐 볼까요.
(1) 결과를 표시할 셀(여기서는 J3 셀)을 선택합니다.
(2) =concatenate()라고 입력하고 [함수 삽입] 아이콘을 클릭합니다.
(3) [함수 인수] 대화상자에서 Text1 인수 입력란을 클릭한 다음, 주민등록번호 앞자리가 있는 F3 셀을 선택합니다.
(4) Text2 입력란에는 "-", Text3 입력란에는 G3 셀을 각각 지정합니다.
(4) [확인] 버튼을 클릭하면 결과가 표시됩니다. J3 셀에는 다음과 같은 수식이 작성되어 있습니다.
=CONCATENATE(F3,"-",G3)
(5) 채우기 핸들을 이용하여 수식을 다른 셀로 채워넣기 합니다.
방법 2: & 연산자 사용
& 기호를 사용할 수도 있습니다. 정식 명칭은 앰퍼샌드(ampersand)이며, 엑셀에서는 'AND 연산자'라고 보통 부릅니다. 다음 2개 수식의 결과는 같습니다.
[방법 1] =CONCATENATE(F3,"-",G3)
[방법 2] =F3&"-"&G3
여건이 되는 분들은 다음 영상도 참고하세요. CONCATENATE 외의 다른 함수나 기능을 이용해서 텍스트를 합치는 방법을 알 수 있습니다.
초보 탈출 ㅡ 일부 문자열 암호화하기
개인정보가 들어있는 민감한 데이터는 관리에 특히 유의해야 합니다. 불가피하게 공유해야 한다면 일부 정보를 암호화하거나 변경하는 것이 좋습니다. 이럴 때 사용할 수 있는 함수로 REPLACE가 있습니다. REPLACE는 문자열의 일부를 지정된 문사 수만큼 다른 텍스트 문자열로 바꾸는 함수입니다.
=REPLACE(old_text, start_num, num_chars,new_text)
' old_text: 바꾸려는 문자열(필수 요소)
' start_num: old_text에서 new_text로 바꿀 문자열의 위치(필수 요소)
' num_chars: old_text에서 new_text로 바꿀 문자의 수(필수 요소)
' new_text: old_text에 바꿔 넣을 새 문자열(필수 요소)
인수가 4개나 되므로 복잡해 보이지만 예제를 통해 살펴보면 이해하기 쉽습니다.
이름 가운데 글자를 O으로 표시하기
C열에 있는 이름의 가운데 글자를 O으로 표시해 보겠습니다.
(1) 변경된 이름을 입력할 셀(여기서는 B3 셀)을 선택합니다.
(2) =replace()라고 입력하고 [함수 삽입] 아이콘을 클릭합니다.
(3) [함수 인수] 대화상자에 인수를 입력(또는 지정)합니다. A3 셀에 있는 내용의 2번째 문자열을 "O"으로 바꾸는 것이므로 다음 그림과 같이 지정합니다. [함수 인수] 대화상자를 사용하는 경우, 문자열은 큰 따옴표를 입력하지 않아도 자동으로 삽입됩니다.
=REPLACE(A3,2,1,"O")
(4) [확인] 버튼을 누르면 결과가 표시됩니다. 채우기 핸들을 이용하여 다른 셀에도 수식을 채워 완성합니다.
주민등록번호 뒷자리를 ******로 표시하기
주민등록번호는 더 말할 것도 없이 민감한 정보입니다. 주민등록번호에서 대시(-) 뒷자리를 **로 바꿔보겠습니다.
(1) 변경된 이름을 입력할 셀(여기서는 D3 셀)을 선택합니다.
(2) =replace()라고 입력하고 [함수 삽입] 아이콘을 클릭합니다.
(3) [함수 인수] 대화상자에 인수를 입력(또는 지정)합니다. C3 셀에 있는 내용 중에서 8번째 문자열을 ******로 바꾸는 것이므로 다음과 같이 지정합니다.
(4) 여기서도 마찬가지로 문자열에 큰 따옴표는 붙이지 않아도 됩니다. [확인] 버튼을 누르는 순간 자동으로 수식이 완성됩니다.
=REPLACE(C3,8,6,"******")
(5) 다른 셀에도 수식을 채워넣기 하여 완성합니다.
수식을 값으로 바꾸기
B와 D열에 추가된 정보는 각각 A와 C열의 정보를 토대로 합니다. 사물에 종속된 그림자 같은 존재라고 할까요. 따라서 A나 C열을 삭제할 경우 #REF!라는 달갑지 않은 오류 메시지가 나타납니다.
이런 현상을 막으려면 수식이 아닌 값으로 붙여넣기를 해야 합니다.
(1) 함수의 결과로 표시된 영역(여기서는 B3:B22 셀)을 범위로 지정하고 Ctrl + C를 눌러 복사합니다.
(2) 선택된 영역을 해제하지 않고 우클릭한 다음 [값] 아이콘을 클릭합니다.
(3) 이제 지정한 열은 수식이 아닌 값으로 변환되었으므로 A열을 삭제하더라도 이상없이 표시됩니다.
(4) 같은 방식을 사용하여 주민등록번호에 대해서도 처리하여 완성합니다.
나가며
가장 기본적이면서 중요한 텍스트 함수 삼형제와 CONCATENATE, REPLACE 함수 등을 이용하여 다양한 작업을 처리할 수 있게 되었습니다. 축하합니다.
'Excel' 카테고리의 다른 글
| Excel의 FV 함수로 미래 가치를 계산하는 방법 (9) | 2024.02.23 |
|---|---|
| Excel에서 중복 값에 강조 표시하는 간단한 방법 2가지 (6) | 2024.02.21 |
| Excel 상태 표시줄에 합계가 표시되지 않을 때 해결 방법 4가지 (4) | 2024.02.19 |
| Copilot Pro를 사용하여 Excel 작업 개선하는 방법(코파일럿도 가능) (3) | 2024.02.16 |
| Excel에서 숫자 범위를 만드는 3가지 방법 (6) | 2024.02.15 |
