티스토리 뷰
한 셀에 뭉쳐 있는 데이터를 나눌 때는 데이터 → 텍스트 나누기, 반대로 합칠 때는 &(앰퍼샌드) 를 씁니다. "서울시 강남구 테헤란로 123"을 시·구·도로명으로 쪼개는 것도, "김"과 "철수"를 "김철수"로 합치는 것도 5초면 됩니다. 규칙이 일정하지 않은 데이터는 빠른 채우기(Ctrl+E) 가 답입니다.
이 글에서 답하는 질문
- 한 셀에 있는 데이터를 여러 칸으로 나누려면?
- 여러 칸을 한 칸으로 합치려면?
- 구분자가 일정하지 않으면 어떻게 하나?
- 원본을 지우면 결과도 사라지는 이유는?
- 전화번호 앞의 0이 사라지는 문제는?
1. 나누기 — 방법 세 가지
| 상황 | 방법 | 특징 |
|---|---|---|
| 쉼표·공백 등 구분자가 일정 | 데이터 → 텍스트 나누기 | 가장 빠름, 원본을 덮어씀 |
| 글자 수가 일정 | LEFT·RIGHT·MID 함수 | 원본 유지, 자동 갱신 |
| 규칙이 애매함 | 빠른 채우기 (Ctrl+E) | 예시 몇 개만 보여 주면 알아서 |
2. 텍스트 나누기 — 가장 많이 쓰는 방법
"홍길동,010-1234-5678,서울" 처럼 쉼표로 구분된 데이터를 나눕니다.
1단계. 나눌 열을 통째로 선택 (열 머리글 클릭)
2단계. 데이터 탭 → 텍스트 나누기
3단계. 구분 기호로 분리됨 선택 → 다음
4단계. 구분 기호 체크 (쉼표, 공백, 탭 등) → 다음
5단계. 각 열의 데이터 서식 지정 → 마침
여기서 반드시 확인할 것 두 가지
오른쪽에 빈 열을 미리 만들어 두세요. 나뉜 데이터가 오른쪽 칸을 덮어씁니다. 3칸으로 나눌 예정이면 오른쪽에 빈 열 2개가 필요합니다. 이걸 안 하면 옆 데이터가 사라집니다.
전화번호·사업자번호는 5단계에서 "텍스트"를 선택하세요. 일반으로 두면 010-1234-5678은 괜찮지만 01012345678 같은 형태는 앞의 0이 잘려 1012345678이 됩니다.
3. 나누기 — 함수로 하기 (원본 유지)
텍스트 나누기는 원본을 덮어쓰지만, 함수는 원본을 그대로 두고 옆에 결과를 만듭니다. 원본이 계속 바뀌는 데이터라면 함수가 낫습니다.
왼쪽에서 N글자
=LEFT(A2, 3)오른쪽에서 N글자
=RIGHT(A2, 4)가운데에서 잘라내기 (시작위치, 길이)
=MID(A2, 4, 2)특정 문자 위치 찾기
=FIND("-", A2)FIND와 조합하면 구분자 기준으로 자를 수 있습니다.
첫 번째 하이픈 앞부분만
=LEFT(A2, FIND("-", A2) - 1)첫 번째 하이픈 뒷부분만
=MID(A2, FIND("-",A2)+1, LEN(A2))LEN은 전체 글자 수입니다. 넉넉하게 잡아 두면 뒤쪽이 전부 딸려 옵니다.
성과 이름 분리 (한국식 성 1글자 기준)
성: =LEFT(A2, 1)
이름: =MID(A2, 2, LEN(A2))남궁, 황보 같은 두 글자 성은 예외라 수동 확인이 필요합니다.
4. 빠른 채우기 (Ctrl+E) — 규칙이 애매할 때
이 기능 하나만 알아도 위 함수들을 다 대체할 수 있습니다.
원리는 간단합니다. 원하는 결과를 한두 개 직접 써 주면 엑셀이 패턴을 알아서 파악합니다.
사용법
- 원본 옆 칸에 원하는 결과를 직접 타이핑
- 두 번째 칸에도 한 번 더 타이핑 (패턴이 복잡하면 세 번)
- Ctrl + E
- 나머지가 한 번에 채워짐
활용 예시
홍길동→홍*동(가운데 글자 가리기)010-1234-5678→5678(뒷자리만)서울시 강남구 테헤란로 123→강남구hong@gmail.com→hong김철수 대리→대리
함수로 짜려면 복잡한 것들이 예시 두 개로 끝납니다. 엑셀 2013 이상에서 쓸 수 있습니다.
단, 결과는 값으로 고정됩니다. 원본이 바뀌어도 자동 갱신되지 않으니, 원본이 계속 변하는 데이터에는 함수를 쓰세요.
5. 합치기 — & 하나면 됩니다
=A2 & B2사이에 뭔가 넣고 싶으면 따옴표로 감싸서 끼워 넣습니다.
공백 넣기
=A2 & " " & B2구분자 넣기
=A2 & " / " & B2글자 붙이기
=A2 & "님 안녕하세요"여러 개 합치기
=A2 & "-" & B2 & "-" & C2CONCATENATE 함수도 있지만 &가 짧고 직관적이라 실무에서는 &를 더 많이 씁니다.
6. 합칠 때 자주 겪는 문제
날짜를 합쳤더니 숫자가 나옵니다
=A2 & " " & B2B2가 날짜면 46245 같은 숫자가 붙습니다. 엑셀이 날짜를 숫자로 저장하기 때문입니다.
TEXT 함수로 형식을 지정하세요.
=A2 & " " & TEXT(B2, "yyyy-mm-dd")금액을 합쳤더니 쉼표가 사라집니다
=A2 & " " & TEXT(B2, "#,##0") & "원"빈 칸 때문에 공백이 두 번 들어갑니다
=TRIM(A2 & " " & B2 & " " & C2)TRIM으로 감싸면 중복 공백이 하나로 정리됩니다.
7. 가장 중요한 주의사항 — 원본을 지우면 결과가 사라집니다
합치기·나누기 함수의 결과는 원본을 참조하고 있습니다. 원본 열을 지우면 결과가 전부 #REF! 오류로 바뀝니다.
원본을 지우고 싶다면 순서가 있습니다.
- 결과 열 전체 선택 → Ctrl + C
- 같은 자리에서 마우스 오른쪽 → 선택하여 붙여넣기 → 값
- 이제 수식이 값으로 바뀌었으므로 원본 열을 지워도 안전
이 단계를 건너뛰고 원본부터 지우는 것이 초보자가 가장 자주 하는 실수입니다.
8. 실무 활용 예시
거래처명 + 사업자번호로 고유키 만들기
=A2 & "|" & B2중복 검사용 키를 만들 때 씁니다. 구분자로 파이프(|)를 넣는 이유는, 구분자 없이 이어 붙이면 "AB"+"C"와 "A"+"BC"가 똑같아져 엉뚱한 중복이 잡히기 때문입니다.
주소에서 시·도만 추출
=LEFT(A2, FIND(" ", A2) - 1)첫 공백 앞까지 자릅니다.
이메일에서 도메인만 추출
=MID(A2, FIND("@",A2)+1, LEN(A2))전화번호 하이픈 제거
바꾸기(Ctrl+H)에서 -를 찾아 빈칸으로 바꾸면 됩니다. 다만 결과가 숫자가 되어 앞의 0이 사라지니, 미리 셀 서식을 텍스트로 바꾸거나 SUBSTITUTE를 쓰세요.
=SUBSTITUTE(A2, "-", "")9. 첨부 실습파일 사용법
시트 세 개입니다.
- 나누기실습: 쉼표로 뭉친 고객 정보를 LEFT·MID·FIND 조합으로 분리한 예시입니다. 수식을 클릭해 직접 확인해 보세요.
- 합치기실습: &와 TEXT 함수 조합, TRIM 처리 예시입니다.
- 주소분리: 주소에서 시·도, 구·군을 추출하는 실전 예시입니다.
노란색 칸의 원본 데이터를 바꾸면 결과가 즉시 따라옵니다.
마무리
세 가지만 기억하면 됩니다.
- 구분자가 일정하면 데이터 → 텍스트 나누기 (오른쪽 빈 칸 확보 필수)
- 원본을 유지하려면 LEFT·MID·FIND
- 규칙이 애매하면 Ctrl + E
그리고 원본을 지우기 전에는 반드시 값으로 붙여넣기부터. 이것만 지키면 데이터가 날아갈 일이 없습니다.
관련 글
- 엑셀 중복값 찾기·표시·제거 완전정리
- 엑셀 IFERROR 함수 완전정리 — 오류 6가지 한 번에 처리하는 법
- 엑셀 날짜 계산 완전정리 — DATEDIF로 근속연수·D-day 구하기
- 엑셀 VLOOKUP #N/A 오류 뜨는 이유와 해결법 5가지
- 엑셀 피벗테이블 처음 만드는 법
공식 출처
- Microsoft 지원 — 텍스트를 여러 열로 분할: https://support.microsoft.com/ko-kr/office/%ED%85%8D%EC%8A%A4%ED%8A%B8%EB%A5%BC-%EC%97%AC%EB%9F%AC-%EC%97%B4%EB%A1%9C-%EB%B6%84%ED%95%A0-7ca35f59-84da-4163-8770-ef0c19c9f544
- Microsoft 지원 — 데이터 열 결합: https://support.microsoft.com/ko-kr/office/%EB%91%90-%EA%B0%9C-%EC%9D%B4%EC%83%81%EC%9D%98-%EC%97%B4%EC%9D%98-%ED%85%8D%EC%8A%A4%ED%8A%B8%EB%A5%BC-%ED%95%98%EB%82%98%EC%9D%98-%EC%85%80%EB%A1%9C-%EA%B2%B0%ED%95%A9-1ecc0810-45ec-4a94-8d66-1cd813506d3f
ALT 텍스트 추천
- LEFT MID FIND 함수로 셀 내용을 분리한 결과 비교
- 빠른 채우기 Ctrl E로 패턴을 자동 인식해 채운 화면
- 앰퍼샌드로 여러 셀을 합치고 TEXT 함수로 날짜 형식을 지정한 예시
- 선택하여 붙여넣기 값으로 수식을 고정하는 화면