최초 작성일: 2000-08-11
최종 수정일: 2026-09-29
조회수: 32 회
작성자: 권현욱 (엑셀러)
강의 제목: 숫자만 발췌해서 계산하기

들어가기 전에

오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.

질문 하나

다음과 같은 형식으로 자료를 입력하였습니다. 그런데 보시는 바와 같이 한 셀안에 문자열과 숫자가 함께

있는데 이것의 합계를 구해야하는 상황이 생겼습니다. 이 상태에서 그냥 sum이나 곱하기 등을 하게 되면

제대로 계산이 되지 않습니다. 무슨 방법이 없겠습니까?

팀명	인원수	금액	15000
1팀	10명	150,000	=$E$14*VALUE(LEFT(C15,2))
2팀	14명	210,000	=$E$14*VALUE(LEFT(C16,2))
3팀	20명	300,000	=$E$14*VALUE(LEFT(C17,2))
4팀	17명	255,000	=$E$14*VALUE(LEFT(C18,2))
팀계		915,000

저자: 권현욱(엑셀러), 아이엑셀러 대표

필자는 Excel 컨설턴트, 작가, 그리고 크리에이터입니다. 현재 Microsoft Excel MVP이며, 『챗GPT+엑셀 업무자동화 정석』을 비롯한 10여 권의 도서를 집필했습니다. Excel 자동화 및 생산성 향상 분야에서 25년 넘는 경력을 보유하고 있습니다.

권현욱(엑셀러) 님의 최신 포스트:

최신 글을 불러오는 중...

숫자만 발췌해서 계산하기

핵심 요약

숫자 뒤에 “명” 같은 단위를 보여주기만 할 목적이라면 값을 문자열로 입력하지 말고 사용자 정의 서식으로 표시하는 것이 좋습니다.
이미 문자열로 입력된 데이터는 SUBSTITUTE로 단위 문자를 제거한 뒤 VALUE로 숫자로 변환할 수 있습니다.
여러 셀의 문자열 숫자를 한꺼번에 합산할 때는 SUBSTITUTE와 VALUE를 배열수식으로 조합할 수 있습니다.

참고: 위 표의 마지막 열은 금액을 계산하는 수식이며(단가 15000 × 인원수), 셀 주소($E$14, C15 등)는 원본 워크시트 기준입니다.

질문하신 분은 금액부분은 Value() 함수와 Left() 함수를 사용하여 구하셨더군요. 위의 표에서 보시는 바와 같이 말입니다. 그런데 이것도 인원수가 동일하게 2자리일 경우에는 가능한데 자리수가 다 틀리다면 다른 방법을 사용해야 했을 것입니다.

그리고 인원수의 경우에는 그냥 공란으로 보내오셨는데 마찬가지로 Value(), Left() 등의 함수를 사용하면 비슷하게 답을 구하실 수는 있었을 것입니다. 그런데 이러한 방법은 그다지 바람직한 방법은 아니라고 생각 됩니다.

가장 바람직한 방법은(제 기준에서 그렇다는 것이지요 ^^),

인원수에 해당되는 부분, 즉 OO명과 같이 문자열"명"이 포함되어야 하는 경우에는 사용자정의 서식을 사용 하시는 것이 좋을 것 같습니다. 즉 아래의 빨간 테두리선 부분에 사용자 정의 서식(#,#"명")을 설정해 주면 그냥 숫자만 입력하더라도 "명"이 자동으로 입력이 되며, 각종 계산작업도 제대로 이루어지게 될 것입니다.

팀명	인원수	금액
1팀	10	150,000
2팀	14	210,000
3팀	20	300,000
4팀	17	255,000
팀계	61	915,000

그런데 만약에 자료가 이미 다 입력이 되어 있어서 이 방법을 적용할 수 없는 경우도 있겠지요? 이럴 때에는 할 수 없이 몇 가지 함수를 사용해서 변환을 해 주어야 할 것입니다. 아래의 표에서 "인원수2" 부분을 살펴보세요.

팀명	인원수	인원수2	금액
1팀	10명	10	150,000
2팀	14명	14	210,000
3팀	20명	20	300,000
4팀	17명	17	255,000
팀계	61	61	915,000

아래와 같은 공식이 입력되어 있습니다.

=VALUE(SUBSTITUTE(C51,"명",""))

Substitute() 함수는 예전에 설명을 드렸는데 기억이 나시는지? 특정 문자열을 다른 문자열로 치환하는 함수 입니다. 그리고 Value() 함수는 소개해 드렸던가요? 숫자 형태의 문자열 자료를 숫자로 바꾸어 주는 함수입 니다. 따라서 이 두 개의 함수를 조합해서 위와 같이 사용을 하게 되면 "명"이라는 문자열을 제거한 다음, 완전한 숫자 형태로 바뀌게 되는 것이지요.

한 가지 더!!!!

배열수식을 사용해서 계산해 줄 수도 있습니다. 위의 빨간색 셀을 보세요. 아래와 같은 수식이 들어 있을 것 입니다. 배열수식이니까 당연히 Ctrl + Shift + Enter키를 쳐 주어야 겠지요?

{=SUM(VALUE(SUBSTITUTE(C51:C54,"명","")))}

배열수식은 여러 차례 설명드렸으므로 위 수식에 대한 해설은 생략해도 될 것 같군요.

그럼…

정리 — 숫자만 발췌해서 계산하기

구분	내용
문제	10명처럼 숫자와 문자가 섞인 셀은 합계 등 계산이 제대로 되지 않음
권장	숫자만 입력하고 사용자 정의 서식 `#,#"명"`으로 표시
이미 입력된 자료	`=VALUE(SUBSTITUTE(C51,"명",""))`
합계 (배열수식)	`{=SUM(VALUE(SUBSTITUTE(C51:C54,"명","")))}` — 예제 결과 61