엑셀 최대한 간단하게 ; 중간/마지막 글자 제거(SUBSTITUTE)하고 숫자로 변환(INT) 후 합치기(SUM)

나에게 필요했던 조건

- 최대한 간단하고 나중에 봐도 헷갈리지 않게 작성한다.

- 복붙해서 가져 온 일반 텍스트 데이터인 E열의 가격 'ㅇㅇ,ㅇㅇ원'을 G열의 도매가 셀에 숫자만 추출하고자 한다. 

 

 

업무적으로 마진 계산을 위해 엑셀을 정리하던 중 일반 텍스트에서 숫자 추출 하고자 했다. 처음부터 가격을 손으로 다 입력할 수도 있겠지만 너무 오래 걸려서 비효율적이라고 느꼈다. 주문내역에서 갖다 붙여도 바로 적용 가능하도록 수식을 찾아놓고자 했다.

 

처음에 엑셀 만들었을 때는 G2열에 굳이 합계가 필요하지 않았기 때문에 숫자일 필요도 없었다. 그래서 초반에는

LEFT(E4,FIND("원",E4)-1)
LEFT(E4,LEN(E4)-1)

이런 식으로 만들어 썼었는데 장기적으로 이렇게 데이터를 계속 추가하고 손 보다보니 더 확실한 데이터 변환이 필요했다.

 

 

사실 텍스트 추출에 대해 찾다보면 어려운 함수 정말 많이 나온다. 나는 엑셀은 그냥 기본적인 함수나 작동 원리 정도만 파악하고 있고 그냥 필요할 때 찾아다 적용시키는 편인데 딱! 나에게 필요한 건 잘 보이지 않아서 단계별로 적용할 만한 함수들 먼저 찾아보고 하나하나 추가해서 적용시켰다.

 

[ 사용 ]

SUBSTITUTE

INT

SUBSTITUTE : 텍스트 중의 old_text를 찾아서 new_text로 바꿉니다.
==>> 간단하게 내 방식대로 설명하자면 ctrl+F 창에서의 '바꾸기'와 같은 기능을 하는 함수 같다.

SUBSTITUTE(text, old_text, New_text, Instance_num)
text : 찾기 및 바꾸기의 대상이 되는 텍스트
old_text : 찾아서 new_Text로 바꿀 텍스트. 대/소문자가 정확하게 일치해야만 바꿀 수 있다.
new_text : old_text와 바꾸려는 새 텍스트.
Instance_num : 몇 번째의 old_text를 바꿀 것인지 지정

INT : 정수로 변환

 

 

[ 적용 ] =INT(SUBSTITUTE(SUBSTITUTE(E4,"원",""),",",""))

 

E4   >>   10,880원

1. '원'을 없앤 나머지 텍스트만 추출하기 위해 '원'을 ''(아무것도 없는 텍스트)로 대체한다.

SUBSTITUTE(E4,"원","")

>>   10,880 

 

2. 1번 텍스트(SUBSTITUTE(E4,"원",""))에서 같은 방식으로 ','를 ''(아무것도 없는 텍스트)로 대체한다. SUBSTITUTE(SUBSTITUTE(E4,"원",""),",","")

>>   10880 (일반 텍스트 상태)

 

3. INT로 데이터 자체를 숫자 형태로 변형 시켜준다.

INT(SUBSTITUTE(SUBSTITUTE(E4,"원",""),",",""))

G4    >>   10880

 

4.

=SUM(G4:G105)   >>   734860

예시는 간단하게 SUM으로 사용하긴 했는데, 숫자 형식으로 추출하여 여러 곳에서 가져다 계산하기 좋아졌다.