엑셀함수, 글자수 LEN, 공백제거 TRIM, 문자바꾸기 SUBSTITUTE

안녕하세요. 햇살조림입니다. 엑셀함수를 검색해서 들어오신 여러분 환영합니다.

오늘 이야기할 엑셀함수는 공백제거와 관련된 함수들입니다. 제 경우에는 엑셀을 사용할 때 띄어쓰기 같은 공백에 대해 수정한 경우는 많지 않습니다. 대신 다른 부서에서 데이터를 넘겨받았을때 띄어쓰기나 유령문자처럼 이상한 파일이 올때가 있습니다. 파일을 넘겨준 사람이 직장 상사라면 아무소리 못하고 데이터를 수정해야 하는데요. 지금부터 공백제거와 관련된 TRIM함수와 SUBSTITUTE함수에 대해 살펴봅니다.

 

=TRIM(text) / =LEN(text)

병원진료과를 예로 들어봤습니다. 간호과, 피부과 등 여러 과를 나열했습니다. 지금 위 그림에서는 각 셀들을 가운데 정렬을 해놨는데요. 간호과와 피부과를 보시면 뭔다 다른점을 발견하게 됩니다. 바로 유령문자인데요. LEN함수를 사용해서 글자수를 확인해보니 간호과는 5자, 피부과는 8자로 표시됩니다. 피부과에 유령문자가 포함되어 있다는 말인데요. 이때 TRIM함수를 사용하여 유령문자를 없앨 수 있습니다.

TRIM의 단어 뜻은 '끝부분을 잘라내다', '불필요한 부분을 잘라내다'인데요. 엑셀함수로서는 '텍스트의 양 끝 공백을 없애는 기능'을 합니다. 여기서 텍스트는 공백없이 이어진 문장을 말하는데요. A7의 '산부  인  과'처럼 '산부'/'인'/'과'를 별개의 텍스트로 인식을 하게 됩니다. 조금 더 쉽게 설명하자면, TRIM함수는 텍스트 내의 공백을 한칸으로 줄이는 기능을 한다고 설명할 수 있겠죠.

지금 생각해보니, 여러명이 작성한 엑셀데이터를 하나로 합칠때 TRIM함수를 사용한 적이 있는데요. 개인적으로는 그렇게 사용빈도가 많진 않습니다.

 

=SUBSTITUTE(text,Old_text,New_text,[Instance_num])

SUBSTITUTE함수는 공백을 제거할때 사용하긴 하지만, 정확히 말해서 특정문자를 바꿔주는 기능을 합니다. '대체자', '교체선수'라는 뜻을 가진만큼 엑셀기능인 '바꾸기'와 동일한 역할을 하죠. 공백제거를 위해 SUBSTITUTE함수를 사용한다면 =SUBSTITUTE(A2," ","")처럼 사용하면 되는데요. 풀어서 설명하자면 셀A2의 값에서 공백(" ")을 제거("")하라는 뜻입니다. C2부터 C6까지 모든 데이터가 공백이 없어졌죠.

반대로 공백(" ")을 두배로 늘리면("  ") 어떻게 될까요? 공백이 늘어나게 됩니다. 바꾸기 기능이니까 당연한 말이겠죠.

 

SUBSTITUTE함수가 정확히 '바꾸기'기능을 한다고 했는데요. 공백뿐만 아니라 문자와 숫자 모두 바꿀 수 있습니다. 문자는 따옴표를 붙이고 숫자는 그냥 입력하면 되죠. 그래서 공백도 제거하고 문자/숫자도 바꾸는 만능역할을 하는 함수라 하겠습니다.

만약 동일한 내용이 중복된 데이터에서 특정한 값만 바꾸고 싶다면 어떻게 해야 할까요? 예를 들어 '101 101 101'이란 데이터를 '101 105 101'로 바꾸고 싶다면...? [Instance_num]을 넣으시면 됩니다. =SUBSTITUTE(A14,101,105,2)라고 입력하면 셀A14의 값에서 두번째 101값을 105로 바꾸게 됩니다.

SUBSTITUTE함수보다는 '컨트롤+F'를 눌러 '바꾸기'기능을 많이 사용하는데요. SUBSTITUTE함수에는 [Instance_num]을 지정할 수 있다는 사실만 기억하면 될 것 같습니다.