Please Enable JavaScript!
Gon[ Enable JavaScript ]

엑셀 Excel 문자가 포함된 숫자만 합치기

엑셀(Excel)/Excel
반응형

엑셀에서 연산할 때 텍스트가 포함된 숫자는 합산이 되지 않습니다. 그림처럼 숫자가 아닌 문자는 0 입니다. 합산한 결과값은 0 이 되겠죠. 텍스트에 포함된 숫자만 합치는 방법에는 어떤 것들이 있을까요?

 

1. 텍스트가 일정한 자릿수를 차지하는 경우

 

위치가 일정한 문자열인 경우 MID 함수를 이용해서 숫자만 자른 후 합계를 구할 수 있습니다. MID 함수의 형식은 아래와 같습니다. 매개변수로 숫자가 시작되는 위치와 잘라낼 문자 개수를 입력합니다.

 

MID(, 시작지점, 잘라낼 문자 개수)

 

MID 함수를 이용해서 잘라낸 숫자는 한 번 더 변환을 해야 합니다. 반환된 값은 텍스트이기 때문에 INT 함수로 데이터 형식을 숫자로 변환해야 합니다. 그래야 합계가 가능하겠죠.

 

다음으로 SUM, INT, MID 함수와 배열 수식을 이용해서 최종 결과값인 합계를 구해 보겠습니다. 위에서 숫자를 추출했던 수식과 SUM() 을 합쳐서 아래와 같이 만듭니다.

 

=SUM(INT(MID(B54, 1, 2)))

 

 

두 번째는 배열 수식을 적용하기 위해 MID 함수의 첫 번째 파라미터 값으로 범위를 지정합니다.

 

마지막으로 배열 수식을 적용하기 위해 Ctrl + Shift + Enter 단축키를 클릭합니다. 그림처럼 수식 양끝에 {} 대괄호가 있으면 배열 수식이 적용된 것입니다.

 

{=SUM(INT(MID(B54:B58, 1, 2)))}

 

배열 수식과 관련된 내용은 아래 포스팅을 참고하세요.

 

 ※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다.
엑셀(Excel) 배열수식을 이용해 두개의 열에 만족하는 찾기
엑셀(Excel) 배열수식을 이용해서 에러를 제외한 합산하기
엑셀(Excel) 배열수식을 이용해 여러 조건에 맞는 행의 수를 세고 싶을
셀(Excel) 배열수식을 이용해 여러조건에 맞는 합치기



 

 

결과는 다음과 같습니다. 바로 옆 열에서 숫자를 추출한 후 합계를 구한 값과 같다는 것을 알 수 있습니다.


 

 

2. 텍스트 삭제해서 숫자만 남긴 후 합계 구하기

 

두 번째는 동일한 문자가 들어간 경우 사용할 수 있는 방법입니다. 이 방법은 문자의 위치에 상관없이 적용할 수 있습니다. , 삭제할 문자는 일정해야 합니다. 방법은 함수를 이용해서 문자를 공백으로 바꿉니다. 그럼 삭제가 되고 숫자만 남겠죠. 함수는 SUBSTITUTE 를 사용합니다. SUBSTITUTE 에서 삭제할 문자를 입력하고 교체할 문자로 공백을 입력합니다.

 

SUBSTITUTE (, 삭제할 값, 교체할 값)

 

수식은 아래와 같습니다. SUBSTITUTE 를 이용해서 텍스트를 삭제하고 결과값을 숫자로 변환하기 위해 INT 함수를 사용합니다.

 

=INT(SUBSTITUTE(B54, "FT", ""))

 

 

 

다음은 첫 번째 사용했던 것과 동일하게 배열 수식을 적용해서 한 번에 합계를 구하겠습니다. SUBSTITUTE 의 첫 번째 매개변수에 셀 범위를 지정하면 배열 수식을 적용해야 정상적인 결과값을 얻을 수 있습니다.

 

=SUM(INT(SUBSTITUTE(B54:B58, "FT", "")))

 


 

 

배열 수식 적용을 위해 단축키 Ctrl + Shift + Enter 을 클릭합니다.

 

{=SUM(INT(SUBSTITUTE(B54:B58, "FT", "")))}

 


 

 

3. VBA 숫자만 분리한 후 합계 구하기

 

첫 번째와 두 번째에서 다룬 방법은 일정한 위치와 문자일 경우 적용이 가능합니다. 만약 무작위로 포함된 텍스트를 제거하고 숫자만 추출해서 합치고 싶다면 VBA 코드로 작성해야 합니다. 내용이 길어서 별도로 작성한 포스팅의 링크를 걸어 놨습니다. 참고하세요.

 

셀 VBA 문자가 포함된 숫자만 합치기
반응형
Posted by 녹두장군

댓글을 달아 주세요