숫자문자 섞인 셀에서 숫자만 더하기 (바꾸기/서식/배열합계)

숫자문자가 섞인 셀의
문자 제거 후 숫자의 합계 
1. 문자 포함 셀의 SUM 오류
셀내 문자포함, 범위내 문자 포함
2. 해결 방법
 * 바꾸기 (사용자 지정 서식) *
* 배열 함수 *
 빈칸이면 0으로 표현하라 IF(셀="",0)
문자 지울 때 SUBSTITUTE
텍스트 숫자를 숫자 형식으로 VALUE
배열 합계
{=SUM(VALUE(IF(범위="",0,SUBSTITUTE(범위,"문자",""))))}

안녕하세요. Excelcopycat 입니다.

문자와 숫자가 섞인 Data를 정리할 때
매우 유용한 기술과 함수를
한번 알아보려 합니다.
앞으로 보실 바꾸기와 사용자지정은
수 많은 노가다를 한번에 풀어버리는
 핵심 기능입니다.
배열 함수 부분은 시간 되실 때
천천히 읽어보세요.
함수 응용하시는데 도움이 될겁니다.

---

숫자만더하기 예시.xlsx

---

[참고]
텍스트 형식 숫자의 합계 오류 해결법은
아래 링크를 참조해주세요.

2020/10/07 - [함수-수식] - 엑셀 SUM 더하기 (여러범위/빠르게/누적/텍스트합계)

---

---

---

---

---

---

---

---

1. 문자 포함 셀의 SUM 오류

여러 Data들의 합은
SUM으로 할 수 있죠.
=SUM(범위) 또는 SUM(셀,셀,범위)

그런데 data 값이 1개, 1대 등
문자가 들어있으면 계산이 안되죠.
숫자가 아니니까요.
텍스트 형식의 숫자 더할 때 생기는
오류와는 다른 겁니다.
지금 보는 것은 셀 안에
실제 문자가 들어있는 경우예요.
텍스트 형식 숫자의 SUM 오류 해결법은
아래 링크를 참조해주세요.

2020/10/07 - [함수-수식] - 엑셀 SUM 더하기 (여러범위/빠르게/누적/텍스트합계)

이제 시작해볼게요.

예시1 : 숫자만 있음. SUM 합계 OK!
예시2:  "개" 문자 있음
숫자 부분만 더해짐
예시3,4 : 문자 때문에 값 0 나옴

---

2. 해결 방법

1) 바꾸기 (Ctrl + h)

이 방법은 범위내에 있는
특정 글자를 원하는 글자로
바꾸는 겁니다.
숫자, 문자 다 가능합니다.
"이글자"를 "저글자로" 바꾸는 건데요,
글자수 제한도 없고,
숫자, 문자, 기호도 다 적용됩니다.

방법
셀 또는 범위 위에서
Ctrl 키를 누르고 h를 눌러보세요.
찾기 및 바꾸기란 창이 하나 뜨죠?
바꾸기 탭이 선택되어 있습니다.
(Ctrl f 하면 찾기 탭으로 열림)

찾을 내용(N)에 개 를 적습니다.
바꿀 내용(E)에는 아무것도 안 적습니다.
바꿀 내용에 스페이스바 하시면
 1개가 1 이 됩니다.
1 뒤에 띄어쓰기 보이시나요?
이 점 주의해 주세요.

위에 처럼 적으셨으면
 모두 바꾸기(A)를 누르세요.
그러면 6개 항목이 바뀌었다고
창이 뜨면 확인 눌러주세요. (또는 엔터) 

합계가 제대로 되었네요.

---

※ 주의

바꾸기 하실 때 바꾸려는 값을
잘 정하셔야 되요. 예를 들어,
=SUM(P3:S3) 라는 함수에서
S3 대신에 R3으로 바꾸는 경우,
찾을 내용에 S, 바꿀 내용에 R 으로 하면?
=RUM(P3:R3) 로 되면서 오류 나요.

그래서 바꿀 내용의 앞뒤로 있는
2~3글자 더 적는 것이 좋습니다.
찾을 내용에 :S, 바꿀 내용에 :R
이렇게요.

---

위에 바꾸기 기능 정말 유용합니다.
다른 업무에 꼭 활용해보세요.
그런데 표에는 단위가 있어야죠.
표 위에 (단위 : 개) 를 명기하면 되지만
그래도 표 안에 "개" 가 있어야 한다면...
사용자 지정 서식으로 해결할 수 있어요.

사용자 지정 서식 (Ctrl + 1)

엑셀에서 멋진 기능 중 하나는
숫자를 문자와 같이
보이게 할 수 있는 것입니다.
실제는 숫자라서 계산도 가능하고
단위 같은 문자와 같이 표현되어
표 이해를 더 쉽게 만들어주죠.

[사용자 지정 서식 적용 방법]

1. 서식을 바꿀 범위를 선택
2. Ctrl 키를 누른 상태에서 1 버튼을 누르기
   또는 선택한 범위(회색으로 변함)
위에서 마우스 우클릭, 셀 서식(F) 클릭
3. 표시 형식 탭 클릭
4. 좌측 메뉴 하단 사용자 지정 탭 클릭
5. 형식 칸에 "G/표준"이 선택되어 있음.
아니라면 목록 최상단 "G/표준" 선택
6. G/표준 옆에 따옴표를 이용하여
삽입하려는 문자 입력
"개", "_개", "\/KG" 등
특수기호, 숫자, 빈칸 전부 가능
7. 확인 버튼 클릭

이렇게 하면 SUM 더하기가 가능하고
단위 등의 글자도 같이 보이죠.

---

보통 바꾸기와 사용자지정 서식이면
업무 보는데 지장은 없어요.
추가 공부를 위해
함수를 이용해서 계산해볼게요.
천천히 갈테니, 잘 봐 주세요.

2) 배열 함수

제가 이렇게 수식을 만들면
계산이 될거라고 말로 해볼게요.

셀 안에 있는 문자를 지우고
남은 숫자들을 더해라!

여러 함수들이 조합되니까
단계별 단계별 설명하겠습니다.

[1단계]
문자를 지워라(빼라,바꿔라)
SUBSTITUTE 함수로 가능합니다.
=SUBSTITUTE(셀,"문자","바꿀값")
예를들면
=SUBSTITUTE("문자지워","지워","빼")
결과는 "문자빼" 입니다.

우리의 케이스 "개"를 없애야죠.
=SUBSTITUTE(C2,"개","")
바꿀값에 ""(큰따옴표2개)를 적으면
아무것도 적지 말라는 뜻이 되어
"개"가 지워지는 효과가 납니다.
아래 C4 셀을 보시면 "개"가 사라졌죠?
근데 옆에 E3 합계는 0이네요. 
아직 1 이 텍스트 형식 숫자라서 그래요.

[2단계]
텍스트 형식을 숫자 형식으로 바꾸죠.
=VALUE(셀)
이러면 셀의 텍스트형식 숫자가
진짜 숫자로 변환됩니다.
셀 값에는 위에서 만든
SUBSTITUTE 수식을 넣습니다.
=VALUE(SUBSTITUTE(A2,"개",""))

C2는 숫자로 바꿨으니,
D2도 바꾸고 둘을 더하면 되네요.
이제 1단계와 2단계를 더하면 되죠?
=VALUE(SUBSTITUTE(C2,"개",""))+VALUE(SUBSTITUTE(D2,"개","")) =21

[3단계]
Data 가 많으면 어쩌죠?
SUM을 써서 묶어줘야 할텐데...
잘 보시면 계산하려는 셀만 다르지
나머지 수식은 다 똑같지요?
그러면 수학 시간 때 배운 것처럼
이것들을 묶을 수가 있습니다.
셀 대신에 범위로 적어주고,
이 수식을 그냥 엔터가 아니라
Ctrl + Shift + Enter로 입력합니다.
그러면 { } 이렇게 대괄호로
자동으로 묶인 것을 볼 수 있습니다.
=SUM(VALUE(SUBSTITUTE(C2:D2,"개","")))
까지 적고 (바로 엔터치지 마세요)
Ctrl + Shift + ENTER!!
{=SUM(VALUE(SUBSTITUTE(C2:D2,"개","")))}
이렇게 수식이 입혀져서 계산이 됩니다.

주의 : 셀이 합쳐진 병합된 셀에서는
ctrl shift enter 안 됩니다.
병합된 셀을 풀고 다시 해보세요.

위에 예시를 보시면
없애야 하는 문자에 따라
Substitute 안의 문자를 바꿔주면 되죠.

---

합계 범위에 빈칸이 있을 수도 있죠.
 0값과 빈칸은 다른 겁니다.
1+0 과 1+빈칸의 결과는 같지만
셀값이 있고 없고가 다르게
영향을 주는 경우도 있어요. 

이번 경우에는 셀이 0인 경우
계산이 되지만 비워져 있는 경우는
에러 납니다.

간단히 해결할 수 있는 방법은
Data 범위는 선택하시고
 Ctrl h 하셔서 찾을 내용은 비우고
바꿀 내용에 0 적고
모두 바꾸기 하시면
빈칸들이 0으로 채워지고
합계가 정상으로 됩니다.
꼭 함수를 써야한다면...
수식에서 어디가 잘못 된건지 확인해보죠.

data2를 보니 Value(D4) 에 문제가 있네요.
VALUE는 값을 숫자로 바꿔주는 함수인데
빈 셀은 숫자로 못 바꿔주니까
에러가 난 것입니다.

해결 수식을 또 말로 만들어 볼까요?
빈 셀이 있다면 그건 0으로 적고,
data가 있으면
substitute 함수를 써서 더해라!
그러면 되겠죠? 말은 쉽네요 ㅋ

빈셀을 찾는 함수는 ISBLANK 입니다.
=ISBLANK(셀) 하면
빈셀이면 TRUE라고 값이 나오고
빈셀이 아니면 FALSE라고 나옵니다.

~라면 이렇게 하고 아니면 저렇게 하라
이건 IF 함수입니다.
=iF(5>3,"맞아요","틀려요")
이런식입니다.
(5와 3 대신에 셀을 클릭하셔도 됩니다)

두 수식을 조합하면,
=만일 =IF(
빈셀이면 ISBLANK(C2)=TRUE,
0으로 적고 0,
 아니면 셀의 "개"글자를 지워라
SUBSTITUTE(C2,"개","")) 

빈셀이던 C2는 0이 되었고
나머지들도 숫자로 잘 나왔네요.
지금 1,0,3 숫자의 상태는
0은 숫자이고 1,3은 텍스트 형식입니다.

VALUE로 숫자형식으로 바꿔주고,
범위도 B~D로 늘려주고,
SUM도 씌워준 후
Ctrl Shift Enter 치면 합계를 계산됩니다.

완성 수식은 다음과 같습니다.{=SUM(VALUE(IF(ISBLANK(B2:D2)=TRUE,0,SUBSTITUTE(B2:D2,"개",""))))}
답은 4 입니다!

---

수고 많으셨어요.
바꾸기랑 사용자지정서식
정말 자주 쓰게 되는 기능입니다.
이걸 몰랐을 땐 야근하면서
열심히 data 손봐서 계산했었는데 ㅋ
여러분은 그러지 마세요 :) 
뿌듯한 하루 보내세요~ 감사합니다.

도움이 되었다면 공감 부탁드립니다ㅎㅎ