문자와 숫자가 동시에 있는 문자열에서 숫자만 가져오기

가계부를 쓸 때 금액을 작성하면서 숫자 뒤에 단위를 함께 쓰는 경우가 있습니다.  이런 경우 엑셀에서는 문자로 인식을 하기 때문에 사칙연산을 할 수 없게 됩니다. 이렇게 숫자와 문자를 함께 써 작성한 문자열에서 숫자만 추출하여 계산 가능하도록 하는 방법에 대해 알아보겠습니다.

 

1. 숫자가 앞에 나오고 문자가 뒤에 위치해 있을 때

 금액을 작성할 때 한국에서는 흔히 숫자 뒤에 '원'을 붙입니다. '1000원'과 같이 사용합니다.

하지만 엑셀에서 셀에 '1000원'이라고 입력하면 사람은 이를 문자가 아닌 숫자로 생각합니다. 그러나, 엑셀은 '원'이라는 문자가 함께 쓰였기 때문에 이를 문자열로 인식합니다.

엑셀에서 문자열은 계산을 할 수가 없습니다. 엑셀에서 이 문자열을 계산할 수 있도록 하기 위해 입력된 문자열에서 숫자만을 추출하여 계산하도록 하는 것입니다. 이에 사용되는 함수는 RIGHT 함수와 LEFT 함수가 쓰입니다. 

 

 

 

 

RIGHT 함수와 LEFT 함수는 다음과 같이 사용합니다.

 

  RIGHT(문자열, 문자수)

 

  LEFT(문자열, 문자수)

 

RIGHT 함수는 문자열 마지막에서부터 문자수만큼 추출하라는 함수입니다.

LEFT 함수는 문자열 처음에서부터 문자수만큼 추출하라는 함수입니다.

 

예제 1) '1000원'이라는 문자열에서 RIGHT 함수와 LEFT 함수를 사용하면서 차이점을 알아보겠습니다.

  

RIGHT("1000원", 1)  => 결과 : 원 

LEFT("1000원", 1)  => 결과 : 1

 

RIGHT 함수는 '1000원'에서 가장 마지막에 있는 문자 '원'부터 한 문자를 추출하므로 '원'을 결괏값으로 출력합니다.

LEFT 함수는 '1000원'에서 가장 처음에 있는 문자 '1'부터 한 문자를 추출하므로 '1'을 결괏값으로 출력합니다.

 

RIGHT("1000원", 2)  => 결과 : 0원

LEFT("1000원", 2)  => 결과 : 10

 

RIGHT 함수는 '1000원'에서 가장 마지막에 있는 문자 '원'부터 두 개 문자를 추출하므로 '0원'을 결괏값으로 출력합니다.

LEFT 함수는 '1000원'에서 가장 처음에 있는 문자 '1'부터 두 개 문자를 추출하므로 '10'을 결과값으로 출력합니다.

 

RIGHT 함수는 지정한 문자열에서 가장 오른쪽에 있는 문자(마지막 문자)에서부터 주어진 숫자만큼의 문자를 왼쪽으로 이동하면서 추출을 하여 결과값으로 출력하는 함수이며, LEFT 함수는 지정한 문자열에서 가장 왼쪽에 있는 문자(첫 번째 문자)에서부터 주어진 숫자만큼의 문자를 오른쪽으로 이동하면서 추출을 하여 결괏값으로 출력하는 함수입니다.

 

[그림 1] 지출내역

[그림 1]과 같이 지출내역을 작성한 시트가 있습니다. 그런데 금액을 입력할 때 '원'이라는 문자와 함께 입력하여 지출 합계를 구하려면 숫자만으로 만들어야 합니다.

여기서 한 가지 함수를 더 알아보겠습니다. LEN 함수입니다.

LEN 함수는 지정한 문자열의 문자수를 알려주는 함수입니다.

 

지출내역을 보시면 지출금액의 단위가 달라서 자릿수가 틀린 것을 알 수 있습니다. 그래서 LEN 함수를 이용하여 각 지출금액의 문자수를 확인하겠습니다.

 D2 셀에 '=LEN(C2)'를 입력하고 엔터를 치시면 '10000원'의 문자 개수인 '6'을 결괏값으로 출력합니다. 그리고 D6 셀까지 수식 채우기를 하여 각 문자수를 계산합니다.

 

이제 E열에 LEFT 함수를 사용하여 지출금액의 숫자 부분만 추출합니다.

E2 셀에 '=LEFT(C2, D2-1)'이라고 입력하고 엔터를 누릅니다. 그러면 '10000'이라는 결과가 출력됩니다.

이는 '10000원'이라는 문자열에서 6에서 1을 뺀 5만큼의 문자를 맨 앞 문자인 '1'부터 5개를 추출하라는 의미입니다.

이제 E6 셀까지 수식 채우기로 숫자만 추출합니다.

 

[그림 2] 문자열에서 LEFT 함수로 숫자만 추출한 결과

[그림 2]와 같은 결과를 얻을 수 있습니다.

 

 

 

2. 문자가 앞에 나오고 숫자가 뒤에 나올 때

 

[그림 3] 문자가 앞에 있는 지출내역

[그림 3]은 금액을 입력할 때 숫자 앞에 '일금'이라고 입력하였습니다. 이번에는 숫자만을 추출하기 위해 함수 RIGHT를 이용하겠습니다.

 

D2 셀에 '=LEN(C2)'를 입력하고 엔터를 치면 '일금10000'의 문자 개수인 '7'이 출력됩니다. D6셀까지 수식 채우기를 하여 문자수를 구합니다.

 

E2 셀에 '=RIGHT(C2, D2-2)'를 입력하고 엔터를 치면 숫자인 '10000'만 추출되어 출력됩니다.

입력한 함수 식은 '일금 10000' 문자열에서 전체 문자 개수인 7에서 '일금'  두 개의 문자를 제외하여야 하기 때문에 '2'를 뺀 5개의 문자를 문자열의 마지막 문자인 '0'에서부터 추출한 것입니다.

 

3. 문자를 상수로 바꿔주기

위에서 LEFT 함수와 RIGHT 함수로 숫자만을 추출하였습니다. 하지만 추출한 숫자 역시 엑셀은 문자로 인식하고 있습니다. 그래서 추출한 숫자로 합계를 구할 수가 없습니다. 

 

이제 문자를 숫자로 바꿔주기 위해 VALUE 함수를 사용합니다.

[그림 2]에서 F2 셀에 '=VALUE(E2)'라고 입력하고 엔터를 누릅니다. 그러면 E2 셀과 동일한 '10000'이 출력됩니다. 하지만 E2 셀의 '10000'과 F2 셀의 '10000'은 엑셀에서는 다르게 인식합니다. 

E2 셀의 '10000'은 문자로, F2 셀의 '10000'은 숫자로 인식합니다.

이와같이 문자를 숫자로 바꿀 때는 VALUE 함수를 사용합니다.

 

이제 F6 셀까지 수식 채우기를 하여 E열의 문자를 숫자로 바꾸고 F열의 숫자 합계를 구하면 '41000'이라는 결괏값을 얻을 수 있습니다.

 

 

 

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.