실무에서 받는 데이터는 대부분 정리되지 않은 상태로 제공됩니다. 공백이 불규칙하게 들어가 있거나, 대소문자가 섞여 있고, 필요한 정보가 하나의 셀에 뭉쳐있는 경우가 많습니다. 이런 지저분한 데이터를 수작업으로 정리하는 것은 엄청난 시간 낭비입니다. 엑셀의 텍스트 함수를 활용하면 이러한 작업을 자동화할 수 있습니다. LEFT, RIGHT, MID 함수를 사용한 문자열 추출 기법부터 TRIM, SUBSTITUTE로 불필요한 공백과 문자를 제거하는 방법, 그리고 TEXTJOIN과 CONCAT으로 여러 셀의 데이터를 효율적으로 결합하는 실전 노하우까지 상세하게 다룹니다. 반복적인 데이터 정리 작업에서 벗어나 생산적인 업무에 집중할 수 있도록 도와드립니다.
LEFT, RIGHT, MID 함수를 활용한 문자열 추출 기법
텍스트 데이터에서 원하는 부분만 정확하게 추출하는 것은 데이터 정리의 첫 단계입니다. LEFT 함수는 문자열의 왼쪽에서부터 지정한 개수만큼 문자를 가져옵니다. 기본 문법은 LEFT(텍스트, 추출할문자수)입니다. 예를 들어 주민등록번호에서 생년월일만 추출하려면 LEFT(A1, 6)처럼 작성하여 앞 6자리를 가져올 수 있습니다. 제품 코드가 "PRD-2024-001" 형태일 때 앞의 "PRD"만 추출하려면 LEFT(A1, 3)을 사용합니다. RIGHT 함수는 반대로 문자열의 오른쪽에서부터 문자를 추출합니다. 파일명에서 확장자만 가져오거나, 계좌번호의 마지막 4자리만 표시할 때 유용합니다. RIGHT(A1, 4)는 셀 A1의 오른쪽 끝에서부터 4개 문자를 반환합니다. 만약 확장자를 추출할 때 확장자 길이가 다르다면 FIND나 SEARCH 함수와 결합해야 합니다. MID 함수는 가장 유연한 함수로, 문자열의 중간 부분을 추출할 수 있습니다. 문법은 MID(텍스트, 시작위치, 추출할문자수)입니다. 주민등록번호 "901225-1234567"에서 월과 일만 추출하려면 MID(A1, 3, 4)로 3번째 위치부터 4개 문자를 가져옵니다. 이메일 주소에서 도메인 이전의 사용자명만 추출하려면 MID와 FIND 함수를 조합합니다. MID(A1, 1, FIND("@", A1)-1)은 @ 기호 위치를 찾아 그 앞까지만 추출합니다. 전화번호 "010-1234-5678"을 지역번호, 국번, 번호로 분리할 때도 MID 함수가 핵심입니다. 첫 번째 하이픈과 두 번째 하이픈의 위치를 FIND 함수로 찾은 후 MID로 각 부분을 추출할 수 있습니다. 실무에서는 이러한 함수들을 중첩하여 사용하는 경우가 많습니다. 예를 들어 "2024년 10월 15일" 형태의 날짜에서 연도만 추출하려면 LEFT(A1, 4)로 간단히 해결되지만, "2024.10.15" 형태라면 LEFT(A1, FIND(".", A1)-1)처럼 동적으로 처리해야 합니다. LEN 함수와 결합하면 더욱 강력해지는데, 전체 문자 길이에서 특정 부분을 제외한 나머지를 추출할 때 RIGHT(A1, LEN(A1)-FIND("-", A1))처럼 계산식을 활용합니다.
TRIM, SUBSTITUTE로 불필요한 공백과 문자 제거하기
외부에서 받은 데이터는 종종 불필요한 공백이나 특수문자가 포함되어 있어 정확한 분석을 방해합니다. TRIM 함수는 텍스트 앞뒤의 공백을 제거하고, 단어 사이의 연속된 공백을 하나의 공백으로 정리합니다. 기본 사용법은 TRIM(텍스트)로 매우 간단합니다. 예를 들어 " 홍길동 "처럼 이름 앞뒤에 공백이 있다면 TRIM(A1)을 사용하여 "홍길동"으로 깔끔하게 만듭니다. 엑셀 파일을 다른 시스템에서 가져올 때 자주 발생하는 문제인데, 겉으로는 똑같아 보이지만 공백 때문에 VLOOKUP이나 일치 검색이 작동하지 않는 경우가 있습니다. 이때 TRIM을 적용하면 대부분 해결됩니다. SUBSTITUTE 함수는 특정 문자나 문자열을 다른 것으로 바꿔줍니다. 문법은 SUBSTITUTE(텍스트, 찾을문자, 바꿀문자, [인스턴스번호])입니다. 전화번호에서 하이픈을 제거하려면 SUBSTITUTE(A1, "-", "")처럼 빈 문자열로 치환합니다. 여러 문자를 동시에 제거해야 한다면 SUBSTITUTE를 중첩 사용합니다. SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "-", ""), "(", ""), ")", "")는 하이픈과 괄호를 모두 제거합니다. 줄바꿈 문자를 제거할 때는 SUBSTITUTE(A1, CHAR(10), "")을 사용하는데, CHAR(10)은 줄바꿈을 의미하는 특수 문자입니다. 가끔 데이터에 보이지 않는 특수문자가 포함되어 있어 문제를 일으키는데, CLEAN 함수를 사용하면 출력할 수 없는 문자들을 자동으로 제거합니다. TRIM과 CLEAN을 함께 사용하는 공식인 TRIM(CLEAN(A1))은 데이터 정리의 기본 조합입니다. UPPER, LOWER, PROPER 함수는 대소문자를 통일할 때 사용합니다. UPPER는 모든 문자를 대문자로, LOWER는 소문자로, PROPER는 각 단어의 첫 글자만 대문자로 변환합니다. 영문 이름 "john SMITH"를 "John Smith"로 정리하려면 PROPER(A1)을 사용합니다. 실무에서는 이러한 함수들을 조합하여 한 번에 여러 정리 작업을 수행합니다. TRIM(SUBSTITUTE(UPPER(A1), " ", " "))처럼 대문자 변환, 이중 공백 제거, 앞뒤 공백 제거를 동시에 처리할 수 있습니다.
TEXTJOIN과 CONCAT으로 데이터 효율적으로 결합하는 방법
여러 셀에 흩어진 정보를 하나로 합치는 작업은 보고서 작성이나 데이터 통합에서 자주 발생합니다. 과거에는 앰퍼샌드(&) 연산자를 사용했지만, 최신 엑셀에서는 TEXTJOIN과 CONCAT 함수가 훨씬 효율적입니다. TEXTJOIN 함수는 구분 기호를 지정하여 여러 값을 연결할 수 있는 강력한 도구입니다. 기본 문법은 TEXTJOIN(구분기호, 빈셀무시여부, 텍스트1, [텍스트2], ...)입니다. 예를 들어 성, 이름, 직급이 각각 다른 셀에 있을 때 TEXTJOIN(" ", TRUE, A1, B1, C1)로 "김철수 대리"처럼 공백으로 구분하여 결합합니다. 두 번째 인수인 TRUE는 빈 셀을 무시하라는 의미로, 일부 셀이 비어있어도 오류 없이 작동합니다. 주소를 결합할 때는 TEXTJOIN(", ", TRUE, A1:E1)처럼 범위를 지정할 수 있어 편리합니다. 구분 기호로 쉼표와 공백을 함께 사용하면 "서울시, 강남구, 테헤란로, 123" 형태로 보기 좋게 만들어집니다. CONCAT 함수는 TEXTJOIN보다 단순하지만 빠르게 문자를 연결할 때 유용합니다. CONCAT(A1:C1)은 세 셀의 내용을 구분 기호 없이 바로 붙입니다. 제품 코드를 생성할 때처럼 "CAT" & "2024" & "001"을 하나로 만들어 "CAT2024001"로 표시하는 상황에서 활용됩니다. 조건부로 텍스트를 결합해야 할 때는 IF 함수와 조합합니다. TEXTJOIN(" ", TRUE, A1, IF(B1<>"", B1, ""), C1)은 B1 셀이 비어있지 않을 때만 포함시킵니다. 날짜와 시간을 텍스트로 결합할 때는 TEXT 함수를 함께 사용해야 합니다. TEXTJOIN(" ", TRUE, TEXT(A1, "yyyy-mm-dd"), B1)은 날짜를 원하는 형식으로 변환한 후 결합합니다. 대량의 데이터를 처리할 때는 배열 수식을 활용합니다. TEXTJOIN(", ", TRUE, IF(B1:B100>1000, A1:A100, ""))는 B열 값이 1000을 초과하는 행의 A열 값만 모아서 쉼표로 구분하여 표시합니다. 이는 조건에 맞는 항목의 목록을 빠르게 만들 때 매우 유용합니다. 엑셀 텍스트 함수로 데이터 정리 자동화하기의 핵심은 이러한 함수들을 상황에 맞게 조합하는 것입니다. 예를 들어 정리되지 않은 고객 정보를 표준 형식으로 변환하려면 TEXTJOIN(", ", TRUE, PROPER(TRIM(A1)), SUBSTITUTE(B1, "-", ""), UPPER(C1))처럼 여러 함수를 중첩하여 한 번에 처리할 수 있습니다. 이렇게 작성한 수식을 전체 데이터에 복사하면 수천 건의 데이터도 몇 초 만에 정리됩니다.