HOWTO APPS/MS Office작성일 : 2020. 9. 2.

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

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

 

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

 

목차

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

    SUSTITUTE로 추출하기

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

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

     

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

    LEFT, RIGHT 함수로 추출하기

    ▼ LEFT, RIGHT, MID 함수는 셀에서 원하는 위치의 텍스트만 돌려주는 함수입니다. 위의 예제에서 확인해 보면 "2020년 2월 1일" 이라는 셀의 앞 2020까지만 가지고 오고 싶을 때 LEFT(원본셀,가져올 텍스트 갯수)에서 가져올 숫자의 개수를 4자로 하면 앞의 4글자만 가지고 오므로 결괏값은 2020이 반환됩니다.

     

    ▼ RIGHT 함수의 경우도 오른쪽에서부터 원하는 갯수만큼의 텍스트를 가지고 오는 함수로 RIGHT(원본셀,가지고올 텍스트 갯수)를 설정하면 숫자만 가지고 오게 됩니다. 

     

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

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

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

     

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

    날짜형식으로 되어있을 때

    ▼ 만약 원본셀이 날짜 형식으로 되어 있을 경우 MID 함수로는 추출이 불가능합니다. 이럴 경우에는 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))

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

    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 엑셀에서 숫자 추출하는 방법에 대해서 알아봤습니다.

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