[Skill/Excel] 엑셀 텍스트 함수, 한 셀에 뭉친 이름·코드·날짜를 3분 만에 분리하는 법

 

 

회사 시스템에서 내려받은 데이터를 열면 이런 상황이 있습니다.

"김민준/마케팅팀/SEL001"처럼 이름, 부서명, 코드가 A열 하나에 몰려 있습니다.

이걸 각각 다른 열로 분리해야 하는데, 행이 수백 개입니다.

 

처음에는 하나씩 복사해서 붙여넣거나, 텍스트 나누기 기능을 씁니다. 그래도 예외 케이스가 생기면 결국 손으로 다시 고칩니다. 이 작업이 매달 반복되면 데이터 정리만으로 30분이 넘어가기도 합니다.

이 문제는 엑셀 텍스트 함수 4가지를 알면 수식 몇 줄로 끝낼 수 있습니다.

1. 왜 한 셀에 뭉친 데이터가 계속 생기는가

ERP, CRM, 인사 시스템에서 내보내는 데이터는 여러 항목이 한 셀에 합쳐진 형태로 나오는 경우가 많습니다.

담당자명과 사번이 붙어 있거나, 주소에서 시도와 구군이 합쳐져 있거나, 날짜와 시간이 한 셀에 들어오는 경우가 대표적입니다.

시스템 설계 단계에서 분리되어 나오면 좋지만, 실무에서는 그렇지 않은 경우가 더 많습니다. 그래서 데이터를 받는 담당자가 분리 작업을 매번 떠맡게 됩니다.

 

손으로 반복하면 시간이 오래 걸리고, 행이 많을수록 실수가 섞입니다. 텍스트 함수를 활용하면 같은 작업을 수식 하나로 처리하고, 데이터가 늘어도 수식을 아래로 내려 채우면 됩니다.

2. 실무에서 가장 많이 쓰는 텍스트 함수 4가지

LEFT / RIGHT / MID — 위치로 텍스트 추출하기

LEFT(텍스트, 글자 수)는 왼쪽부터, RIGHT(텍스트, 글자 수)는 오른쪽부터 지정한 수만큼 텍스트를 뽑아냅니다.

MID(텍스트, 시작 위치, 글자 수)는 중간 어느 위치에서든 원하는 만큼 가져올 수 있습니다.

 

예를 들어 A1에 "SEL001-마케팅팀"이 있을 때

• 코드만 뽑으려면: =LEFT(A1, 6) → SEL001
• 팀명만 뽑으려면: =MID(A1, 8, 4) → 마케팅팀

단, 항목의 길이가 일정하지 않으면 위치를 직접 숫자로 넣기 어렵습니다. 이럴 때는 FIND와 함께 씁니다.

 

FIND — 구분자의 위치 찾기

FIND(찾을 텍스트, 대상 텍스트)는 특정 문자(구분자)가 몇 번째 위치에 있는지 숫자로 알려줍니다. 구분자가 슬래시(/), 하이픈(-), 공백 등으로 일정하게 들어올 때 LEFT, MID와 조합하면 길이가 달라도 자동으로 분리됩니다.

예를 들어 A1에 "김민준/마케팅팀/SEL001"이 있을 때

• 이름만: =LEFT(A1, FIND("/", A1)-1) → 김민준
• 팀명만: =MID(A1, FIND("/",A1)+1, FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1) → 마케팅팀
• 코드만: =MID(A1, FIND("/",A1,FIND("/",A1)+1)+1, 99) → SEL001

수식이 길어 보이지만, 구분자 위치를 기준으로 잘라내는 패턴을 한 번 익히면 응용이 쉽습니다.

 

TRIM — 보이지 않는 공백 제거하기

TRIM(텍스트)은 앞뒤 공백과 단어 사이 반복 공백을 제거합니다. 시스템에서 내려받은 데이터에는 눈에 보이지 않는 공백이 섞여 있는 경우가 있습니다. 이 상태에서 VLOOKUP이나 MATCH를 쓰면 값이 분명히 있는데도 오류가 납니다.

데이터를 받은 뒤 TRIM으로 한 번 정리해두면 이후 함수 오류를 상당히 줄일 수 있습니다.

 

SUBSTITUTE — 특정 문자 바꾸거나 제거하기

SUBSTITUTE(텍스트, 찾을 텍스트, 바꿀 텍스트)는 문자열 안의 특정 문자를 다른 문자로 바꿉니다. 바꿀 텍스트를 ""(빈 문자열)로 넣으면 특정 문자를 제거하는 용도로 씁니다.

전화번호에서 하이픈을 없애거나, 금액 앞에 붙은 "원" 자를 제거할 때 씁니다.

3. 실무 적용 사례: 한 셀 데이터 분리 Before / After

Before (원본 데이터)

A열에 이런 형태로 들어옵니다.

김민준/마케팅팀/SEL001
이수진/영업팀/BUS002
박재현/기획팀/PLN003

 

After (수식으로 분리)

• B열 (이름): =LEFT(A2, FIND("/",A2)-1)
• C열 (팀): =MID(A2, FIND("/",A2)+1, FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1)
• D열 (코드): =MID(A2, FIND("/",A2,FIND("/",A2)+1)+1, 99)

4. 텍스트 함수 적용 전에 확인해야 할 것

텍스트 함수를 쓰기 전에 아래를 먼저 확인하면 오류를 줄일 수 있습니다.

• 구분자가 일정한가 (슬래시, 하이픈, 공백 등)
• 구분자 없이 길이로만 구분하는 경우인가
• 앞뒤에 불필요한 공백이 섞여 있는가
• 구분자가 없는 예외 행이 있는가

구분자가 있으면 FIND + LEFT/MID 조합이 가장 깔끔합니다. 길이가 고정되어 있으면 LEFT/RIGHT/MID 단독으로 씁니다. 예외 행이 있으면 IFERROR로 오류 처리를 함께 넣는 것이 좋습니다.

5. 마무리 체크리스트

아래 항목에 해당된다면 텍스트 함수를 먼저 적용해볼 만합니다.

 

이름, 코드, 날짜가 한 셀에 합쳐진 열이 있다

분리 작업을 손으로 하고 있다

같은 형태의 데이터가 매번 반복해서 들어온다

VLOOKUP/XLOOKUP에서 이유 없는 오류가 난다

공백, 특수문자가 섞인 데이터를 정리하는 데 시간이 든다

 

텍스트 함수는 수식이 처음에 낯설어 보이지만, LEFT-FIND 패턴을 한 번 익히면 분리 작업에 들어가던 시간을 눈에 띄게 줄일 수 있습니다.

 

p.s. 이 글에서 다룬 텍스트 함수를 포함해, 직장인의 엑셀 반복 업무를 줄이는 방법을 전자책으로도 정리해두었습니다. 필요하신 분만 참고해보세요.

 

https://ebook-product.kyobobook.co.kr/dig/epd/ebook/E000012269516

핵심만 배우는 엑셀 실무의 기술 | 영원한배움