텍스트(문자열)에서 숫자만 추출

Excel에서는 수식을 사용하여 텍스트와 숫자가 결합된 값에서 숫자를 추출할 수 있습니다. 다음 예에서는 “TPS1984” 값에서 1984를 가져오는 수식을 사용했습니다.

텍스트에서 숫자를 추출하는 공식

 =TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),""))

이 튜토리얼에서는 이 공식을 자세히 이해할 것입니다. 그리고 이를 이해하려면 이를 여러 부분으로 나누어야 합니다.

1. 먼저, 값의 문자 수를 가져오는 LEN 함수가 있습니다.
   
2. 그 후 INDIRECT는 1과 7(LEN에서 반환됨)을 사용하는 셀 참조를 사용합니다.
   
3. 그런 다음 ROW 함수는 INDIRECT를 사용하고 1로 시작하고 7로 끝나는 숫자 배열을 반환합니다.
   
4. 이제 MID는 이 배열을 사용하고 참조한 셀의 값이 포함된 새 배열을 반환합니다.
   
5. 다음으로 배열에 1을 곱하는 간단한 곱셈 공식이 있습니다. 이 간단한 곱셈을 사용하면 #VALUE가 되는 새 배열을 얻게 됩니다! 텍스트 값과 숫자에 대한 오류는 그대로 유지됩니다.
   
6. 여기에서 IFERROR 함수는 오류 값을 빈 값으로 변환합니다.
   
7. 그리고 마지막에 TEXTJOIN이 값을 결합하고 결과에는 숫자만 표시됩니다.
   

위 수식은 Excel 2019 버전 이상에서만 작동합니다. 이는 이전 버전에서는 사용할 수 없는 TEXTJOIN을 사용하고 있기 때문입니다.

수식의 일부에 대한 값을 원하는 경우 해당 부분을 선택하고 F9 단축키를 누르십시오.

TEXTJOIN은 워크시트에서 셀을 업데이트할 때 값을 변경하는 휘발성 함수입니다. 이로 인해 통합 문서가 약간 느려질 수 있습니다.

하지만 이전 버전(2007, 2010, 2013, 2016)을 사용하면 어떻게 되나요?

이전 버전을 사용하는 경우 다른 수식을 사용해야 합니다. 다음과 같은 공식:

 =IF(SUM(LEN(A1)-LEN(SUBSTITUTE(A1, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1))* ROW(INDIRECT("$1:$"&LEN(A1))),0), ROW(INDIRECT("$1:$"&LEN(A1))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A1)))/10),"")

 =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)

위의 두 공식은 다양한 유형의 값에 잘 적용됩니다. 나는 다음과 같은 방법으로 테스트했다.

위의 셀 A1에는 텍스트, 숫자 및 기호로 구성된 복잡한 값이 있으며 결과에는 숫자만 있습니다.