엑셀(EXCEL), 숫자만 추출하는 배열함수로 숫자와 텍스트가 복잡하게 섞여있는 문장을 간단하기 정리하기(글에서 수만 걸러내기)

텍스트와 숫자가 한셀에 포함되어 있는 경우, 숫자만 추출하고 싶을 때가 많습니다.

모든 글이 "OO회차" 같이 숫자+문자로 고정된 형태라면 mid, left, right 함수같은 방법이 있습니다.

하지만 불규칙하게 복잡하게 섞여있다면 여러가지 절차를 거치거나 숫자인지 판별해야 합니다.

 

 

1. 함수내용

 

여러 절차를 한개의 함수 안로 작성하는 것이라 상당히 길게 작성이 됩니다.

셀주소 "A1"에 있는 글자에서 숫자만 추출하는 함수입니다.

CONCAT, IF, ISNUMBER, VALUE, MID, ROW, INDIRECT, LEN, VALUE가 포함되어 있습니다.

 

=CONCAT(IF(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))), VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)), ""))

 

 

2. 동작 예시

 

소스가 복잡해서 한눈에 알기는 힘들지만, 복사해서 붙여 넣으면 여튼 동작은 합니다.

배열함수임으로 복사해서 붙여넣은 후에 Ctrl + Shift + Enter를 입력해야 합니다. 

숫자형식으로 만드어져서 저 상태에서 +,-,/,*등의 연산을 해도 문제없습니다.

자주 사용하는 방식은 아니겠지만 여러가지 상황에서 유용하게 사용할 수 있습니다.

 

함수 동작하기

 

 

 

 

 

3. 함수 동작방식

 

이제 동작 방식을 조금 알아보겠습니다.

이 부분부터는 함수의 기능을 어느정도 알고 있는 분들이 보기를 바랍니다.

작동하는 순서대로 안에서부터 하나씩 기능을 설명하겠습니다.

 

함수 동작순서

 

 

① MID 함수 : 글자에서 일부만 추출하는 함수입니다. 원하는 "A1"셀을 가져와 지정된 문자수만큼 문자를 추출하는 함수인데, 글자수를 1로 고정하여 1개만 불러오도록 했습니다.

② ROW 함수 + INDRIECT(LEN) 함수 : ROW함수를 지정된 행번호를 반환하는 함수입니다. INDIRECT 함수는 문자열 형식의 참조를 만드는데 LEN 함수를 포함하면 1~글자수만큼의 숫자를 형성합니다.

이 문구가 "배열"을 형성해서 "A1" 셀에 있는 글자 수만큼 {1, 2, 3, 4, ... , 글자수(N)} 배열(행렬)을 만듭니다.

앞의 MID함수를 이용하면 "A1" 셀의 글자를 쪼개서 한글자씩 반환하는 함수가 됩니다..

③ VALUE 함수 : 이렇게 추출한 글자를 숫자형식으로 변환하는데 숫자가 아닌 경우 오류가 발생하게 됩니다. 이 VALUE 함수가 없다면 숫자 2가 있다고 해도 문자형식으로 받아들입니다.

④ ISNUMBER 함수 : VALUE를 정상적으로 통과해서 숫자로 변환된 경우 1을 반환하고, 아닌 경우에는 0을 반환합니다.

⑤ IF 함수 : ISNUMBER에서 숫자로 판명되어 1(TRUE)로 반환된 경우 그 숫자를 그대로, 0(FALSE)이 된 경우에는 ""(빈칸)으로 반환하게 합니다. 이 ""를 ","로 변경하면 모든 텍스트가 ,(쉼표)로 반환됩니다.

⑥ CONCAT : 글자 하나씩 판명한 결과를 한 셀에 합칩니다. CONCAT 함수가 없다면 숫자들이 {1, 2, 3 ...} 식으로 여러개로 반환됩니다.

⑦ 결과 : ROW+INDIRECT(LEN)으로 배열을 만들고 MID함수가 한글자씩 추출합니다. 그걸 VALUE를 써서 숫자로 바꾼후에 ISNUMBER로 숫자인지 아닌지 검사해서 IF로 분리합니다. 그리고 CONCAT으로 다시 합쳐 결과로 반영합니다.

 

---

 

문자에서 숫자만 혹은 특정한 규칙만 추출하는 기본 기능, 함수가 있어도 될 것 같은데 없습니다.

그래서 여러가지로 공부를 많이하게 되었습니다.

여러가지 함수나 기능들을 사용했지만, 오늘 본 함수가 아마 최종판일 것 같습니다.

복잡해서 저도 사용할 때마다 이렇게 미리 적어둔 곳에서 복사 붙여넣기로 사용합니다.

하시는 일에 도움이 되었으면 좋겠습니다.