엑셀에서 텍스트(문자열) 다루기

엑셀에서 다루는 데이터의 형식은 다음 그림과 같이 몇 가지가 있습니다.

숫자, 날짜, 텍스트가 대표적인 데이터 형식인데 이 중에서 '텍스트'는 '문자열'이라고도 부릅니다.

텍스트는 입력된 그대로 사용되기도 하지만 필요에 따라 텍스트를 자르거나 붙이거나 바꾸는 등 다양한 처리를 해야 하는 경우가 있습니다. 이를 위해 엑셀에서는 다양한 텍스트 처리 함수를 제공합니다.  텍스트 처리 함수는 기능이 비슷하지만 용도가 조금씩 다르기 때문에 함수 별 기능의 차이를 정확하게 아는 것이 중요합니다.

이번 글에서는 텍스트 처리의 기본 개념 이해를 위해 간단하게 용도별 텍스트 함수를 소개합니다. 함수별 자세한 사용법은 '함수별 사용법 페이지'를 참고하세요.

 

텍스트 자르기

텍스트를 자를 때는 다음과 같이 LEFT, MID, RIGHT 함수를 주로 사용합니다.

=LEFT(B5,2) => '서울'
[B5] 셀에 입력된 '서울 서대문고/홍익문고'에서 왼쪽부터 2자리를 자릅니다. LEFT 함수는 이름 그대로 왼쪽부터 텍스트를 자를 때 사용합니다.

=MID(B7,4,4) => '서대문구'
[B7] 셀에 입력된 텍스트에서 중간(4번째 자리)부터 4자리를 자릅니다.

=RIGHT(B8,4) => '홍익문고'
[B8] 셀에 입력된 텍스트에서 오른쪽부터 4자리를 자릅니다.

 

특정 문자가 있는 위치를 기준으로 텍스트 자르기

텍스트를 자를 때 앞의 예처럼 자를 자릿수를 지정할 수도 있지만 다음과 같이 특정 문자(예 '/')가 있는 위치를 앞 부분이나 뒷 부분을 잘라낼 수 있습니다.

=LEFT(B15,FIND("/",B15)-1) => '서울 서대문구'
1) 먼저 특정 문자의 위치를 알아야 하므로 FIND 함수를 사용하여 위치를 찾습니다.
2) 구한 위치를 이용하여 LEFT 함수로 '/' 왼쪽의 텍스트를 자릅니다.

 

텍스트 나누기

다음과 같이 A열에 슬래쉬, 쉼표, 공백 등으로 구분된 텍스트가 입력되어 있을 때 여러 열에 나누어서 값을 가져오려면 텍스트 나누기로 해도 되지만 TRANSPOSE, FILTERXML 함수를 사용한 수식으로 간단히 해결할 수 있습니다.

=TRANSPOSE(FILTERXML("<list><item>" &SUBSTITUTE(A1,"/","</item><item>") & "</item></list>","//item"))

자세한 사용법은 수식으로 한 번에 텍스트 나누기 글을 참고하세요.

 

텍스트 합치기

텍스트를 합치는 함수는 여러 가지가 있습니다. 비슷한 것 같지만 용도가 약간씩 다릅니다.

=CONCATENATE(B21,C21) => '홍길동대리'
CONCATENATE 함수는 단순히 [B21], [C21] 셀의 값을 합쳐줍니다.

=CONCAT(B22:C22) => '홍길동대리'
[B22:C22] 셀 범위의 값을 합쳐줍니다. CONCATENATE 함수는 범위를 지정하여 합칠 수 없지만 CONCAT 함수는 범위를 한 번에 지정해서 합칠 수 있습니다.

=B23&C23 => '홍길동대리'
함수를 사용하지 않고 텍스트 연결연산자 '&'를 이용하여 합칠 수도 있습니다. 실무에서는  단순히 셀의 값을 합칠 때는 함수 대신 '&'를 많이 사용합니다.

=TEXTJOIN("/",TRUE,B24:C24) => '홍길동/대리'
엑셀2019, Microsoft 365 이상 버전에서 사용할 수 있는 TEXTJOIN 함수를 사용하면 텍스트 합칠 때 중간에 구분 기호를 넣을 수 있습니다.
이 외에도 TEXTJOIN 함수는 다양하게 응용할 수 있습니다.  예를 들어 고객명이 동일한 조건을 만족하는 주문상품명 텍스트를 합쳐야 할 때 TEXJOIN 함수와 FILTER, UNIQUE 함수(동적 배열 함수)를 쓰면 간단하게 처리할 수 있습니다.
(참고) 엑셀에서 조건을 만족하는 텍스트 합치기(병합)

 

텍스트 바꾸기

텍스트를 바꿀 때는 REPLACE 함수와 SUBSTITUTE 함수를 많이 사용합니다.

=REPLACE(B29,4,2,"가회") => '종로구가회동'
[B29] 셀의 '종로구익선동'에서 4번째 부터 2자리 '익선'을  '가회'로 바꿉니다.

=SUBSTITUTE(B31,"책","옷") => '옷장에 있는 옷'
REPLACE 함수는 위치와 길이를 지정해서 바꾸지만 SUBSTITUTE 함수는 일치하는 값이 있으면 위치에 관계없이 모든 값을 바꿉니다.

=SUBSTITUTE(B32,"-","") => '01024242424'
SUBSTITUTE 함수는 위치에 관계없이 일치하는 모든 값을 바꿔주므로 전화번호에서 하이픈을 한번에 제거할 때 사용할 수 있습니다.

 

TEXT 함수로 숫자, 날짜를 텍스트로 표시

TEXT 함수는 숫자, 날짜를 원하는 형태의 텍스트로 바꾸어서 표시해줍니다.

=TEXT(B37,"#,##0") => '10,000' (천단위 쉼표 표시)

=TEXT(B38,"yyyy-mm-dd hh:mm AM/PM") => '2022-07-01 03:52 PM'

=TEXT(B39,"[DBNum4][$-ko-KR]G/표준") => '삼천오백'

 

기타 텍스트 함수

위에서 설명하지 않았지만 다음과 같은 텍스트 함수들도 자주 사용됩니다.

용도	함수
필요 없는 값 제거	CLEAN - 인쇄할 수 없는 문자 제거하기 TRIM - 공백 제거하기
텍스트 비교, 반복	EXACT - 두 텍스트가 같은지 비교하기(대소문자 구분하여 비교 가능) REPT - 텍스트를 반복해서 표시(셀을 특정 값으로 채우거나 간단한 차트를 만들 수 있음)
대소문자 변경	UPPER - 텍스트를 대문자로 변경 LOWER - 텍스트를 소문자로 변경 PROPER - 텍스트에서 첫 글자를 대문자로 변경