엑셀 텍스트로 되어 있는 숫자를 계산할 수 있는 숫자로 바꾸기(VALUE, NUMBERVALUE)

엑셀 데이터가 텍스트 형식으로 되어 있는 경우 계산을 해야 하는데 안되는 경우가 있을 수 있습니다.

셀 서식을 변경 할 수도 있고 수식에 텍스트를 넣으면 엑셀에서는 자동으로 숫자로 인식하고 계산을 합니다.

하지만 소수점이나 공백등의 처리가 잘 되지 않아 자동으로 변환이 되지 않을 수 있습니다.

변환이 잘 되지 않는 숫자가 셀에 텍스트로 입력 되어 있는 경우 명시적인 함수를 사용하여 엑셀에서 숫자로 변환시켜줄 수 있습니다.

또한 미국의 영향을 받는 나라에서는 숫자 단위를 표시할 때 ",", 소수를 표시할 때는 "."으로 표시를 하지만 다른 나라에서는 반대로 하는 경우가 있습니다.

즉 우리나라에서 1,300.05라고 표현하는 숫자를 1.300,05라고 표현하는 국가도 있다는 뜻입니다.

그런 경우 일일히 변경해 줄 필요 없이 고정해서 한 가지 표현법으로 표시할 수도 있고 엑셀을 보는 컴퓨터의 설정에 따르도록 할 수도 있습니다.

 

 

---

1. 엑셀에서 텍스트를 숫자로 바꾸는 함수

1-1. VALUE 함수

VALUE(text)

text: 필수요소로 숫자로 변환을 하고자 하는 텍스트입니다.

문자열을 숫자형식으로 바꾸어 주는 기본 함수입니다.

대부분의 경우 VALUE 함수로도 충분히 원하는 결과를 얻을 수 있습니다.

하지만 몇몇 경우에는 NUMBERVALUE 함수가 필요합니다.

 

1-2. NUMBERVALUE 함수(기본)

NUMBERVALUE(text)

text: 필수요소로 숫자로 변환을 하고자 하는 텍스트입니다.

NUMBERVALUE 함수에서 필수 인자만 사용하는 경우 대부분의 상황에서 VALUE 함수와 동일하게 동작하지만 텍스트에 공백이나 "%%"이 있는 경우에는 NUMBERVALUE을 사용해야 합니다.

 

1-3. NUMBERVALUE 함수(활용)

NUMBERVALUE(text, [decimal_separator], [group_separator])

text: 필수요소로 숫자로 변환을 하고자 하는 텍스트입니다.
decimal_separator: 선택요소로 정수와 소수를 구분하는 문자입니다. 지정하지 않으면 엑셀 파일을 보고 있는 PC의 세팅값이 사용됩니다.
group_separator: 자릿수를 나타내는 문자입니다. 지정하지 않으면 엑셀 파일을 보고 있는 PC의 세팅값이 사용됩니다.

NUMBERVALUE함수를 사용 할 때 정수 부분의 자릿수와 소수점을 나타내는 기호를 지정할 수 있습니다.

국내 데이터만 사용한다면 필요없겠지만 일부 외국 데이터를 사용한다면 decimal_separator를 사용하여 변환하여 줄 필요가 있습니다.

 

 

---

2. 텍스트를 숫자로 바꾸는 함수의 사용 예제

엑셀에서 숫자가 입력된 텍스트를 함수를 이용하여 숫자로 바꾼 예시

 

예시는 기본적으로 숫자로 이루어진 텍스트 형식을 변환하는 방법입니다.

 

1. 앞에 0이 붙어있는 텍스트의 변환입니다.

    가장 앞에 있는 0이 없어지고 숫자만 남았습니다. 3가지 경우 똑같은 결과입니다.

2. 일반적인 숫자형태의 텍스트입니다.

    당연히 3가지 경우 텍스트와 같은 숫자로 변환됩니다.

3. 숫자를 콤마로 그룹지어주는 텍스트입니다.

    NUMBERVALUE에 separator를 넣은 경우만 이상하게 나오는 것을 볼 수 있습니다.

    "1,300"에서 ","이 소수점을 나타내는 문자라고 인수를 넣어주었기 때문에

    "1,300"을  "1.300"으로 인식하고 소수점 뒤의 0은 생략되어 표와 같이 보입니다.

4. 숫자사이에 공백이 있는 텍스트입니다. VALUE는 제대로 인식하지 못하고 오류가 나는 모습입니다.

5. 숫자와 한글이 함께 있는 경우 모든 함수가 인식하지 못합니다.

6. 공백인 경우 모든 함수가 0으로 표시합니다.

7. 50%인 경우 모두 0.5로 표시해 줍니다.

8. 50%%라면 만분율로 인식하여 "0.005"로 표시합니다.

    하지만 VALUE는 만분율을 처리하지 못합니다.

9. 우리나라에서는 잘 쓰지않는 방식의 숫자 표현이 나왔습니다.

    NUMBERVALUE에서 ","과 "."을 변환해준 경우만 정상적으로 표시되는 것을 볼 수 있습니다.

10. 9번과 반대인 경우입니다.

      오히려 NUMBERVALUE에 인자를 넘겨준 세번째 경우만 제대로 출력되지 않습니다.

11. 소수점 표시를 잘못쓴 경우는 어떤 경우에도 제대로 표시해 주지 못합니다.

12. 원표시가 있는 경우 3가지 함수 모두 잘 변환시켜 줍니다.

13. 달러 표시도 마찬가지로 잘 변환시켜 줍니다.