Home > 엑셀 함수 > 텍스트 함수 > 엑셀함수 REPLACE, SUBSTITUTE – 텍스트 바꾸기

엑셀함수 REPLACE, SUBSTITUTE - 텍스트 바꾸기

엑셀에서 텍스트(문자열)의 일부를 바꿔주는 함수는 REPLACESUBSTITUTE가 있다.

둘다 텍스트를 바꿔주는 함수이지만 기능이 약간 다르다.

REPLACE는 지정된 위치로부터 정한 길이만큼 바꿔주고 SUBSTITUTE는 위치와 길이를 지정하지 않고 그냥 바꿀 문자를 지정해주면 바꿔준다.

실습용 엑셀파일 다운로드 : 엑셀함수_REPLACE_SUBSTITUTE_텍스트바꾸기.xlsx

1. REPLACE

구문(Syntax)

REPLACE(old_text, start_num, num_chars, new_text)

텍스트의 일부를 지정된 길이만큼 다른 텍스트로 바꾼다.

인수 :
old_text : 바꾸기 전 전체 텍스트
start_num : 바꿀 텍스트의 시작위치
num_chars: 바꿀 텍스트의 길이(갯수)
new_text: 바꾼 후의 새 텍스트

아래 그림과 같이 "종로구익선동" 텍스트에서 4번째부터 2글자를 "가회"로 바꾸려면 수식을 다음과 같이 입력한다.

=REPLACE("종로구익선동",4,2,"가회") => "종로구가회동"

 

셀의 값의 참조하여 바꿀 때는 다음과 같이 수식을 입력한다.

=REPLACE(B6,4,2,"가회")

엑셀함수 REPLACE, SUBSTITUTE - 텍스트 바꾸기

 

*참고 : REPLACEB함수도 있는데 더블바이트 문자(한글,일본어,중국어 등 글자 하나가 2바이트인 문자)가 포함되어 있으면 한 글자를 바이트단위로 인식하여 2자리로 계산하는 것만 다르고 REPLACE함수와 사용법이 동일하다.

 

2. SUBSTITUTE

구문(Syntax)

SUBSTITUTE(text, old_text, new_text, [instance_num])

텍스트의 일부를 새로운 텍스트로 바꾼다.

인수 :
– text : 바꾸기 전 전체 텍스트
– old_text : 바꿀 텍스트
 new_text : 바꾼후의 새 텍스트
– instance_num : 동일한 텍스트가 여러 개 있을 경우 몇 번째를 바꿀 것인지 지정, instance_num을 생략하면 일치하는 모든 텍스트가 바뀌고, 지정하면 해당하는 위치에 있는 old_text만 바뀐다

동일한 텍스트는 모두 바꾸기

아래 그림과 같이 "인스타그램먹스타그램" 텍스트에서 "그램"을 찾아서 모두 "GRAM"으로 바꾸려면 수식을 다음과 같이 입력한다.

=SUBSTITUTE(B13,"그램","GRAM")

엑셀함수 REPLACE, SUBSTITUTE - 텍스트 바꾸기

결과를 자세히 살펴보자. 원래 텍스트에 "그램"이 두군데 있었는데 SUBSTITUTE함수는 두군데의 "그램"을 모두 "GRAM"으로 바꾸어 주었다.

 

동일한 텍스트 중에 지정된 위치에 있는 것만 바꾸기

아래 그림과 같이 "인스타그램먹스타그램" 텍스트에서 첫번째로 나타나는 "그램"을 찾아서 "GRAM"으로 바꾸려면 수식을 다음과 같이 입력한다.

마지막 인수가 1이다. 첫번째로 나타나는 것만 바꾸라는 뜻이다.

=SUBSTITUTE(B14,"그램","GRAM",1)

엑셀함수 REPLACE, SUBSTITUTE - 텍스트 바꾸기

 

두번째로 나타나는 "그램"을 찾아서 "GRAM"으로 바꾸려면 마지막 인수를 2로 바꿔준다.

=SUBSTITUTE(B15,"그램","GRAM",2)

엑셀함수 REPLACE, SUBSTITUTE - 텍스트 바꾸기

 

[ 엑셀 텍스트 함수 ]

“엑셀함수 REPLACE, SUBSTITUTE - 텍스트 바꾸기”의 2개의 댓글

  1. 안녕하세요
    엑셀 궁금한 점 남깁니다^^
    특정값이 들어간 값을 특정 단어로 변환시키는 점이 궁금한데요!
    예로
    lg 냉장고, 삼성 냉장고, 양문형 냉장고, 건조기. 세탁기 조건 중
    "냉장고"만 들어간 값들을 "냉장고"라는 값으로 변환시키는 좋은 엑셀 함수가..있을까요???

    1. 안녕하세요. comma로 구분된 값들중에서 "냉장고"라는 문자열이 포함된 값에서 다른 값을 지우고 "냉장고"만 남기라는 것으로 이해되는데요.
      우선 엑셀에서 리본메뉴 > 데이터 > 텍스트 나누기에서 comma로 구분하여 필드를 나누시고 찾기/바꾸기 메뉴(Ctrl+F)에서 찾을 내용에 "*냉장고*", 바꿀 내용에 "냉장고"를 입력 후 모두바꾸기 클릭하면 됩니다.
      감사합니다.

답글 남기기

이메일 주소를 발행하지 않을 것입니다.