SUBSTITUTE로 텍스트 바꾸기

데이터에서는 수식 안에서 찾기 및 바꾸기를 수행해야 하는 경우가 많습니다. 전화번호에서 하이픈을 제거하거나, 단어를 바꾸거나, 잘못 들어간 기호를 삭제하는 경우가 그렇습니다.

SUBSTITUTE 함수는 셀 안에서 텍스트 일부를 찾아 모든 일치 항목을 새 텍스트로 바꾸며, 원본 셀은 변경하지 않습니다.

수식으로 구현한 찾기 및 바꾸기와 같지만, 데이터가 변경되면 자동으로 업데이트됩니다.

SUBSTITUTE는 최대 네 개의 인수를 받습니다.

• 텍스트 원래 문자열 또는 셀
• 찾을 텍스트 찾으려는 텍스트
• 새 텍스트 그 자리에 넣을 텍스트
• 발생 번호 (선택 사항) 바꿀 일치 항목

형식은 =SUBSTITUTE(text, old_text, new_text, [instance_num])입니다. 마지막 인수를 생략하면 모든 일치 항목이 바뀝니다.

자주 사용하는 방법 중 하나는 어떤 항목을 아무것도 없음으로 바꿔 삭제하는 것입니다. 새 텍스트 인수를 빈 문자열 ""로 설정하면 됩니다.

A2에 555-123-4567이 들어 있다면 아래 수식은 모든 하이픈을 제거하여 5551234567을 반환합니다.

이 패턴은 전화번호, 식별자 및 구두점이 포함된 다른 값을 정리할 때 유용합니다.

SUBSTITUTE는 한 문자뿐만 아니라 전체 단어에도 사용할 수 있습니다.

A2에 연례 보고서 2023이 들어 있다면 단어를 바꿔 연도를 업데이트할 수 있습니다.

결과는 연례 보고서 2024가 됩니다. 셀에서 일치하는 모든 항목이 바뀝니다.

중요합니다. SUBSTITUTE는 대소문자를 포함하여 텍스트를 정확히 일치시킵니다.

A2에 고양이 고양이 CAT이 들어 있다면 =SUBSTITUTE(A2, "cat", "dog")는 소문자로 된 고양이만 바꾸어 고양이 개 CAT을 반환합니다.

대소문자를 구분하지 않고 바꾸려면 먼저 LOWER 또는 UPPER로 대소문자를 통일하거나, 다음 학습에서 Sheets의 정규 표현식 함수를 사용해 보세요.

선택 사항인 네 번째 인수인 발생 번호를 사용하면 모든 일치 항목 대신 특정 항목 하나를 지정할 수 있습니다.

A2에 a-b-c-d가 들어 있고 두 번째 하이픈만 바꾸려면 발생 번호를 2로 설정합니다.

결과는 a-b/c-d가 됩니다. 두 번째 하이픈만 슬래시로 바뀌었습니다.

텍스트로 저장된 가져온 금액을 생각해 보겠습니다. 값은 $1,250.00입니다. 사용 가능한 숫자로 바꾸려면 달러 기호와 쉼표를 제거해야 합니다.

SUBSTITUTE 호출 두 개를 하나 안에 다른 하나가 들어가도록 중첩하여 두 문자를 모두 제거합니다.

안쪽 호출은 쉼표를 제거하고 바깥쪽 호출은 달러 기호를 제거하여 1250.00만 남깁니다.

각각 다른 문자를 정리하도록 필요한 만큼 SUBSTITUTE 호출을 중첩할 수 있습니다.

공백, 마침표, 슬래시가 포함된 지저분한 코드를 정리된 하이픈 형식으로 통일하려면 세 번의 바꾸기를 연결합니다.

각 단계는 결과를 다음 단계에 전달하여 최종적으로 정리된 문자열을 만듭니다.

SUBSTITUTE를 비슷한 REPLACE 함수와 혼동하지 마세요.

• SUBSTITUTE는 내용이 일치하는 텍스트를 바꿉니다. (이 단어를 찾아 바꾸기)
• REPLACE는 위치를 기준으로 텍스트를 바꿉니다. (5번째 위치에서 시작하는 4개의 문자 바꾸기)

무엇을 찾아야 하는지 알고 있다면 SUBSTITUTE를 사용하고, 그것이 어디에 있는지 알고 있다면 REPLACE를 사용하세요.

유용한 응용 방법으로 SUBSTITUTE와 LEN을 함께 사용해 어떤 항목이 몇 번 나타나는지 셀 수 있습니다.

문자를 제거한 후의 길이를 원래 길이에서 빼세요. A2에 a,b,c,d가 들어 있다면 이 방법으로 쉼표의 개수를 세어 3을 반환합니다.

이 방법은 구분 기호로 나뉜 목록의 항목 수를 셀 때 유용합니다.

A2에 555.123 4567처럼 마침표와 공백이 섞인 지저분한 번호가 들어 있다고 가정해 보겠습니다. 이를 일관된 하이픈 형식인 555-123-4567로 바꾸려고 합니다.

먼저 마침표와 공백을 제거한 다음 하이픈을 다시 삽입할 수 있지만, 여기서는 구분 기호를 하이픈으로 통일하기만 하겠습니다.

바꾸기를 중첩하면 어떤 불필요한 구분 기호가 나타나더라도 형식을 표준화할 수 있습니다.

SUBSTITUTE에 대한 이해도를 확인해 보세요.

수식에서 텍스트를 찾고 바꾸는 방법을 배웠습니다.

• =SUBSTITUTE(text, old_text, new_text, [instance_num])
• ""을 새 텍스트로 사용하면 문자를 삭제할 수 있습니다.
• 대소문자를 구분하므로 필요하면 먼저 대소문자를 통일하세요.
• 선택 사항인 발생 번호를 사용하면 특정 일치 항목 하나를 지정할 수 있습니다.
• 여러 문자를 정리하려면 호출을 중첩하고, 일치 항목을 세려면 LEN과 함께 사용하세요.

텍스트 연결 및 변경 단원을 완료했습니다. 깔끔하고 일관된 데이터를 만드는 훌륭한 작업이었습니다!