엑셀 숫자만 추출하기 공식
엑셀 숫자만 추출하기 목차 바로가기
함수 공식
=SUMPRODUCT(MID(0&셀,LARGE(ISNUMBER(--MID(셀,ROW($1:$50),1))*ROW($1:$50),ROW($1:$50))+1,1)*10^(ROW($1:$50)-1))
'본 공식은 배열수식이므로 M365 이전버전은 Ctrl + Shift + Enter로 입력합니다.
'본 공식은 배열수식이므로 M365 이전버전은 Ctrl + Shift + Enter로 입력합니다.
소수점(.)을 포함하여 숫자를 추출하는 공식 업데이트!
(엑셀 2019 이후 버전)
=TEXTJOIN("",,IF(--ISNUMBER(MID(셀,ROW(INDIRECT("A1:A"&LEN(셀))),1)*1)+(--(MID(셀,ROW(INDIRECT("A1:A"&LEN(셀))),1)=".")),MID(셀,ROW(INDIRECT("A1:A"&LEN(셀))),1),""))
'위 공식은 엑셀 2019 이후 버전에서만 사용가능합니다.
(엑셀 2019 이후 버전)
=TEXTJOIN("",,IF(--ISNUMBER(MID(셀,ROW(INDIRECT("A1:A"&LEN(셀))),1)*1)+(--(MID(셀,ROW(INDIRECT("A1:A"&LEN(셀))),1)=".")),MID(셀,ROW(INDIRECT("A1:A"&LEN(셀))),1),""))
'위 공식은 엑셀 2019 이후 버전에서만 사용가능합니다.
매크로를 사용해서 숫자 추출을 자동화하는 방법은 아래 게시글에 정리해드렸습니다.
인수 설명
| 인수 | 설명 |
| 셀 | 숫자와 문자가 함께 입력된 셀 주소입니다. |
| $1:$50 | $1:$50로 범위를 입력하면, 셀에 입력된 문자를 최대 50자까지 받아와 숫자를 추출합니다. 만약 더 많은 글자에서 숫자를 추출해야 할 경우, 범위를 $1:$100 등으로 수정합니다. 단 범위를 넓게 지정할수록, 함수의 처리속도가 느려지므로 주의해서 사용합니다. |
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀공식] 엑셀 숫자만 추출하기 공식예제파일
호환성
운영체제 호환성 Windows 버전 모든 엑셀 버전에서 사용 가능합니다. Mac 버전 모든 엑셀 버전에서 사용 가능합니다. 사용된 기초 함수
엑셀 숫자만 추출하기 공식 알아보기
공식 설명
본 공식은 셀 안에 입력된 값에서 숫자만 추출하는 공식입니다. 셀 안에 입력된 글자수나 사용된 언어(영어, 한국어, 중국어 등)에 상관없이 사용할 수 있습니다.
M365 버전에서는 일반 수식과 동일하게 Enter키로 입력해도 동작하지만, M365 이전 버전에서는 반드시 Ctrl + Shift + Enter로 수식을 입력해야 합니다. 그렇지 않을 경우 #VALUE! 오류를 반환합니다.
공식의 동작원리
- 셀 안에 입력된 값 앞에 강제로 0 추가하기
'셀 : abc12e34f
= SUMPRODUCT(MID(0&셀,LARGE(ISNUMBER(--MID(셀,ROW($1:$50),1))*ROW($1:$50),ROW($1:$50))+1,1)*10^(ROW($1:$50)-1))
'셀 안에 입력된 텍스트 앞에 0을 추가합니다.
= SUMPRODUCT(MID("0abc12e34f",LARGE(ISNUMBER(--MID(셀,ROW($1:$50),1))*ROW($1:$50),ROW($1:$50))+1,1)*10^(ROW($1:$50)-1)) - LARGE 함수로 문자 추출지점 구하기
= SUMPRODUCT(MID("0abc12e34f",LARGE(ISNUMBER(--MID(셀,ROW($1:$50),1))*ROW($1:$50),ROW($1:$50))+1,1)*10^(ROW($1:$50)-1))
' 공식에서 LARGE 함수 부분만 분리해서 단계별로 알아보겠습니다.
= LARGE(ISNUMBER(--MID("abc12e34f",ROW($1:$50),1) )*ROW($1:$50), ROW($1:$50)) +1
' ROW($1:$50)은 배열함수에서 {1,2,3,4,5...., 50}을 반환합니다.
= LARGE(ISNUMBER(--MID("abc12e34f",{1,2,3,4,...,50},1) )*ROW($1:$50), ROW($1:$50)) +1
' MID 함수로 "0abc12e34f" 에서 첫번째 문자부터 두번째, 세번째... 문자를 하나씩 분리합니다.
' -- 기호는 문자로 반환된 값을 숫자로 강제 변환합니다.
= LARGE ( ISNUMBER( {a,b,c,1,2,e,3,4,f} ) * ROW($1:$50) , ROW($1:$50)) +1
' ISNUMBER를 통해 반환된 값의 숫자 여부를 판단합니다. 숫자일경우 1, 아닐경우 0을 반환합니다.
= LARGE ( {0,0,0,1,1,0,1,1,0} ) * ROW($1:$50) , ROW($1:$50)) +1
= LARGE ( {0,0,0,4,5,0,7,8,0} , ROW($1:$50) ) + 1
= {8,7,5,4} + 1
= {9,8,6,5}
' 0을 포함한 텍스트(0abc12e34f)에서, 각 9번째,8번째,6번째,5번째 값이 숫자인 것을 확인할 수 있습니다. - 맨 뒤에 위치한 숫자값 부터 10^제곱근을 곱하여 나열한 뒤, SUMPRODUCT로 합산하기
' MID 함수로 각 n번째 문자를 분리합니다.
= SUMPRODUCT( MID( "0abc12e34f", {9,8,6,5}, 1) * 10^(ROW($1:$50)-1) )
= SUMPRODUCT( {4,3,2,1} * 10^(ROW($1:$50)-1) )
= SUMPRODUCT ( {4,3,2,1} * 10^( {0,1,2,3} ) )
' 10의 0제곱은 1, 10의 1제곱은 10, 10의 2제곱은 100... 으로 계산됩니다.
= SUMPRODUCT ( {4*10^0, 3*10^1, 2*10^2, 1*10^3} )
= SUMPRODUCT ( {4, 30, 200, 1000} )
= 1234
'결과값으로 1234를 반환합니다. (=셀에서 숫자만 추출)
- 셀 안에 입력된 값 앞에 강제로 0 추가하기
