텍스트에서 숫자만 추출
지원 버전 자세히 보기
문자와 섞인 값에서 숫자만 골라냅니다.
인수 설명
01모든 버전 숫자 추출 공식
=SUMPRODUCT ( MID ( 0&;셀, LARGE ( ISNUMBER ( --MID ( 셀, ROW ( $1:$50 ), 1 ) ) * ROW ( $1:$50 ), ROW ( $1:$50 ) ) + 1, 1 ) * 10^( ROW ( $1:$50 ) - 1 ) )인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
동작 원리
MID 함수가 문자를 하나씩 나누고 LARGE 함수와 SUMPRODUCT 함수가 숫자 문자만 원래 순서의 숫자값으로 조립합니다.
모든 버전 숫자 추출 공식의 예제 시트에서 B2 셀이 1250을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
숫자가 있는 문자 위치를 찾습니다
MID 함수가 A2의 앞 8개 문자를 하나씩 나누고 ISNUMBER 함수가 숫자인 문자에만 행 번호를 남깁니다. 실제 공식의 $1:$50 범위도 같은 방식으로 50번째 문자까지 확인합니다.
=ISNUMBER ( --MID ( A2, ROW ( $1:$8 ), 1 ) ) * ROW ( $1:$8 )
= {0;2;3;0;5;6;0;0}
숫자 1, 2, 5, 0이 있는 위치만 남긴 배열입니다. LARGE 함수가 숫자 위치를 뒤에서부터 정렬합니다
LARGE 함수는 숫자가 있는 위치 6, 5, 3, 2를 큰 값부터 가져옵니다. 앞에 붙인 0을 기준으로 읽도록 각 위치에 1을 더합니다.
=LARGE ( ISNUMBER ( --MID ( A2, ROW ( $1:$50 ), 1 ) ) * ROW ( $1:$50 ), {1;2;3;4} ) + 1
= {7;6;4;3}
0&A2 문자열에서 숫자를 뒤에서부터 읽을 위치입니다. SUMPRODUCT 함수가 자릿값을 합산합니다
MID 함수가 뒤에서부터 0, 5, 2, 1을 가져오고 각각 1, 10, 100, 1000을 곱합니다. SUMPRODUCT 함수가 0+50+200+1000을 더해 1250을 반환합니다.
=SUMPRODUCT ( MID ( 0&A2, LARGE ( ISNUMBER ( --MID ( A2, ROW ( $1:$50 ), 1 ) ) * ROW ( $1:$50 ), ROW ( $1:$50 ) ) + 1, 1 ) * 10^( ROW ( $1:$50 ) - 1 ) )
= 1250
A2에서 마침표를 제외한 숫자값입니다. 02Excel 2019 이후 소수점 포함 공식
=TEXTJOIN ( "", TRUE, IF ( ISNUMBER ( MID ( 셀, ROW ( INDIRECT ( "A1:A"&;LEN ( 셀 ) ) ), 1 ) * 1 ) + ( MID ( 셀, ROW ( INDIRECT ( "A1:A"&;LEN ( 셀 ) ) ), 1 )="." ), MID ( 셀, ROW ( INDIRECT ( "A1:A"&;LEN ( 셀 ) ) ), 1 ), "" ) )인수 설명 자세히 보기
동작 원리
INDIRECT 함수와 ROW 함수가 문자 위치를 만들고 IF 함수와 TEXTJOIN 함수가 숫자와 마침표만 텍스트로 연결합니다.
Excel 2019 이후 소수점 포함 공식의 예제 시트에서 C2 셀이 12.50을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
INDIRECT 함수가 문자 위치 범위를 만듭니다
LEN 함수는 A2의 문자 수 8을 반환합니다. INDIRECT 함수가 A1:A8 참조를 만들고 ROW 함수가 각 문자의 위치를 배열로 반환합니다.
=ROW ( INDIRECT ( "A1:A"&LEN ( A2 ) ) )
= {1;2;3;4;5;6;7;8}
A2의 여덟 문자를 읽을 위치입니다. IF 함수가 숫자와 마침표만 남깁니다
MID 함수가 여덟 문자를 하나씩 나누고 IF 함수가 숫자 또는 마침표인 항목만 남깁니다. 문자 A, k, g가 있던 자리는 빈 문자열로 바뀝니다.
=IF ( ISNUMBER ( MID ( A2, {1;2;3;4;5;6;7;8}, 1 ) * 1 ) + ( MID ( A2, {1;2;3;4;5;6;7;8}, 1 )="." ), MID ( A2, {1;2;3;4;5;6;7;8}, 1 ), "" )
= {"";"1";"2";".";"5";"0";"";""}
숫자와 마침표만 남긴 문자 배열입니다. TEXTJOIN 함수가 남은 문자를 연결합니다
TEXTJOIN 함수는 빈 문자열을 건너뛰고 남은 문자를 구분자 없이 연결합니다. Excel 2019에서는 Ctrl+Shift+Enter로 확정해야 하며, INDIRECT 함수는 입력 길이에 맞는 범위를 다시 계산하는 휘발성 함수입니다.
=TEXTJOIN ( "", TRUE, IF ( ISNUMBER ( MID ( A2, ROW ( INDIRECT ( "A1:A"&LEN ( A2 ) ) ), 1 ) * 1 ) + ( MID ( A2, ROW ( INDIRECT ( "A1:A"&LEN ( A2 ) ) ), 1 )="." ), MID ( A2, ROW ( INDIRECT ( "A1:A"&LEN ( A2 ) ) ), 1 ), "" ) )
= ";12.50";
숫자와 마침표를 이어 붙인 텍스트입니다. 03Excel 2021·M365 최신 공식
=LET ( txt, 셀, chars, MID ( txt, SEQUENCE ( LEN ( txt ) ), 1 ), TEXTJOIN ( "", TRUE, IF ( ISNUMBER ( --chars ) + ( chars="." ), chars, "" ) ) )인수 설명 자세히 보기
동작 원리
LET 함수가 입력값과 문자 배열에 이름을 붙이고 SEQUENCE 함수와 TEXTJOIN 함수가 숫자와 마침표만 한 셀에 연결합니다.
Excel 2021·M365 최신 공식의 예제 시트에서 D2 셀이 12.50을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
LET 함수가 입력값에 이름을 붙입니다
LET 함수는 A2 셀의 값을 txt라는 이름으로 저장합니다. 뒤의 계산식은 같은 셀 참조를 반복하지 않고 txt를 사용합니다.
=LET ( txt, A2, txt )
= ";A12.50kg";
txt에 저장된 입력 문자열입니다. SEQUENCE 함수가 문자를 하나씩 분리합니다
LEN 함수가 반환한 길이 8만큼 SEQUENCE 함수가 위치 배열을 만들고, MID 함수가 각 위치의 문자를 하나씩 반환합니다.
=MID ( A2, SEQUENCE ( LEN ( A2 ) ), 1 )
= {"A";"1";"2";".";"5";"0";"k";"g"}
A2를 문자 단위로 나눈 배열입니다. TEXTJOIN 함수가 숫자와 마침표를 연결합니다
IF 함수가 숫자와 마침표만 남기고 TEXTJOIN 함수가 빈 문자열을 제외해 연결합니다. 내부에서는 동적 배열을 사용하지만 최종 결과는 한 셀에만 표시됩니다.
=LET ( txt, A2, chars, MID ( txt, SEQUENCE ( LEN ( txt ) ), 1 ), TEXTJOIN ( "", TRUE, IF ( ISNUMBER ( --chars ) + ( chars="." ), chars, "" ) ) )
= ";12.50";
숫자와 마침표를 이어 붙인 텍스트입니다. 
엑셀은 시스템적으로 최초 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/논리값-숫자-변경-기호/