구글 시트 일부 데이터 몇몇개의 값만 SUM함수를 이용해 합칠려고 했더니 값에 숫자와 텍스트가 있어서 ‘#VALUE!’ 문구가 나왔어요. 쉽게 숫자만 있어야하는데 텍스트가 있어서 안된다는 말인데요. 이 경우 셀 속에 텍스트를 없애주고 값을 숫자로 인식하게 만든 다음 합치면 된다고 합니다. 구체적인 방법을 알아볼게요.
엑셀 텍스트 형식으로 저장된 숫자 변환 방법
엑셀 텍스트 형식으로 저장된 숫자로 변환하는 가장 간단한 방법은 문자를 지워주는 겁니다. US$, 원 등의 단위가 들어가 있다면 하나씩 지워주면 계산하면 되겠죠. 그러나 바꿔야할게 너무 많거나 꼭 문자를 표시하고 싶다면, Substitute 함수를 써주면 됩니다. 이 함수를 이용하면 셀 속 특정 문자를 변경하거나 없앨 수 있습니다.
Substitute 함수 구조 등 자세히 알아볼게요.
엑셀 substitute 함수란?
Substitute 함수는 셀 속 문자를 원하는 문자로 바꿔주는 함수로, 함수 구조는 SUBSTITUTE(셀 , “바꾸고 싶은 문자”, “변경 문자”)입니다.
- 예를 들어 A열에 있는 100원을 B열에 100달러로 변경하고 싶다면 substitute(A1, “원”, “달러”)를 하면 달러로 변경됩니다.
이 구조를 이용해 달러 부분을 공백으로 둔다면 ‘원’ 글자를 사라지게 만들 수 있습니다. substitute(A1, “원”, “”) 이렇게 말이죠. 그럼 해당 셀에는 문자가 공백처리 되고 숫자만 보이게 됩니다. 이때 숫자가 아니라 글자가 표시가 안된 거라 문자열로 취급됩니다. 그래서 날짜/시간/숫자 문자열을 숫자로 변환하는 함수 Value를 이용해 숫자로 만들어줍니다.
정리하자면 다음과 같습니다.
- 셀 속 문자값을 공백으로 만듭니다. 사람 눈에는 없어진것처럼 보이지만 컴퓨터는 문자로 인식하고 있습니다.
- 숫자로 인식하도록 Value 함수를 사용합니다.
- 원하는 셀을 선택해 합칩니다.
엑셀 서식제외 후 합치기
작성돼 있는 서식을 제외하고 숫자를 합치는 함수는 다음과 같이 사용할 수 있습니다.
- =value(substitute(셀, “없앨 문자”, “”)
그럼 substitute 함수를 여러개 써서 합치려면 어떻게 해야할까요?
- =sum(value(substitute(A1, “원”, “”))+value(substitute(C1, “원”, “”)))
이렇게 작성해주면 원하는 데이터의 합을 구할 수 있습니다.
만약 연속된 셀이라면 substitute(A1:A3,”원”,””) 형식으로 사용가능합니다.