엑셀 셀에서 숫자만 추출 하는 방법

 엑셀을 사용하다보면 문자와 숫자 값이 같이 입력된 셀에서 문자를 제외하고 숫자만 사용해야하는 경우가 있다.

 이런 경우 방법을 아는사람이라면 숫자를 추출하여 쉽게 사용하겠지만 방법을 모르는 사람이라면 숫자를 추출하면 좋겠지만 어떻게 추출해야 하는지 방법을 몰라 난감할 수 있을 텐데 함수를 사용하든 메크로를 사용하든 방법은 많이 있지만 메크로라고 하면 아무래도 거부감이 있는 사람도 많다보니 여기서는 함수를 사용한 방법 몇 가지를 정리해 봤다.

엑셀 숫자만 추출 하는 방법

1. SUBSTITUTE 함수.

 셀에 문자가 일정하게 입력되어있다면 SUBSTITUTE 함수를 이용하여 문자를 공백으로 바꾸고 숫자만 남게 하는 것으로 숫자만 추출한 것과 같은 효과를 얻을 수 있다.

 예를 들면 전화번호나 주민등록번호 처럼 '-'와 같은 값이 일정하게 들어있는 경우 사용이 가능하다고 보면 된다.

2. MID 함수.

 문자가 일정하지는 않지만 숫자의 위치가 일정한 경우에는 MID 함수를 이용하여 해당 위치의 값과 범위를 넣고 추출하는 방법도 있다.

 예를 들면 년, 월, 일과 같이 숫자의 위치가 일정하다면 사용이 가능하며 숫자 앞에 특정한 문자가 항상 있다면 FIND와 같이 사용하는 것도 좋으며, 셀에 사용한 문제 전체의 값이 필요하다면 LEN과 같이 사용할 수도 있다.

3. 문자와 숫자가 일정하지 않은 경우.

 위의 두 방법은 엑셀을 조금 해봤다 하는 사람들은 모두 알고 있을 것이다.

 하지만 문자와 숫자가 일정한 패턴없이 섞여있는 경우라면 조금 골치 아파질 수 있는데 이런 경우에는 메크로를 사용하는 것이 더 편할 수도 있지만 여러가지 함수를 조합한다면 함수 만으로도 숫자를 추출할 수는 있다.

=SUMPRODUCT(MID(0&B3,LARGE(INDEX(ISNUMBER(-MID(B3,ROW($1:$50),1))*ROW($1:$50),0),ROW($1:$50))+1,1)*10^ROW($1:$50)/10)

(빨간 부분은 사용자가 변경하여 사용하는 부분으로 B3은 대상 셀, $1:$50은 문자 최대수라고 보면 되는데 숫자가 높으면 그만큼 계산이 오래걸릴 수 있으니 적당한 수로 넣는 것이 좋다.)

 위 수식을 간단하게 설명하자면 A3에 있는 값을 MID와 ROW($1:S50)) 함수를 통해 셀의 문자를 최대 50자 까지 뽑아낸다.

 그 다음 ISNUMBER 함수를 이용해 문자를 제외한 숫자 값을 찾아 내고 INDEX 함수로 배열에 넣은 뒤 LARGE함수로 찾은 숫자 값의 자릿수 별로 곱한뒤(x1, x10, x100...)마지막으로 SUMPRODUCT로 각 배열값을 더하면 셀에서 숫자만 나타나는 형식이다.