Excel 엑셀 숫자만 추출하는 방법 3가지

이 글에서는 Excel 엑셀 숫자만 추출하는 방법 3가지에 대해서 정리해 보도록 하겠습니다.

엑셀을 사용하다 보면 셀에서 숫자만 추출해야 하는 경우도 있습니다. 몇 개 되지 않는 다면 일일이 손으로 옮겨 적는 방법도 있겠습니다만, 함수로 자동으로 이런 기능을 하게 할 수도 있는데요. 여러 가지 방법이 있겠지만, 아래 방법 중에서 제일 적합한 방법을 사용해 보시면 좋을 것 같습니다.

SUBSTITUTE 함수로 Excel 엑셀 숫자만 추출하기

SUSTITUTE로 추출하기
  1. SUBSTITUTE 함수는 셀에서 제거하고 싶은 텍스트를 제외한 나머지 숫자를 돌려줍니다.

예를 들어 [2020년] 이라는 셀이 있을 경우 =SUBSTITUTE(해당셀,”년”,””) 이런 식으로 함수를 구성하면 년 자를 뺀 숫자만 돌려주게 됩니다. 셀에 일정한 텍스트가 있을 때는 굉장히 편하게 추출이 가능하겠죠?

LEFT, RIGHT 함수로 숫자만 추출하기

LEFT, RIGHT 함수로 추출하기
  1. LEFT, RIGHT, MID 함수는 셀에서 원하는 위치의 텍스트만 돌려주는 함수입니다. 위의 예제에서 확인해 보면 “2020년 2월 1일” 이라는 셀의 앞 2020까지만 가지고 오고 싶을 때 LEFT(원본셀,가져올 텍스트 갯수)에서 가져올 숫자의 개수를 4자로 하면 앞의 4글자만 가지고 오므로 결괏값은 2020이 반환됩니다.
  2. RIGHT 함수의 경우도 오른쪽에서부터 원하는 갯수만큼의 텍스트를 가지고 오는 함수로 RIGHT(원본셀,가지고올 텍스트 갯수)를 설정하면 숫자만 가지고 오게 됩니다. 

MID 함수로 중간 숫자만 추출하기

MID 함수로 중간 숫자만 추출하기
  1. MID함수는 가져올 텍스트가 몇번째부터 몇 개를 가지고 올지 설정하고 그 값을 되돌려주는 함수입니다. 만약 원본 셀에서 추출하고 싶은 숫자가 가운데에 있을 경우 MID(원본셀,가지고올 시작 위치, 가지고올 갯수)로 설정할 수 있습니다.

예를 들어 “책392페이지”의 경우 추출해야 할 숫자가 가운데 있으므로 MID(원본셀,2,3) 일 경우 392가 반환됩니다.

날짜형식으로 되어있을 때
  1. 만약 원본셀이 날짜 형식으로 되어 있을 경우 MID 함수로는 추출이 불가능합니다. 이럴 경우에는 YEAR 함수나, MONTH, DAY 함수를 사용하여 해당 값을 추출할 수 있습니다.

복합 함수 구성으로 엑셀 숫자만 추출하기

복합함수로 추출하기
  1. 복합 함수를 이용하여 원본 셀에서 숫자만 추출할 수 있습니다.

=SUMPRODUCT(MID(0&원본셀,LARGE(ISNUMBER(–MID(원본셀, ROW($1:$50),1))*ROW($1:$50),ROW($1:$50))+1,1)* 10^(ROW($1:$50)-1))

(2021-05-02 추가) 위의 함수는 배열수식입니다. 엑셀 2010,2013,2016,2019버전에서는 배열수식을 입력할 때는 컨트롤키 + 쉬프트키 + 엔터키 를 눌러 입력합니다. 최신버전인 오피스365(엑셀365)버전에서는 엔터키만 눌러도 배열수식이 입력됩니다. 참고하시기 바랍니다.

위의 함수는 여러가지 복합 함수로 복잡하게 구성이 되어 있습니다. 작동 원리는 아래와 같습니다.

  1. MID 함수로 원본값의 텍스트를 하나하나 반환합니다.
  2. ISNUMBER로 하나씩 반환된 텍스트들이 숫자인지 아닌지 확인하고 숫자일 때 TRUE값을 보냅니다.
  3. 숫자가 몇번쨰 있는지 TRUE(1) 값에 1~50까지의 숫자로 곱하면 숫자가 몇 번째에 있는지 알 수 있습니다. 위의 예에서는 [1,2,3,4,6,7,9,10]이 됩니다.
  4. LARGE 함수로 이를 높은 순서대로 정렬합니다. [10,9,7,6,4,3,2,1]
  5. 순서대로 정렬한 10,9,7,6,4,3,2,1에서 1을 일률로 더해줍니다. [11,10,8,7,5,4,3,2]
  6. 숫자가 없는 경우 에러가 발생되기 때문에 원본셀앞에 0을 붙여줍니다. (0&원본셀) = [02020년02월19일]
  7. MID 함수로 [11,10,8,7,5,4,3,2] 번째 숫자를 추출합니다 [ 9, 1, 2, 0, 0, 2, 0, 2 ] 여기에 [ 0, 10, 100, 1000, 10000, 100000, 1000000 ]를 곱하여 합합니다.(SUMPRODUCT)
  8. 원본 셀에서 숫자만 추출됩니다.

오늘은 Excel 엑셀에서 숫자 추출하는 방법에 대해서 알아봤습니다.

지금까지, 안테나곰이었습니다. 

댓글 남기기