엑셀 데이터 정리 방법: 공백, 중복, 빈 셀 정리하기

웹사이트에서 복사했거나, 다른 시스템에서 내보냈거나, 여러 사람이 함께 입력한 데이터는 깔끔한 경우가 드물어요. 눈에 안 보이는 공백, 한 셀에 몰아넣은 두 가지 정보, 똑같이 반복된 행, 빈 셀 때문에 정렬과 필터, 수식이 엉뚱한 답을 내요. 엑셀에는 이런 문제마다 쓸 수 있는 도구가 있어요. 이 글에서는 작은 연락처 목록에 그 도구들을 차례로 써 볼게요.

fx

	A	B	C	D	E
1	이름	이메일	도시	정리한 이름	글자 수
2	Alvarez, Marco	marco.a@example.com	서울
3	Chen, Wei	wei.chen@example.com	서울시
4	Chen, Wei	wei.chen@example.com	서울시
5	Delgado, Ines		부산
6	Farooq, Sana	s.farooq@example.com	서울

TRIM으로 불필요한 공백 없애기

셀 앞뒤의 공백은 눈에 보이지 않지만 엑셀은 그것도 글자로 세요. 엑셀에게 "Chen, Wei"와 끝에 공백이 하나 붙은 "Chen, Wei "는 서로 다른 값이에요. 그래서 필터나 조회, 중복된 항목 제거(Remove Duplicates)에서 같은 값으로 취급되지 않아요.

LEN을 쓰면 문제가 보여요. LEN은 공백까지 포함해서 모든 글자 수를 세요.

=LEN(A2)복사

A2에서는 18이 나와요. "Alvarez, Marco"는 14글자뿐인데 말이에요. 나머지 4글자는 공백이에요.

1. 도우미 열에 TRIM을 쓰세요

   D2에 아래 수식을 입력하고 Enter를 누르세요. TRIM은 텍스트 앞뒤의 공백을 없애고, 중간에 여러 개 겹친 공백은 하나로 줄여요.

   =TRIM(A2)복사

   D2에 Alvarez, Marco가 표시되고, 이제 =LEN(D2)는 14를 돌려줘요.

2. 수식을 아래로 복사하세요

   D2의 채우기 핸들을 더블클릭해서 수식을 D6까지 복사하세요.

3. 원래 값을 정리한 값으로 바꾸세요

   D2:D6을 선택하고 Ctrl+C를 누르세요. A2를 클릭한 다음 홈 탭에서 붙여넣기 아래의 화살표를 클릭하고 값 붙여넣기 아래의 값을 고르세요. 이제 A열에는 수식이 아니라 정리된 텍스트 자체가 들어 있어요.

4. 도우미 열을 지우세요

   D2:D6을 선택하고 Delete를 누르세요. 이제 필요 없는 셀이에요.

찾기 및 바꾸기로 값 바꾸기

도시 열의 "서울시"와 "서울"은 같은 곳이지만, 필터나 피벗 테이블은 두 도시로 취급해요. 한쪽 값을 다른 값으로 한 번에 모두 바꾸세요.

1. 바꾸기를 여세요

   Ctrl+H를 누르거나 홈› then 편집› then 찾기 및 선택› then 바꾸기로 가세요. 찾기 및 바꾸기(Find and Replace) 대화 상자가 바꾸기 탭으로 열려요.

2. 두 상자를 채우세요

   찾을 내용에 서울시, 바꿀 내용에 서울을 입력하세요.

3. 모두 바꾸세요

   모두 바꾸기를 클릭하세요. 엑셀이 몇 개를 바꿨는지 알려 줘요. 여기서는 2개예요. 확인을 클릭하고 닫기를 클릭하세요.

대화 상자에서 옵션을 클릭하면 더 세밀하게 정할 수 있어요. 대/소문자 구분을 체크하면 "nyc"와 "NYC"를 다른 값으로 봐요. 전체 셀 내용 일치를 체크하면 정확히 "서울시"만 들어 있는 셀만 바뀌어서 "서울시청" 같은 값은 그대로 남아요. 범위에서는 시트 하나 대신 통합 문서 전체에서 바꾸도록 정할 수 있어요.

텍스트 나누기로 한 열을 두 열로 나누기

이름 열에는 "성, 이름"이 한 셀에 들어 있어요. 이름으로 정렬하거나 "Marco 님께"처럼 이름만 넣은 편지를 쓰려면 두 부분을 서로 다른 열로 나눠야 해요.

1. 새 열이 들어갈 자리를 만드세요

   B 열 머리글을 마우스 오른쪽 단추로 클릭하고 삽입을 고르세요. 텍스트 나누기는 이름의 뒷부분을 오른쪽 열에 쓰기 때문에 그 열이 비어 있어야 해요. 비어 있지 않으면 엑셀이 그 자리의 이메일을 덮어쓸지 물어요.

2. 이름을 선택하고 마법사를 여세요

   A2:A6을 선택하고 데이터› then 데이터 도구› then 텍스트 나누기(Text to Columns)로 가세요. 텍스트 마법사의 첫 단계에서 구분 기호로 분리됨을 고르고 다음을 클릭하세요.

3. 구분 기호를 고르세요

   구분 기호에서 쉼표와 공백을 체크하고, 연속된 구분 기호를 하나로 처리도 체크하세요. 그러면 쉼표와 그 뒤의 공백이 한 번의 구분으로 처리돼서 이름 앞에 공백이 붙지 않아요. 아래쪽 데이터 미리 보기에서 두 열로 나뉘는지 확인하세요.

4. 마침을 클릭하세요

   마침을 클릭하세요. A열에는 성(Alvarez, Chen, Chen, Delgado, Farooq)이, B열에는 이름이 들어가요. A1과 B1에 성, 이름처럼 새 제목을 입력하세요.

공백으로 나누면 "De la Cruz, Ana"처럼 공백이 들어 있는 이름도 쪼개져요. 미리 보기에서 이런 이름이 있는지 확인하거나, 쉼표만 체크한 다음 나중에 이름 열에 TRIM을 쓰세요.

중복 행 제거하기

Chen, Wei가 똑같은 정보로 두 번 나와요. 중복된 항목 제거는 반복된 행을 지우고 처음 나온 행만 남겨요.

1. 중복된 항목 제거를 여세요

   목록 안의 아무 셀이나 클릭하고 데이터› then 데이터 도구› then 중복된 항목 제거로 가세요.

2. 어떤 열이 같아야 하는지 고르세요

   내 데이터에 머리글 표시는 체크된 그대로 두세요. 처음에는 모든 열이 체크되어 있는데, 이건 모든 값이 앞의 행과 똑같을 때만 그 행을 지운다는 뜻이에요. 무시할 열은 체크를 해제하세요. 예를 들어 이메일만 체크하면 이름 철자가 달라도 이메일이 같은 두 행을 중복으로 봐요.

3. 확인을 클릭하세요

   엑셀이 중복된 값 1개를 찾아서 지웠고 고유한 값 4개가 남았다고 알려 줘요. 두 번째 Chen, Wei 행이 사라지고 그 아래 행들이 위로 올라와요.

중복된 항목 제거는 행을 완전히 지워요(바로 Ctrl+Z를 누르면 되돌릴 수는 있어요). 먼저 확인하고 싶다면 값마다 개수를 세어 보세요. 원래 목록에서 =COUNTIF(B2:B6,B3)은 2를 돌려줘요. B3의 이메일이 두 번 나오기 때문이에요. 결과가 1보다 크면 중복이에요.

빈 셀을 한 번에 모두 찾기

긴 목록에서는 빈 셀을 놓치기 쉬워요. 이동 옵션(Go To Special)을 쓰면 빈 셀을 한 번에 모두 선택할 수 있어요.

1. 목록을 선택하세요

   제목까지 포함해서 목록 전체를 선택하세요.

2. 이동 옵션을 여세요

   홈› then 편집› then 찾기 및 선택› then 이동 옵션으로 가세요. F5를 누르고 옵션을 클릭해도 돼요.

3. 빈 셀을 고르세요

   빈 셀을 고르고 확인을 클릭하세요. 선택 영역 안의 빈 셀이 모두 선택돼요. 여기서는 Delgado, Ines의 이메일 칸이에요.

빈 셀이 선택된 상태에서 한 번에 채울 수도 있어요. 없음 같은 값을 입력하고 Ctrl+Enter를 누르면 선택된 모든 셀에 같은 값이 들어가요. 수식으로 빈 셀을 세고 싶다면 COUNTBLANK를 쓰세요. 원래 목록에서 =COUNTBLANK(B2:B6)은 이메일 열의 빈 셀 수인 1을 돌려줘요.

자주 하는 실수

• 원본 하나뿐인 데이터를 바로 정리하기. 중복된 항목 제거와 텍스트 나누기는 데이터를 그 자리에서 바꿔요. 결과가 맞다고 확신할 때까지 원래 시트의 복사본을 남겨 두세요. 시트 탭을 마우스 오른쪽 단추로 클릭하고 이동/복사를 고른 다음 복사본 만들기를 체크하면 돼요.
• 공백을 없애기 전에 중복 제거하기. "Chen, Wei"와 "Chen, Wei "는 중복으로 잡히지 않아요. 공백부터 정리하고 중복을 제거하세요.
• TRIM으로 안 지워지는 공백. 웹 페이지에서 복사한 텍스트에는 줄 바꿈 없는 공백(non-breaking space)이 들어 있을 수 있어요. 공백처럼 보이지만 일반 공백이 아니라서 TRIM으로 지워지지 않아요. TRIM을 쓴 뒤에도 LEN이 글자를 더 많이 센다면 찾기 및 바꾸기를 쓰세요. 찾을 내용에서 Alt를 누른 채 숫자 키패드로 0160을 입력하고, 바꿀 내용은 비워 두세요.
• 데이터가 있는 열로 나누기. 텍스트 나누기는 오른쪽 셀을 덮어써요. 빈 열을 먼저 삽입하세요.

연습

연습

지저분한 내보내기 데이터 정리하기

먼저 아래 시트에서 수식을 연습해 보세요. 그다음 실제로 지저분한 목록을 가져와서(CSV로 내보낸 파일이나 웹 페이지에서 복사한 목록) 정리해 보세요. 마땅한 게 없다면 이 글 맨 위의 연락처 목록을 엑셀에 입력해도 돼요. 이 순서로 정리하세요.

1. 이름 몇 개를 LEN으로 확인하고, TRIM으로 불필요한 공백을 없앤 다음 결과를 값으로 다시 붙여 넣으세요.
2. 찾기 및 바꾸기로 같은 대상이 항상 같은 표기로 쓰이게 하세요.
3. 합쳐진 열은 텍스트 나누기(또는 빠른 채우기)로 나누세요.
4. 완전히 똑같은 중복 행을 제거하세요.
5. 이동 옵션으로 빈 셀을 선택하고, 각 셀에 무엇을 넣어야 할지 정하세요.

직접 해 보기4개 중 0개 완료

1. 1.E2에 LEN으로 A2의 글자 수를 공백까지 포함해서 세어 보세요.힌트 보기
2. 2.D2에 A2 이름의 불필요한 공백을 없애 보세요.힌트 보기
3. 3.B8에 이메일 열(B2:B6)의 빈 셀 개수를 세어 보세요.힌트 보기
4. 4.B9에 B3의 이메일이 B2:B6에 몇 번 나오는지 세어 보세요. 1보다 크면 중복이에요.힌트 보기

E2fx수식 입력 셀 E2Enter ↵

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	E
1	이름	이메일	도시	정리한 이름	글자 수
2	Alvarez, Marco	marco.a@example.com	서울
3	Chen, Wei	wei.chen@example.com	서울시
4	Chen, Wei	wei.chen@example.com	서울시
5	Delgado, Ines		부산
6	Farooq, Sana	s.farooq@example.com	서울
7
8	빈 이메일
9	B3의 개수

실력 점검

• 찾기 및 바꾸기로 시트 전체에서 한 값을 다른 값으로 바꿀 수 있나요?
• 완전히 똑같은 중복 행을 제거할 수 있나요?
• "Smith, John"을 성 열과 이름 열로 나눌 수 있나요?
• 셀의 불필요한 공백을 없애고, 빈 셀을 한 번에 모두 선택할 수 있나요?