엑셀 숫자만 추출하기 합산방법 진짜 1초 완성

엑셀 수많은 텍스트 중에서 숫자만 추출하는 방법과 추출한 숫자를 더하는 방법에 대해서 알아보겠습니다.

2023년 들어서 엑셀에 많은 기능들이 추가되고있습니다. 365 버전에 파이썬도 호환이 가능하고, 실무에 다양하고 효율적으로 사용 할 수 있는 방법들을 계속해서 제공하는 것 같습니다. 

이번 포스팅에서는 엑셀에서 숫자들이 포함된 텍스트에서 숫자를 간단하게 추출하는 방법에 대해서 알아보겠습니다.

엑셀 숫자만 추출하기

 

엑셀 숫자만 추출 방법 (단축키 활용)

먼저 텍스트와 숫자가 있는 셀 옆으로 이동해서 텍스트를 제외한 숫자를 입력합니다.

텍스트에서 숫자만 추출하기

1. ABC123EEE500 에서는 ABCEEE를 뺀 123500을 입력합니다.

2. 소고기500G 에서는 소고기G를 뺀 500을 입력합니다.

항상 2개 이상의 셀 내의 텍스트 외에 숫자를 적어주세요.

그래야 규칙이 생성되어서 빠른채우기가 가능합니다.

그럼 모든 준비는 끝났습니다.

 

 

텍스트에서 숫자추출 단축키

규칙을 입력 한 후, 세번째 셀에서 컨트롤 + E (빠른채우기) 를 누르면 끝났습니다.

엑셀은 2013버전 이후에서만 가능하다고 하니 참고 바랍니다.

 

엑셀 숫자만 추출 후 합산하기

엑셀 숫자만 추출 후 합산을 할때는 단위가 동일하거나 합계값이 명확히 나오는 값들에 대해서만 가능한 점 참고 바랍니다.

숫자만 추출 후 합산하기

위와 같이 사과 2000g , 소고기 500g, 쌀 1030g 과 같이 동일한 단위에 한해서 숫자를 추출 후 합산을 해서 총 몇 g인지 구할 수 있습니다.

방법은 컨트롤 + E 와 동일 합니다.

먼저 첫 번째와 두 번째 셀의 숫자만 먼저 옆에 셀에 입력을 합니다.

사과2000g과 소고기500g 에서 숫자만 옆에 셀이 입력을 합니다.

셀 숫자 추출 후 합계구하기

컨트롤 E로 숫자만 추출 했으면 SUM 함수를 활용해서 숫자의 합계를 구해줍니다.

 

 

엑셀 숫자만 추출 방법 (함수사용)

함수를 사용해서 숫자만 추출하는 방법은 데이터가 일정하거나 복잡한 규칙이 없다는 가정하에 가능합니다.

또한 엑셀 365 버전에서 가능 하단점 참고바랍니다.

엑셀 숫자만 추출 방법 함수사용

함수는 다음과 같습니다.

=TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),""))

함수풀이

ROW(INDIRECT("1:"&LEN(A1)))

A1번 셀 텍스트 개수를 len 함수로 구합니다.

ROW(INDIRECT("1:"&7)))

배열로서 1,2,3,4,5,6,7을 아래로 만들어 냅니다.

 

MID(A1,{1,2,3,4,5,6,7},1) 이라는 값이 나오겠죠.

그럼 

1 = 사

2 = 과

3 = 2

4 = 0

5 = 0

6 = 0

7 = g

라는 값들이 배열 반환이 됩니다.

 

MID(A1,{1,2,3,4,5,6,7},1) * 1

 

곱하기 1을 해주는이유는 숫자값 외에는 오류값이 뜨게끔 처리를 합니다.

 

엑셀 오류 종류알아보기

 

다시 정리하면,

 

1 = #VALUE!

2 = #VALUE!

3 = 2

4 = 0

5 = 0

6 = 0

7 = #VALUE

 

라는 값들이 배열로 나오게 됩니다.

 

=IFERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1,"")

 

IFERROR 함수를 통해 오류값은 ""으로 반환하게합니다.

 

그럼

1 = "'

2 = ""

3 = 2

4 = 0

5 = 0

6 = 0

7 = ""

으로 나오게됩니다.

 

=TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),""))

마지막으로 TEXTJOIN 함수로 ""을 구분자로 텍스트를 합칩니다.

즉, 2000이 나오게 됩니다.

 

위와 같은 방법 외에도 VBA를 활용해서 숫자를 추출하는 방법도 있고, 다른 함수방법을 사용해서 숫자를 추출 할 수 있습니다.

 

이 포스팅을 통해서 텍스트에서 숫자를 추출하는 간단한 방법과 추출한 숫자의 합계를 구하는 벙법에 대해서 알아보았습니다.

 

 

알고있으면 유용한 기능들

엑셀 더하기 빼기 곱하기 나누기 함수 총정리 5분완성

엑셀 간트차트 만드는방법 양식만들기 템플릿 배포

엑셀 CHOOSE 함수 알아보기 CHOOSE함수의 모든것

엑셀 빈칸채우기 자동채우기 자동완성 단축키

엑셀 빈칸 없애기 빈셀 삭제 단축키

 

엑셀관련 오픈톡방