■ 목차
1. 함수 설명
2. Replace 함수의 구문 / 사용방법
3. Substitute 함수의 구문 / 사용방법
4. Replace 함수 사용해 보기
5. Substitute 함수 사용해 보기
6. Replace 함수 응용 - 위치가 가변적일 때 Find, Search 함수
7.Substitute 함수 응용 - 특정 문자의 개수 알아보기
** 핵심 요약
1. 함수 설명
- Replace 함수는 영어로는 바꾸다, 대체하다는 의미로 텍스트의 일부를 다른 텍스트로 바꾸는 함수입니다.
- 그리고 Substitute 함수도 동일한 의미이고 동일한 기능을 하지만 조금 다른 방식으로 사용하는 함수입니다.
- Replace 함수 : 위치와 변경할 문자의 개수를 지정하여 변경
- Substitute 함수 : 특정 문자를 찾아서 변경
- 기능은 유사하지만 방식이 조금다르고 텍스트 문자열을 바꾸고자 하는 경우 케이스별로 더 편리한 방법이 있기 때문에
- 2개의 함수가 어떻게 다르고 어떻게 사용을 할 수 있는지를 예제로 이해를 하시고
- 또한 바꾸고자 하는 텍스트의 위치가 유동적인 경우에는 Find, Search 함수와 같이 사용을 하기도 하니 이러한 경우의 사용방법도 알고 계시면 좋을 듯합니다.
2. Replace 함수의 구문 / 사용방법
- REPLACE(old_text, start_num, num_chars, new_text)
- old_text 필수 요소입니다. 일부 문자를 바꿀 문자열입니다.
- start_num 필수 요소입니다. old_text에서 new_text로 바꿀 문자의 위치입니다.
- num_chars 필수 요소입니다. old_text에서 new_text로 replace 할 문자의 수입니다.
- new_text 필수 요소입니다. old_text에 바꿔 넣을 새 문자열입니다.
- Replace 함수는 텍스트의 "특정 위치"에서 "지정한 개수"만큼의 문자를 다른 문자로 변경을 해주는 함수입니다.
- "위치를 기반으로 문자를 변경해 주는 함수"라고 이해를 하시면 됩니다.
- 첫 번째 인수 "old_text"는 기존 텍스트로 변경할 문자가 들어 있는 텍스트 혹은 해당하는 셀 참조 주소입니다.
- 두 번째 인수 "start_num"는 변경할 문자의 시작위치입니다. "몇 번째 문자부터 몇 개을 바꾼다"에서 "몇 번째"에 해당하는 정수입니다.
- 세 번째 인수 "num_chars"는 문자(characters) 개수(number)로 바꿀 문자의 개 수를 정수로 입력합니다.
- 네 번째 인수 "new_text"는 바꾸고자 하는 텍스트입니다.
- 위치를 기반으로 "몇 번째부터 몇 개의 문자를 바꿔줘!"라는 함수로 사용이 됩니다.
- Replace 함수는 위치를 기반으로 하기 때문에 사용하기 위해서는 우리가 바꾸고자 하는 위치에 대한 정보를 알고 있는 경우나
- 위치를 숫자로 알려주는 Find 함수, Search 함수 등과 같이 사용되는 경우가 많습니다.
3. Substitute 함수의 구문 / 사용방법
- SUBSTITUTE(text, old_text, new_text, [instance_num])
- text 필수 요소입니다. 문자를 대체할 텍스트가 포함된 셀의 참조 또는 텍스트입니다.
- old_text 필수 요소입니다. 바꿀 텍스트입니다.
- new_text 필수 요소입니다. old_text를 대신할 텍스입니다.
- instance_num 선택 요소입니다. text에서 몇 번째에 있는 old_text를 new_text로 바꿀 것인지를 지정하는 수입니다
- Substitute 함수는 텍스트(A)를 찾아서 다른 텍스트(B)로 변경을 해주는 함수입니다.
- 그리고 만약 찾고자 하는 텍스트가 여러 개가 있다면 기본 옵션으로는 모두 변경을 해주지만 몇 번째 것을 바꿀지를 지정하는 것도 가능합니다.
- 그래서 변경하고자 하는 텍스트의 문장 내의 위치와는 상관없이 변경이 가능하고 여러 개를 모두 변경하는 것도 가능합니다.
- 가장 유사하게 엑셀의 찾기/바꾸기 메뉴를 함수로 구현했다고 보시면 됩니다.
- 첫 번째 인수 "text"는 기존 텍스트로 변경할 문자가 들어 있는 텍스트 혹은 해당하는 셀 참조 주소입니다.
- 두 번째 인수 "old_text"는 찾고자 하는 문자로 수식에 직접 입력하는 경우 텍스트이기 때문에 항상 큰따옴표( " " )를 사용해 주셔야 합니다.
- 세 번째 인수 "new_text"는 변경하고자 하는 문자입니다.
- 네 번째 인수 "instance_num" 찾고자 하는 문자가 여러 개가 존재하는 경우 몇 번째의 old_text를 바꾸어 줄 것인지 지정하는 정수입니다. 생략하게 되면 모든 old_text를 new_text로 변경을 해주게 됩니다.
- 2개의 함수차이를 아시겠는지요? 중요한 것은 그래서 우리는 어떤 것을 사용해야 하는가입니다.
- Repalce 함수 : place(장소 → 위치) 위치를 기반으로 문자 변경, 위치를 알거나 Find, Search 함수로 위치를 찾을 수 있는 경우
- Substitute 함수 : A문자를 찾아서 B문자로 변경, 특정 문자를 변경, 삭제해야 하는 경우 사용
4. Replace 함수 사용해 보기
- 주민번호 뒷자리를 별표로 변경하는 경우에 대해서 Replace 함수를 사용해서 변경해보도록 하겠습니다.
- 이런 경우에는 변경을 해야 하는 위치를 정확하게 알 수 있기 때문에 Replac 함수가 적합하고
- 만약 Substitute 함수로하고자 한다면 적당한 방법이 나오지 않을 것 같네요.
- 이경우 A를 B로 변경하고 싶은데 A가 각각의 데이터(주민번호)마다 모두 다르기 때문에 수식으로 구현하기는 좀 어려울 듯합니다.
- 주민번호는 총 14자리입니다.
- 그리고 우리가 바꾸고 싶은 범위는 주민번호의 ① 9번째 숫자부터 ② 6개의 숫자이고 이를 6개의 별표인 ③ "******"로 변경을 해줘야 합니다.
- 그래서 수식은 " = replace(C3, 9, 6, "******")로 사용이 가능하며 "C3"의 주민번호 원본을 대상으로 지정한 위치의 개수만큼 변경을 해줄 수 있게 됩니다.
- Replace 함수는 항상 "위치(place, 장소, 위치)"를 지정해서 문자를 교체해 주는 함수라고 생각을 해두시면 되겠습니다.
5. Substitute 함수 사용해 보기
- Substitute 함수는 특정 문자를 찾아서 변경을 해주는 함수입니다.
- 이번에는 주민번호의 하이픈( "-" )을 찾아서 삭제하고 셀서식을 지정하여 관리해 보도록 하겠습니다.
- ① 삭제의 경우는 변경하고자 하는 문자를 빈값 ( "" )으로 해주시면 되고
- ② 셀 서식으로 주민번호를 관리하기 위해서는 텍스트를 숫자형식으로 변경해줘야 하고 숫자형식의 문자로나오는 결과(=텍스트)에 곱하기 숫자 1( *1)을 해줍니다.
- 이번에는 주민번호에서 찾아야 하는 문자는 하이프 ( "-" )이고 바꾸고자 하는 문자는 빈값( "", 공백 없이 큰따옴표)입니다.
- 그래서 숫 식은 : "=substitute( C3, "-", "" )로 표현이 가능하고 마지막인수는 생략하였습니다.
- "주민등록번호"의 셀 서식으로 적용하기 위해서는 "숫자" 형식으로 변환이 필요한데 "Substitute 함수의 결괏값은 항상 문자이기 때문에
- 꼼수로 곱하기 숫자 1을 해줍니다. 이렇게 하면 숫자모양의 문자의 경우 엑셀이 자동으로 숫자형식으로 변환을 해주게 됩니다.
- 그렇게 숫자 형식으로 변경까지 한 후에 "셀 서식" > "표시 형식" > "기타" > "주민등록번호" 서식을 적용해 주시면 됩니다.
- 데이터 입력 시 하이픈( "-" )이나 슬래쉬 ( "/" )가 같이 들어가게 되면 문자형식이 되기 때문에
- 이를 제거하고 숫자형식에서 서식을 적용하는 경우들이 주민번호, 전화번호 등의 데이터를 관리하는 경우에 많이 사용하곤 합니다.
6. Replace 함수 응용 - 위치가 가변적일 때 Find, Search 함수
- 조금 퀴즈 같은 문제이긴 한데 아래와 같이 2번째 슬래쉬( "/" )를 찾아서 "₩"을 삽입하는 작업을 Find함수와 Replace 함수로 구현을 해보도록 하겠습니다.
- Find 함수는 텍스트에서 특정 문자의 위치를 찾아 주는 함수입니다
- ① Find 함수는 찾기를 할 때 시작위치를 지정할 수 있기 때문에 Find 함수를 2번 사용하게 되면 두 번째 슬래쉬( "/" ) 위치를 알 수가 있습니다.
- ② Replace 함수에 대체할 문자의 개수 (세 번째 인수, num_chars)를 0으로 지정하게 되면 삭제되는 문자 없이 특정 문자의 삽입이 가능합니다.
- 설명을 위하여 단계를 좀 나누어서 수식을 구현해 보도록 하겠습니다.
- ① 첫 번째 슬래쉬 ("/") 위치 찾기 - Find 함수
- 첫 번째 슬래쉬 위치는 아래와 같이 Find 함수로 "/" 문자의 위치를 찾을 수 있습니다.
- 이때 Find 함수의 세 번째 인수(시작 위치)를 생략면 텍스트의 처음부터 찾기를 하게 됩니다.
- ② 두 번째 슬래쉬 ( "/" ) 위치 찾기 - Find 함수
- 두 번째 슬래쉬 위치는 동일하게 Find 함수로 찾을 수 있는데 이 번에는 마지막 인수(시작 위치)가 첫 번째 슬래쉬 위치 + 1이 되도록 해 줍니다.
- 이렇게 되면 첫 번째 슬래쉬 다음부터 찾기를 시작해서 최종 값으로 두 번째 슬래쉬을 전체 문장에서의 위치 값으로 찾을 수 있습니다.
- ③ Replace 함수로 "₩" 삽입하기
- 이때 "바꿀 문자의 위치 (start_num) = (두 번째 슬래쉬 위치 + 1)이 되도록 수식을 입력하고
- 그리고 기존 문자 중에서 삭제되는 문자가 없습니다. 그래서 기존 텍스트에서 대체될 문자의 개수인 세 번째 인수 "num_chars"는 숫자 0이 됩니다.
- 그렇게 전체 수식을 입력하게 되면 찾고자 하는 위치가 가변적인 경우에도 원하는 위치에 특정 문자를 삽입하는 것이 가능하게 됩니다.
- 우리는 여기서 ① 가변적인 위치를 Find 함수로 찾아서 적용하는 것과 ② Replace 함수로 기존 문자 삭제 없이 신규 문자를 추가하는 방법을 배웠습니다.
- 전체가 하나로 된 수식은 별첨 파일 예제에 추가해 두도록 하겠습니다. (각각의 해당 수식을 복사해서 하나의 수식으로 만들어 주시면 됩니다. ^^)
7.Substitute 함수 응용 - 특정 문자의 개수 알아보기
- 텍스트 내용에서 특정 문자의 개수가 몇 개인지를 알 수 있는 방법입니다.
- 원리는 Substitute 함수로 특정 문자를 제거하고 원본의 글자 수랑 제거된 텍스트의 글자 수를 비교하는 방식입니다.
- 특정 문자의 개수이기 때문에 특정 문자가 1개짜리 인지 아니면 2개짜리 인지 등의 개수에 따라서 차이나는 값을 특정문자의 개수로 나누어 주시면 최종값을 얻을 수 있습니다.
- 아래 예제를 참조하시기 바랍니다.
** 핵심 요약 : Replace, Substitute 함수 사용법 - 함수로 텍스트(글자) 바꾸기, 응용해보기
1. 함수 설명
- Replace, Substitute 함수는 텍스트의 일부를 다른 텍스트로 바꾸는 함수이다.
- Replace 함수 : 위치와 변경할 문자의 개수를 지정하여 변경
- Substitute 함수 : 특정 문자를 찾아서 변경
2. Replace 함수의 구문 / 사용방법
- REPLACE(old_text, start_num, num_chars, new_text)
- 첫 번째 인수 "old_text"는 기존 텍스트로, 변경할 문자가 들어 있는 텍스트 혹은 해당하는 셀 참조 주소이다.
- 두 번째 인수 "start_num"는 변경할 문자의 시작위치이다. "몇 번째 문자부터 몇 개을 바꾼다"에서 "몇 번째"에 해당하는 정수를 입력한다.
- 세 번째 인수 "num_chars"는 문자(characters) 개수(number)로 바꿀 문자의 개 수를 정수로 입력한다.
- 네 번째 인수 "new_text"는 바꾸고자 하는 문자를 입력 한다.
- 위치를 기반으로 "몇 번째부터 몇 개의 문자를 바꿔줘!"라는 함수로 사용된다.
3. Substitute 함수의 구문 / 사용방법
- SUBSTITUTE(text, old_text, new_text, [instance_num])
- 첫 번째 인수 "text"는 기존 텍스트로 변경할 문자가 들어 있는 텍스트 혹은 해당하는 셀 참조 주소이다.
- 두 번째 인수 "old_text"는 찾고자 하는 문자로 수식에 직접 입력하는 경우 텍스트이기 때문에 항상 큰따옴표( " " )를 사용해 주어야 한다.
- 세 번째 인수 "new_text"는 변경하고자 하는 문자이다.
- 네 번째 인수 "instance_num" 찾고자 하는 문자가 여러 개가 존재하는 경우 몇 번째의 old_text를 바꾸어 줄 것인지 지정하는 정수이다. 생략하게 되면 모든 old_text를 new_text로 변경 해주게 된다.
- Substitute 함수 : A문자를 찾아서 B문자로 변경, 특정 문자를 변경, 삭제해야 하는 경우 사용한다.
4. Replace 함수 사용해 보기
- 주민번호 뒷자리를 별표로 변경하는 경우에 대해서 Replace 함수를 사용해서 변경해 보자.
- 우리가 바꾸고 싶은 범위는 주민번호의 ① 9번째 숫자부터 ② 6개의 숫자이고 이를 6개의 별표인 ③ "******"로 변경해주면 된다.
5. Substitute 함수 사용해 보기
- ① 삭제의 경우는 변경하고자 하는 문자를 빈값 ( "" )으로 해주면 되고
- ② 셀 서식으로 주민번호를 관리하기 위해서는 텍스트를 숫자형식으로 변경해야 하고 이는 결과(=텍스트)에 곱하기 숫자 1( *1)을 해주면 된다.
6. Replace 함수 응용 - 위치가 가변적일 때 Find, Search 함수
- ① Find 함수는 찾기를 할 때 시작위치를 지정할 수 있고 2번 사용하면 2번째 위치를 찾을 수 있다. 이때 2번째 Find 함수는 시작위치를 조정해 주자.
- ② Replace 함수에 대체할 문자의 개수 (세 번째 인수, num_chars)를 0으로 지정하게 되면 삭제되는 문자 없이 특정 문자의 삽입이 가능하다.
7.Substitute 함수 응용 - 특정 문자의 개수 알아보기
- 원리는 Substitute 함수로 특정 문자를 제거하고 원본의 글자 수랑 제거된 텍스트의 글자 수를 비교하는 방식이다.
[엑셀 함수 강좌-플러스] - 999. 엑셀 함수 Plus 목차
999. 엑셀 함수 Plus 목차
[엑셀 함수 강좌-플러스] - 001. (엑셀 함수 Plus) Text 함수 사용법 - 원하는 서식(포맷, 날짜/시간, 소수점, 퍼센트, 시간, 요일)을 텍스로 만들어 보자 [엑셀 함수 강좌-플러스] - 002. (엑셀 함수 Plus) Tr
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글