엑셀(EXCEL) Substitute / REPLACE 함수로 문자안에 글자를 검색하여 변경하기

1. SUBSTITUTE 함수

 

SUBSTITUTE(text, old_text, new_text, [instance_num]) : 글자(text) 안에 있는 old_text를 찾아서 new_text로 변경합니다. old_text를 못찾는 경우에는 그냥 글자(text)를 그대로 출력합니다.

 

• text : 문자를 대체할 텍스트가 포함된 셀의 참조 또는 텍스트입니다.
• old_text : 바꿀 대상 텍스트입니다.
• new_text : old_text를 변경할 텍스트입니다.
• [instance_num] : old_text가 여러 개 있는 경우 몇번째 글자만 바꿀지를 지정합니다. 비우면 모든 문자를 변경합니다.

바꿀 텍스트를 ""로 입력하면 해당 문자를 삭제하는 효과가 있습니다.

 

(예시)

원본	결과	함수	설명
ABC-가나-ABA-가너	KBC-가나-KBK-가너	=SUBSTITUTE(B5,"A","K")	글안에 A를 K로 변경합니다.
ABC-가나-ABA-가너	ABC-가나-KBA-가너	=SUBSTITUTE(B6,"A","K",2)	2번째 A만 K로 변경합니다.
ABC-가나-ABA-가너	BC-가나-B-가너	=SUBSTITUTE(B7,"A","")	A를 삭제합니다.
ABC-가나-ABA-가너	BC-나-B-너	=SUBSTITUTE(SUBSTITUTE(B8,"A",""),"가","")	A를 삭제하고, 가도 삭제합니다.

 

320x100

 

 

2. REPLACE 함수

 

REPLACE(old_text, start_num, num_chars, new_text) : 문자열(old_text) 안에 특정 위치의 문자를 숫자만큼 new_text로 변경합니다.

 

• old_text : 일부 문자를 바꿀 문자열입니다.
• start_num : old_text에서 new_text로 바꿀 문자의 위치입니다.
• Num_chars : old_text에서 new_text로 변경할 문자의 수입니다.
• new_text : old_text에 바꿔 넣을 새 문자열입니다.

 

변경한다고 했지만 정확하게는 텍스트안에 지정한 위치와 갯수의 문자를 지우고 new_text로 변경하는 효과가 있습니다.

원본보다 글자수가 작거나 많아도 그자리에 적을 수 있습니다.

 

동작순서

 

(예시)

원본	결과	함수	설명
ABC-가나-ABA-가너	ABC-사아자-ABA-가너	=REPLACE(B5,5,2,"사아자")	5번째부터 2개 "가나"를 "사아자"로 변경
ABC-가나-ABA-가너	#-가나-ABA-가너	=REPLACE(B6,1,3,"#")	맨 앞에 3글자를 "#"으로 변경
ABC-가나-ABA-가너	ABC-가나-ABA#	=REPLACE(B7,LEN(B26)-2,3,"#")	글자 맨 마지막 3글자를 "#"으로 변경
ABC-가나-ABA-가너	가나-ABA-가너	=REPLACE(B8,1,4,"")	맨 앞 4글자를 삭제
ABC-가나-ABA-가너	ABC-가나-ABA	=REPLACE(B9,LEN(B28)-2,3,"")	글자 맨 마지막 3글자를 삭제