엑셀 숫자만 추출하기 공식
엑셀 숫자만 추출하기 목차 바로가기
함수 공식
=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 추가하기
엑셀은 시스템적으로 최초 15자리 숫자만 값을 표현하고 그 이후 자리는 0으로 변경합니다.
따라서 숫자로 표현하는 것은 근본적으로 해결이 불가능하구요..
대안책으로 앞에 홑따옴표(')를 추가해서 텍스트 형태로 변경하는 방법이 있습니다.
수식이 아닌 기존 값 앞에 홑따옴표를 추가하시면 됩니다.
예를들어, 123123123123123123 을 입력하시면, 123123123123123000 으로 표시가 됩니다.
앞에 홑따옴표를 추가하시면, 123123123123123123 이 텍스트형태로 표시됩니다.
그 상태에서 MID 함수나 LEFT 함수를 사용해서 3자리 번호를 추출하시면 될 듯 합니다.^^
문자만 있을 경우 0이 출력되는 것이 정상입니다.
0 이 아닌 다른 값을 출력하시려면 IF함수를 같이 응용해보세요.
오빠두 엑셀 페이지가 회사 업무하는데 정말 도움이 많이 되고 있어서 어떻게 감사를 드려야 할지 모르겠어요 ㅎㅎ
제가 사용하는 엑셀 자료는 매일매일 자료를 업데이트를 해야 해서 ROW 함수의 범위도 계속계속 늘어나야 하는데, 이렇게되면 엑셀 파일 자체가 버벅되는 현상이 생기는 거죠~?
ROW 함수 범위를 늘리면 처리속도가 느려지는 건 맞지만, 제 예상에 200개 글자 이상 문자열에서 숫자를 추출하는 경우는 없을 것이라고 생각됩니다.^^
ROW 함수 범위가 극단적으로 늘어나지 않는 한, 처리속도가 아주 느려지거나 하진 않을 겁니다. :)
감사합니다.
함수앞과 뒤에 있는 {}표시는 무엇일까요ㅠㅠ 저 저 함수 너무 쓰고 싶은데 왜 전 안될까요ㅠㅠ 유트브 영상에도 없고ㅠㅠ
본 수식은 배열수식이므로 Ctrl + Shift + Enter로 입력하시면 됩니다.^^
실 사용예제는 파일을 확인해주시고, 배열수식에 대한 설명은 아래 강의를 한번 참고해보세요.
https://www.oppadu.com/%ec%a7%84%ec%a7%9c%ec%93%b0%eb%8a%94-%ec%8b%a4%eb%ac%b4%ec%97%91%ec%85%80-7-4-1/
2. 아래 링크를 한번 참고해보시길 바랍니다.^^
https://www.oppadu.com/논리값-숫자-변경-기호/