텍스트에서 숫자 제거하기 – Excel 및 Google 스프레드시트
이 튜토리얼에서는 Excel 및 Google 스프레드시트에서 셀의 텍스트에서 숫자를 제거하는 방법을 보여드립니다.
Excel에서 텍스트에서 숫자를 제거하는 두 가지 다른 수식에 대해 설명드리겠습니다.
SUBSTITUTE 함수
SUBSTITUTE 함수를 기반으로 한 수식을 사용할 수 있습니다. 긴 수식이긴 하지만 문자열에서 숫자를 제거하는 가장 쉬운 방법 중 하나입니다.
이 수식에서는 SUBSTITUTE 함수를 다음과 같이 10번(각 숫자마다 하나씩: 0,1,…,9) 중첩했습니다:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
B3,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")
TEXTJOIN 배열 수식
문자열에서 숫자를 제거하기 위해 TEXTJOIN, MID, ROW 및 INDIRECT 함수로 구성된 복합 배열 수식을 사용할 수도 있습니다.
{=TEXTJOIN("",TRUE,IF(ISERR(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0),MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1),""))}
참고: TEXTJOIN 은 Excel 2019+ 및 Office 365에서 사용할 수 있는 새로운 Excel 함수입니다.
복잡한 수식이므로 더 쉽게 이해할 수 있도록 여러 단계로 나누어 설명하겠습니다.
1단계
MID 함수는 텍스트의 일부를 반환하는 텍스트 함수입니다. 반환할 문자 수와 시작 위치를 지정해야 합니다.
MID 함수의 시작 번호(start_num) 인수는 ROW 및 INDIRECT 함수의 결과로 나온 배열 목록을 사용합니다.
=ROW(INDIRECT("1:"&LEN(B3)))
MID 함수의 시작 번호(start_num) 인수는 ROW 및 INDIRECT 함수의 결과 배열 목록을 사용합니다.
{=MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)}
2단계
결과 배열의 각 값에 0(+ 0)을 더합니다(위의 MID 함수에서 얻은 값). Excel에서 숫자가 아닌 문자에 숫자를 더하면 #VALUE! 오류가 발생합니다. 따라서 위의 배열에 0을 더하면 숫자와 #VALUE!로 채워진 배열을 얻습니다.
{=MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0}
3단계
다음으로 오류가 있으면 TRUE를 반환하고 오류가 아닌 값이면 FALSE를 반환하는 ISERR 함수를 사용하여 숫자가 아닌 문자는 TRUE, 숫자는 FALSE인 배열을 출력합니다.
=ISERR(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0)
4단계
다음으로 IF 함수를 추가합니다.
IF 함수는 ISERR 함수(3단계)의 결과를 확인합니다. 값이 TRUE이면 영숫자 문자열의 모든 문자의 배열을 반환합니다. 이를 위해 끝에 0을 제외하고 나머지는 동일한 MID 함수를 하나 더 추가했습니다. IF 함수의 값이 FALSE이면 공백(“”)을 반환합니다.
이렇게 하면 문자열에서 숫자를 제외하고 문자만 포함하는 배열을 갖게 됩니다.
=IF(ISERR(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0),MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1),"")
5단계
마지막으로 위의 배열을 TEXTJOIN 함수에 넣습니다. TEXTJOIN 함수는 위 배열의 모든 문자를 조인하고 빈 문자열은 무시합니다.
TEXTJOIN 함수의 구분 기호는 빈 문자열(“”)로 설정하고 ignore_empty 인수의 값은 TRUE로 입력합니다.
이렇게 하면 원하는 결과, 즉 문자열에서 숫자를 제외한 문자가 반환됩니다.
{=TEXTJOIN("",TRUE,IF(ISERR(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)+0),MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1),""))}
참고: 예제에서 사용된 수식은 배열 수식입니다. Excel 2019 이하 버전에서 배열 수식을 입력할 때는 일반 Enter 키 대신 CTRL + SHIFT + ENTER 키를 사용하여 수식을 입력해야 합니다.
표시되는 중괄호로 수식을 올바르게 입력했는지 알 수 있습니다. 중괄호를 수동으로 입력하지 않으면 수식이 작동하지 않습니다.
Office 365(및 2019 이후 버전의 Excel)에서는 평소처럼 수식을 입력후 Enter키를 사용하면 됩니다.
TRIM 함수
문자열에서 숫자를 제거하면 여분의 공백이 남을 수 있습니다. 모든 후행 공백과 선행 공백, 단어 사이의 여분의 공백을 제거하려면 다음과 같이 TRIM 함수를 사용해야 합니다:
=TRIM(C3)
Google 스프레드시트에서 텍스트에서 숫자 제거하기
텍스트에서 숫자를 제거하는 수식은 Excel에서와 마찬가지로 Google 스프레드시트에서도 똑같이 작동합니다:
