엑셀에서 셀 안에 있는 내용이 숫자와 문자가 섞여 있을 때 숫자만 추출하는 단독 함수는 없습니다. 하지만 함수의 조합으로 숫자만 추출하는 방법이 있습니다.
이렇게 엑셀을 사용하다 보면, 텍스트와 숫자가 따로 필요한 경우가 있습니다. 특히, 시리얼 번호나 학번, 상품번호처럼 숫자만 구하는 경우에 숫자 데이터만 남기고 모두 지우는 방법에 대해서 알아보겠습니다.
엑셀 숫자만 추출 하는 함수
숫자만 추출하기 위해 사용할 함수식은 다음과 같습니다
=TEXTJOIN(“”, TRUE, IF(ISNUMBER(MID(B2, ROW(INDIRECT(“1:” & LEN(B2))), 1) + 0), MID(B2, ROW(INDIRECT(“1:” & LEN(B2))), 1), “”))
이 함수식은 셀 B2의 문자열에서 숫자만 추출하여 하나의 문자열로 결합합니다.
예를 들어, 셀 B2에 APPLE_프로젝트_2024_XY01이라는 값이 있다면, 이 수식은 “202401”을 반환하게 됩니다.
함수식 설명
이제 이 수식이 어떻게 작동하는지 하나씩 잘라서 단계별로 분석해 보겠습니다.
- LEN(B2)
– LEN(A1)은 셀 B2의 문자열 길이를 반환합니다. APPLE_프로젝트_2024_XY01의 길이는 20입니다. - ROW(INDIRECT(“1:” & LEN(B2)))
– INDIRECT(“1:” & LEN(B2))는 “1:20″라는 문자열을 만들고, ROW(…)는 이 범위에 해당하는 행 번호 (1, 2, 3, …, 20)
이 단계에서 {1, 2, 3, …., 20}이라는 배열이 생성됩니다. - MID(B2, ROW(…), 1)
– MID(B2, ROW(…), 1)은 A1의 각 위치에서 문자를 하나씩 추출합니다. 즉 APPLE_프로젝트_2024_XY01에서 각각의 문자로 반환됩니다. 이 결과로 {“A”, “P”, “P”, “L”, “E”, “_”, “프”, “로”, “젝”, “트”, “_”, “2”, “0”, “2”, “4”,”_”, “X”, “Y”, “0”, “1”}이라는 배열이 생성됩니다. - MID(B2, ROW(…), 1) + 0
– 각 문자를 숫자로 변환하려고 시도합니다. “2”, “0”, “2”, “4”, “0”, “1”은 그대로 숫자로 변환되지만, 나머지 문자 “A”, “P”, “P”, “L”, “E”, “_”, “프”, “로”, “젝”, “트”, “_” ….은 변환되지 않아 #VALUE! 오류가 발생합니다.
– 이 결과로 {“#VALUE!”, “#VALUE!”, “#VALUE!”, “#VALUE!”, “#VALUE!”, “#VALUE!”, “#VALUE!”, “#VALUE!”, “#VALUE!”, “#VALUE!”, “#VALUE!”, 2, 0, 2, 4, “#VALUE!”, “#VALUE!”, “#VALUE!”, 0, 1}이 생성됩니다. - ISNUMBER(…)
– ISNUMBER는 숫자인지 여부를 확인하여, 숫자인 경우 “TRUE”, 숫자가 아닌 경우 “FALSE”를 반환합니다.
– 이 단계에서는 {FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, TRUE, TRUE}라는 배열이 만들어집니다. - IF(ISNUMBER(…), MID(B2, ROW(…), 1), “”)
– IF 함수는 TRUE인 경우 해당 문자를, FALSE인 경우 빈 문자열(“”)을 반환합니다.
– 따라서, {“”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “”, “2”, “0”, “2”, “4”, “”, “”, “”, “0”, “1”} - TEXTJOIN(“”, TRUE, …)
– 마지막으로 “TEXTJOIN” 함수는 배열 내의 문자를 빈 문자열로 결합하여 하나의 문자열로 만듭니다. “TRUE” 옵션은 빈 문자열을 무시하므로, “202401”이라는 결과가 반환됩니다.
▼ 함수로 완성된 값에서 아래로 드래그 자동 완성하면 나머지 셀도 적용이 되어 정확히 숫자만 추출되는 것을 확인할 수 있습니다.
이 수식은 엑셀에서 텍스트와 숫자가 혼합된 데이터에서 숫자만을 추출하는 데 정말 유용합니다.
실전 예시인 “APPLE_프로젝트_2024_XY01″과 같은 데이터를 다룰 때, 이 수식을 활용하여 손쉽게 필요한 숫자 정보를 얻을 수 있습니다.
엑셀에서 숫자만 추출하고 싶다면 간단하게 위 함수식을 작성해서 적용해 보세요.
엑셀 숫자만 남기고 지우기 (빠른 채우기 사용)
또 다른 방법으로 더 간단한 방법으로 Ctrl + E 빠른 채우기를 사용하는 법입니다.
아래처럼 불규칙하고 복잡한 문자열에서 일정 패턴으로 숫자를 입력한 후 Ctrl + E를 사용하면 빠른 채우기로 자동 완성됩니다.
숫자도 마찬가지로 자동 완성되어 나타나지만 일부 복잡한 구조의 문자열은 인식을 못하고 놓치는 경우도 있습니다.
▼ 엑셀에서 패턴을 파악할 수 있도록 2~3개의 셀에 패턴을 만들어 줍니다.
▼ 자동 완성할 셀을 모두 선택하고 Ctrl + E 빠른 채우기를 눌러주세요.
▼ 함수를 사용해서 추출하는 것 보다 빠르게 작업할 수 있습니다.
이 두 가지 방법을 사용하면, 엑셀에서 문자와 숫자가 섞여있는 셀에서 숫자만 추출하는 작업을 더욱 효율적으로 실행할 수 있습니다.
함수는 복잡하지만 정확한 작업이 가능하고, 빠른 채우기는 단순한 문자 배열에서 빠르게 숫자만 추출할 수 있습니다.