엑셀 문서를 다루다 보면 셀 안에 숫자와 문자가 함께 입력되어 일반적인 SUM 함수로 합계가 구해지지 않는 상황을 자주 겪게 됩니다.
기본적으로 엑셀은 텍스트가 섞인 데이터를 문자로 인식하기 때문에 단순 덧셈 수식을 적용하면 0으로 표시되거나 오류가 발생합니다.
본 가이드에서는 엑셀에서 문자가 포함된 숫자 데이터만 정확히 추출해 계산하고 더하는 2가지 핵심 함수 조합 방법을 정리해 드립니다.
문자가 포함된 셀에서 일반 합계 계산이 안 되는 이유
엑셀의 텍스트 인식 구조와 계산 오류
엑셀은 셀에 한 글자라도 텍스트가 섞이면 전체 데이터를 문자열로 처리합니다.
문자열로 인식된 데이터는 SUM과 같은 기본 사칙연산 함수에서 계산 대상에서 제외되어 합계가 정상적으로 구해지지 않습니다.
따라서 텍스트 숫자 계산을 진행하려면 텍스트를 제거하거나 숫자 부분만 따로 분리한 뒤, 이를 다시 숫자로 변환하는 과정이 필요합니다.
방법 1. MID와 INT 함수를 활용한 숫자 추출 및 더하기
MID 함수로 특정 위치의 숫자만 분리하기
숫자의 자릿수와 위치가 일정하다면 MID 함수를 활용하여 원하는 숫자 영역만 지정해 가져올 수 있습니다.
예를 들어 B3부터 B8 셀 범위에서 첫 번째 글자부터 두 자리의 숫자만 가져오고자 할 때는 아래와 같이 범위를 지정합니다.
- 수식:
=MID(B3:B8, 1, 2)
INT 함수로 숫자 변환 후 SUM으로 합계 구하기
MID 함수로 잘라낸 결과값은 겉모습만 숫자일 뿐 여전히 텍스트 형태이므로 그대로는 더하기가 불가능합니다.
이를 실제 연산 가능한 정수형 숫자로 인식시키기 위해 INT 함수로 감싸고, 최종적으로 SUM 함수를 중첩해 합계를 구합니다.
- 최종 수식:
=SUM(INT(MID(B3:B8, 1, 2)))
수식을 입력한 뒤 실행하면 텍스트가 섞인 셀 범위 내 숫자들만 정상적으로 더해진 결과값을 확인할 수 있습니다.
방법 2. SUBSTITUTE와 INT 함수를 활용한 특정 문자 제거 및 더하기
SUBSTITUTE 함수로 불필요한 문자 일괄 치환하기
숫자의 자릿수가 제각각이거나 특정 단위 문자(예: “avd”, “개”, “원” 등)가 고정되어 붙어 있다면 SUBSTITUTE 함수가 적합합니다.
SUBSTITUTE 함수는 지정한 셀 범위에서 불필요한 특정 문자를 빈 문자열(“”)로 바꾸어 깔끔하게 제거해 줍니다.
- 수식:
=SUBSTITUTE(B3:B8, "avd", "")
텍스트 제거 후 정수 변환 및 전체 합치기
특정 문자가 지워진 후 남은 숫자는 텍스트 상태이므로, INT 함수를 통해 숫자로 변환한 뒤 SUM 함수로 묶어 연산합니다.
- 최종 수식:
=SUM(INT(SUBSTITUTE(B3:B8, "avd", "")))
이 수식을 활용하면 번거롭게 셀을 하나씩 수정하지 않고도 특정 문자가 포함된 열 전체의 숫자 합계를 한 번에 산출할 수 있습니다.
상황에 맞는 최적의 수식 선택 기준
데이터 규칙성에 따른 함수 조합 선택
글자 수가 일정하게 고정되어 있다면 MID 함수를 이용하는 것이 빠르고 직관적입니다.
반면 숫자의 길이나 자릿수가 불규칙하고 제외해야 할 특정 단어 및 단위가 명확하다면 SUBSTITUTE 함수를 적용하는 것이 안전합니다.
두 방식 모두 핵심은 텍스트를 제거하거나 분리한 뒤 INT 함수를 거쳐 숫자로 인식시키는 데 있습니다.
자주 묻는 질문
Q1. 수식을 그대로 입력했는데 에러(#VALUE!)가 발생하면 어떻게 해야 하나요?
A1. 범위 내에 빈 셀이 있거나 제외할 문자 외에 다른 텍스트나 공백이 섞여 있을 때 에러가 발생할 수 있습니다. 수식 안의 텍스트가 셀 데이터와 정확히 일치하는지 확인하고, 구버전 엑셀인 경우 수식 입력 후 일반 엔터 대신 Ctrl + Shift + Enter를 눌러 배열 수식으로 입력해야 합니다.
Q2. INT 함수 대신 사용할 수 있는 다른 숫자 변환 방법이 있나요?
A2. INT 함수 외에도 문자를 숫자로 변환해 주는 VALUE 함수를 사용하거나, 추출한 수식 뒤에 *1 또는 --(더블 마이너스)를 붙여 연산 가능한 숫자로 자동 치환할 수 있습니다.
Q3. 셀마다 제거해야 할 특정 문자가 각각 다르면 어떻게 계산하나요?
A3. 여러 종류의 문자가 섞여 있는 경우 SUBSTITUTE 함수를 중첩하여 =SUBSTITUTE(SUBSTITUTE(범위, "단위1", ""), "단위2", "") 형태로 순차적으로 제거하거나, 엑셀의 ‘텍스트 나누기’ 기능 또는 ‘빠른 채우기(Ctrl + E)’를 활용해 숫자를 별도 열로 분리한 후 합계를 구하는 것이 효율적입니다.
추천글