엑셀함수 제8탄 SUBSTITUTE 함수 TEST함수

[글 목차]

엑셀 함수 제8탄 SUBSTITUTE 함수, TEXT 함수

문자열에서 old_text를 new_text로 바꿉니다. 문자열의 특정 텍스트를 바꾸려면 SUBSTITUTE를 사용합니다. 문자열의 특정 위치에 있는 텍스트를 바꾸려면 REPLACE를 사용합니다.

 

◇구문

 

● Instance_num Text에서 몇 번째에 있는 Old_text를 Nex_text로 바꿀 것인지를 지정하는 수입니다. Instance_num을 지정하면 해당하는 위치에 있는 Old_text 만이 바뀝니다. 그렇지 않으면 모든 Old_text가 New_text로 바뀝니다.

◇ 예제

 

◇ 응용
아래 표에서 MSN을 FSC(군급 분류 부호: 앞 4자리)와, NIIN(국가 품목 식별부호 : 뒤 9자리)로 나누어 보시기 바랍니다.

 

 

FSC는 LEFT 함수로 쉽게 추출할 수 있을 겁니다. D53번 수식에서처럼 =LEFT(C53,4) 왼쪽 4글자만 가져오면 됩니다. 결과는 "5036"입니다.

 

MIIN은 앞의 4글자를 뺀 나머지가 되겠지요. 나머지는 오른쪽에서부터 12자리를 가져오면 되므로 RIGHT(C53,12) 이 되고 결과는 "-00-722-0393" 이렇게 됩니다.

 

결과가 비슷하긴 한데 불필요한 문자가 포함되어 있습니다. "-"문자는 불필요하십니까?
"RIGHT 함수" 항의 값들에서 "-"만 빼주면 우리가 원하는 값이 됩니다.
즉, "-"를 공백 " "로 바꾸면 값이 간단히 나오게 될 것입니다.
이것을 함수로 표현하면 =SUBSTITUTE(RIGHT(C53,12), "-", "") 이렇게 됩니다.
Instance_num를 지정하지 않았기 때문에 문자여 내의 모든 "-"를 ""으로 바꿀 것입니다.

◇ 참고
REPLACE : 지정한 자릿수의 문자부터 지정한 자릿수까지 새로운 문자로 바꾸어 줍니다.

 

 

 

TEXT 함수

값을 지정한 표시 형식의 텍스트로 변환합니다.
◇ 구문

● Value 숫자 값, 숫자 값을 계산하는 수식 또는 숫자 값이 포함된 셀의 참조입니다.

● Format_text 셀 서식 대화 상자의 표시 형식 탭의 범주 상자의 텍스트 형식에 있는 숫자 서식입니다.

◇ 주의
● Format_text에 별표는 포함할 수 없습니다.
● 서식 메뉴의 셀 명령에 있는 표시 형식 탭에서 셀 서식을 정하면 값은 변경되지 않고 서식만 변경됩니다. TEXT 함수를 사용하면 값이 서식이 지정된 텍스트로 바뀌고 그 결과는 더 이상 숫자로 계산되지 않습니다.

◇ 표시 형식에 사용하는 문자

 

좀 더 자세한 내용은 Excel 도움말 참조
◇예제

◇응용 1
아래 표에서 MSN이 "-"구분 없이 표기되어 있습니다. MSN의 적당한 위치에 "-"를 넣어 보시면 됩니다.
(예: 5306007220393 -> 5306-00-722-0393)

순번 1의 수식=TEXT(C58, "####-##-###-####")을 살펴보면 C58의 값"5306007220393"을 "4자리-2자리-3자리-4자리"의 형태로 나타내면 되므로 하나의 문자를 나타내는 "#"을 사용하여 필요한 자릿수만큼 "#"을 넣어주고 원하는 위치에 "-"를 추가해주면 원하는 값을 쉽게 얻을 수 있습니다.

◇ 응용 2

아래 품목의 중량을 전체 자리는 5자리로 하되 마지막 1자리는 소수점 2번째 자리에서 반올림한 소수점 1 자리값으로 하고 해당 자릿수에 숫자가 없을 경우는 "0"으로 표현해 보시기 바랍니다.
(예: 25.36 ->00254)

 

순번 1의 수식 =SUBSTITUTE (TEXT(C76, "0000.0"), ".", "",1)에서 먼저 TEXT함수를 먼저 살펴보겠습니다.
TEXT(C76, "0000.0")는 C76의 값은 "8379.49"이고 이 값을 "0000.0"의 형태로 변환합니다. "0000.0"으로 Format을 정해줌으로써 중량 값이 자동으로 수수점 1자리로 반올림되고 자릿수에 값이 없을 때는 "0"값으로 채워주게 됩니다. 아래의 "Text함수" 항을 참조하시기 바랍니다.

 
TEXT 함수만 적용했더니 문제가 한 가지 보이는 알 수 있습니다. 소수점 자리를 표현하는 ". "이 없어져야 원하는 값을 얻을 수 있습니다.

따라서 SUBSTITUTE함수로 첫 번째 나오는 ". "을 " "(공백)으로 바꾸어 주었습니다.
이것을 수식으로 표현하면
=SUBSTITUTE(TEXT(C76, "0000.0"), ". " , " ", 1)이 되는 것입니다.
Instance_num인 "1"은 문자열 내에 ". "이 하나밖에 없으므로 생략해도 상관없습니다.