엑셀 SUBSTITUTE는 셀에 입력된 텍스트 내에서 특정 부분을 찾아 다른 문자로 변경하는데 사용하는 함수다. 이 함수 하나로 응용 범위는 상당히 넓은데 예전에 잘못된 형식으로 입력된 날짜를 SUBSTITUTE 함수를 사용해서 올바른 형식의 날짜로 바꾼 예제도 다룬 적이 있다.
또는 셀의 값에 단위를 직접 적어놓은 경우에는 셀 값이 숫자가 아닌 텍스트로 인식되므로 숫자 연산이 불가능 하다. 이럴 때 역시 SUBSTITUTE 함수를 사용해서 해결할 수 있다.
그럼 구체적으로 하나씩 살펴보도록 해보자.
잘못된 날짜 형식을 바로 잡는 예
엑셀에서는 셀 값에 통화, 날짜, 시간 등에 다양한 서식을 적용할 수 있다. 하지만 위와 같이 2018.01.15는 엑셀이 인식할 수 있는 날짜 형식이 아니다. 왜 그런지에 대해 궁금하다면 잘못 입력된 날짜 형식에서 아주 구체적인 원리를 설명한다.
위 링크와 중복되는 내용이므로 요약해서 간단히 설명하겠다.
우선 날짜 형식에서 년, 월, 일을 .(점)으로 구분하는 형식은 엑셀이 인식하지 못한다. 엑셀에서 분간할 수 있는 형식은 (CTRL+1)에 들어가서 표시형식의 날짜를 클릭해보면 알 수 있다.
일반적으로 -나 /로 년, 월, 일을 구분하면 되므로 .을 - 또는 /로 변경해주면 되겠다.
SUBSTITUTE(Text, Old_text, New_text, Instance_num)
Text 내에 있는 Old_text를 New_text로 바꾸라는 뜻
Instance_num은 Old_text가 여러 번 중첩될 때 몇 번째 Old_text를 바꿀지를 설정할 때 쓴다. 생략되면 모두 바꾼다.
이렇게 서식을 제대로 바꿔놓으면 엑셀에서 정확히 날짜로 인식한다. 더 구체적인 내용은 앞선 링크에 다 설명되어 있다.
단위 제거해서 계산하기
보통 수량에 대한 단위를 붙이는 경우 셀 서식에서 해결하면 계산하는데 큰 문제는 없다. 그런데 사용자 지정 서식으로 단위를 붙이는 경우 계산 결과에 단위가 따라온다. 그리고 애초에 단위를 붙일 때 값에 직접 텍스트로 붙이는 경우는 숫자 연산이 안된다. 이 두 가지 경우를 해결해 보도록 하자.
① 사용자 지정서식으로 단위를 붙이는 경우
위와 같이 사용자 지정 서식을 이용해서 수량에 "개"라는 단위를 붙였다. 이 값을 사용해서 연산을 해보도록 하자.
수량과 가격을 곱했는데 결과에 수량 단위인 "개"가 붙어 나왔다. 이 문제는 사용자 지정 서식이 결과 셀에 적용되서 생기는 문제므로 단순히 결과 셀의 셀 서식을 기본 값으로 변경하면 해결 된다.
② 직접 단위를 붙이는 방식
이런 방식으로 만들어진 데이터가 적은 경우라면 직접 단위를 붙이는 데이터 입력 방식을 수정할 필요가 있다. 하지만 이미 만들어진 방대한 양의 자료에 이 방식이 모두 적용되어 있다면 문제가 심각하다.
해결방법으로 SUBSTITUTE 함수를 사용해서 새롭게 테이블을 구성하거나 계산하면 되겠다.
=SUBSTITUTE(C3, "개", "")
셀 C3 에 있는 텍스트에서 "개"라는 문자를 찾아 "" 빈문자로 만들라는 의미다.
그리고 가격에 적용된 셀 서식이 결과값에도 적용되었는데 ①번 경우와 마찬가지로 셀 서식을 일반으로 변경해주면 해결된다.