1일1함수 (37) SUBSTITUTE 함수 : 특정 문자열을 다른 문자열로 바꾸기

지난 번에는 REPLACE함수를 사용해서, 특정위치에 있는 문자열을 다른 문자열로 바꾸었습니다. 오늘 다루게 될 SUBSTITUTE함수는 특정한 문자열을 찾아, 다른 문자열로 바꾸는 함수입니다.

구문 SUBSTITUTE(text,old_text,new_text,instance_num) text  문자를 대체할 텍스트가 포함된 셀의 참조 또는 텍스트입니다. old_text  바꿀 텍스트입니다. new_text  old_text를 대신할 텍스트입니다. instance_num  text에서 몇 번째에 있는 old_text를 new_text로 바꿀 것인지를 지정하는 수입니다. instance_num을 지정하면 해당하는 위치에 있는 old_text만 바뀝니다. 그렇지 않으면 모든 old_text가 new_text로 바뀝니다.

다음과 같이 전화번호의 국번표시를 바꾸는 함수를 생각해 봅시다.

SUBSTITUTE를 적용하니까 앞의 괄호는 바뀌지 않았습니다. 위의 수식으로 나온 결과값에다 한번 더 SUBSTITUTE를 적용하여, 앞에 있는 괄호를 공백으로 바꾸는 수식을 만들어 봅시다.

사실, 이것은 단축키 CTRL+F를 누르면 나오는 "찾기-바꾸기"와 유사한 기능입니다. CTRL+F를 눌러서 ")"를 찾아 "–"로 바꾸어주고 난뒤, "("를 찾아 빈칸으로 바꾸는 작업을 해도 됩니다. 하지만, CTRL+F로는 할 수 없는 경우도 있습니다.

위의 작업을 거꾸로 한다고 가정합시다. 즉, 02-222-2222라는 전화번호를 (02)-222-2222로 바꾼다고 해봅시다.

위의 그림처럼 CTRL+F를 하여 찾을내용을"–", 바꿀내용을" )"로 입력한 뒤 "모두바꾸기"하면 02)222)2222가 됩니다. 물론, 02뒤에 있는 "–"에 커서가 오면 "바꾸기"를 누르고, 그 다음 "–"에 커서가 오면 "다음찾기"를 눌러서 넘어가도록 수작업으로 하면 가능합니다만, 우리는 엑셀작업에서는 항상 엄청나게 많은 데이터를 처리한다고 생각해야합니다. 10만개의 전화번호를 그런식으로 일일이 바꾸어야한다고 생각하면 끔찍하지 않습니까?
맨위의 구문에서 설명한 인수중에서, instance_num  부분을 이용합니다. 위의 설명에도 있지만, 찾을 내용중에서 몇번째에 나오는 내용을 바꿀것인지를 지정해주는 인수입니다.

위 수식의 의미는 다음과 같습니다.

(02)-222-2222처럼 앞의 괄호를 삽입하려면 여러가지 방법이 있겠습니다만 간단히 문자열을 연결하는 &연산자를 사용하면 됩니다.
즉, 최종적인 수식은 다음과 같이 되겠습니다.

 2009/05/24 - [컴퓨터/엑셀] - 1일1함수 (36) REPLACE 함수 : 지정한 위치에서 문자열 바꿈
2009/03/27 - [컴퓨터/엑셀] - 엑셀의 연산자