엑셀 문서에서 숫자와 문자가 섞여있는 셀이 있을 때 문자를 제외한 숫자만 추출해야 하는 경우가 있는데요.
숫자만 추출하는 함수를 이용하면 쉽게 자동화가 가능하지만 함수 사용법을 몰라서 셀을 일일히 선택해서 글자를 지우는 경우가 있는데, 아래에 소개하는 함수들을 이용해 셀의 규칙에 맞춰서 숫자만 추출할 수 있습니다.
목차
SUBSTITUTE 함수로 엑셀 숫자만 추출하기
=SUBTITUTE(원본셀,"제외할문자","")전화번호, 모델명과 같이 문자와 숫자가 일정한 패턴으로 셀에 입력되어 있는 경우 SUBSTITUTE 함수를 이용해 문자를 제외하고 숫자만 남길 수 있습니다.
특정 문자열을 제외하기 위해서는 원본셀과 제외할 문자를 지정하면 되며 함수를 입력한 후 셀을 드래그해서 같은 패턴의 셀을 전체적으로 적용할 수 있습니다.
=SUBTITUTE(원본셀,"제외할문자","AB-")참고로 맨 마지막 인수는 셀 값 맨 앞에 특정 숫자 또는 문자를 입력하는 것입니다.
LEFT, RIGHT, MID 함수로 엑셀 숫자만 추출하기
=LEFT(원본셀,왼쪽에서 시작할 숫자 위치)
=RIGHT(원본셀,오른쪽에서 시작할 숫자 위치)
=MID(원본셀, 추출할 숫자 위치,추출할 숫자 범위)
=MID(원본셀, FIND("문자 특정",원본셀)-추출할 숫자 위치, 추출할 숫자 범위)LEFT, RIGHT 함수는 셀 내용에서 특정 위치의 텍스트를 기준으로 추출하는 함수로 왼쪽, 오른쪽을 기준으로 셀에서 몇번째 숫자부터 추출할지 지정하는 방식입니다.
MID 함수는 숫자 위치를 지정한 다음 숫자를 기준으로 숫자 범위를 입력해서 추출할 수 있으며 FIND 함수와 연계하면 특정 문자를 기준으로 숫자 범위를 지정해 추출할 수 있습니다.
FIND 함수 사용시 숫자의 위치를 음수(-)로 지정하면 특정 문자 앞의 숫자를 추출하는 것이며 양수(+)를 지정하면 특정 문자 뒤의 숫자를 추출하는 것이니 참고하시길 바랍니다.
위 3가지 함수를 사용해서 숫자를 추출할 때 셀 원본 속성이 텍스트로 되어 있어야 숫자를 정확히 추출하며 날짜 또는 일반 셀로 지정되어 있다면 아래 함수를 참고하시길 바랍니다.
YEAR, MONTH, DAY 함수로 날짜 숫자만 추출하기
=YEAR(원본셀)
=MONTH(원본셀)
=DAY(원본셀)만일 셀 값이 날짜인 경우 년, 월, 일에 해당하는 숫자만 간단하게 추출할 수 있습니다.
함수와 필터링 조합으로 엑셀 숫자만 추출하기
=SUMPRODUCT(MID(0&원본셀,LARGE(ISNUMBER(--MID(원본셀,ROW($1:$50),1))*ROW($1:$50),ROW($1:$50))+1,1)*10^(ROW($1:$50)-1))MS Office 365 이하 버전(2019~2010)일 경우 Ctrl + Shift + Enter 키로 입력합니다.
위 함수 조합이 동작하는 원리를 간단하게 설명하면 다음과 같습니다.
- MID 함수로 원본셀에 있는 데이터를 최대 50자까지 메모리에 적재한다.
- 적재한 데이터에서 ISNUMBER 함수를 이용해 문자를 제외한 숫자만 추출한다.
- LARGE 함수로 배열을 만들어 숫자의 자리수를 지정한다.
- 자리수를 배정한 숫자 모두 1을 더해서 1을 반환하지 않을 경우 0을 채워 빈 자리수를 체크한다.
- SUMPRODUCT 함수로 자리수에 맞는 숫자를 곱한다.(예:x*10=10, 두번째 자리수) 그리고 배열의 모든 자리수를 합산한다.
- 숫자만 추출된 셀 값이 출력된다.
위 함수 조합은 셀 문자열이 최대 50 일 때를 기준으로 하며 많은 셀을 계산할 때 CPU 점유율이 높아질 수 있으므로 필요에 맞게 ($1:$50) 값을 낮추거나 높여서 최대 문자열을 지정하면 되겠습니다.
