엑셀 파일의 행이 수천 개를 넘어가면 잘못된 텍스트 하나를 직접 수정하는 것도 큰 일이 됩니다.
예를 들어 거래처명이
주식회사 가나다
와
(주)가나다
로 섞여 있거나, 상품코드 뒤에 불필요한 공백이 붙어 있을 수 있습니다.
이럴 때 가장 먼저 떠올리는 기능이 찾기 및 바꾸기입니다.
Windows Excel에서는 보통
Ctrl + H
를 누르면 바로 사용할 수 있습니다.
하지만 데이터가 많은 파일에서는 곧바로 모두 바꾸기를 누르는 것이 오히려 위험할 수 있습니다.
같은 글자가 상품명, 설명, 수식, 다른 시트에도 들어 있다면 예상하지 못한 곳까지 한꺼번에 바뀔 수 있기 때문입니다.
따라서 대용량 파일에서는 다음 순서로 작업하는 것이 안전합니다.
검색 범위 지정 → 찾기 결과 확인 → 일치 조건 설정 → 일부 바꾸기 테스트 → 모두 바꾸기 → 결과 검수
공백 문제라면 여기에 한 단계가 더 필요합니다.
일반 공백인지, 앞뒤 공백인지, 보이지 않는 특수 문자까지 포함된 문제인지 먼저 구분해야 합니다.
가장 먼저 원본 파일을 복사합니다
대량 변경 작업에서 가장 중요한 것은 기능 자체가 아니라 되돌릴 방법입니다.
파일을 열자마자 다음처럼 복사본을 하나 만드는 편이 좋습니다.
매출자료_원본.xlsx
매출자료_정리작업.xlsx
특히 수만 행에 대해 ‘모두 바꾸기’를 실행했다가 잘못된 범위를 변경하면 작업을 처음부터 다시 해야 할 수도 있습니다.
실행 직후에는 Ctrl + Z로 되돌릴 수 있지만 파일을 저장하고 닫은 뒤에는 상황이 달라질 수 있습니다.
따라서 대량 수정은 원본이 아닌 작업용 사본에서 진행합니다.
1. Ctrl+H를 누르기 전에 바꿀 열부터 선택합니다
예를 들어 A열에는 거래처명, B열에는 담당자, C열에는 비고가 있다고 가정해 보겠습니다.
A열의
㈜한빛
을
주식회사 한빛
으로 바꾸려는 상황입니다.
그런데 통합 문서 전체에서 바꾸기를 실행하면 비고란이나 다른 시트에 들어 있는 같은 표현까지 바뀔 수 있습니다.
따라서 먼저 수정할 범위만 선택합니다.
예:
A2:A20000
범위를 선택한 상태에서 Ctrl+H를 실행합니다.
이렇게 하면 선택한 영역을 기준으로 작업 범위를 제한하기가 쉬워집니다.
2. ‘모두 바꾸기’보다 먼저 ‘모두 찾기’를 눌러봅니다
Microsoft Excel의 찾기 기능에는 다음 찾기와 모두 찾기가 있습니다.
특히 대량 데이터에서는 모두 찾기가 유용합니다.
예를 들어 찾을 내용에
㈜한빛
을 입력하고 모두 찾기를 누르면 해당 값이 발견되는 위치를 목록으로 확인할 수 있습니다.
이 단계에서 확인할 것은 다음과 같습니다.
- 예상한 개수만큼 검색됐는가
- 다른 열에도 같은 값이 있는가
- 수식 안에도 포함되어 있는가
- 다른 시트에 같은 단어가 있는가
- 실제로 바꾸고 싶은 값만 검색되는가
바로 모두 바꾸기를 누르면 이런 검수 기회를 놓치게 됩니다.
예를 들어 35건을 예상했는데 1,240건이 검색된다면
바꾸기를 진행하기 전에 멈추는 것이 좋습니다.
검색 조건이 너무 넓을 가능성이 있기 때문입니다.
예를 들어
서울
을
서울특별시
로 변경하려고 했는데 주소뿐 아니라
서울영업소
서울담당
서울매출
같은 다른 텍스트에도 모두 포함되어 있을 수 있습니다.
이런 경우 단순히 ‘서울’이라는 글자가 들어 있다는 이유만으로 모두 바꾸면 데이터가 망가질 수 있습니다.
3. 셀 내용 전체가 같은 경우만 바꾸고 싶다면 옵션을 사용합니다
Excel의 찾기 및 바꾸기 창에는 검색 옵션이 있습니다.
Microsoft 공식 안내에 따르면 다음과 같은 조건을 설정할 수 있습니다.
- 시트 또는 통합 문서
- 행 또는 열 방향
- 대/소문자 구분
- 셀 내용 전체 일치
- 수식·값·메모·주석 등 검색 대상
예를 들어 셀 값이 정확하게
미처리
인 경우만
완료
로 바꾸고 싶다고 가정해 보겠습니다.
그런데 다른 셀에
미처리 예정
이라는 문구도 있습니다.
단순 검색 후 모두 바꾸기를 하면
완료 예정
으로 바뀔 수 있습니다.
이럴 때는 셀 내용 전체 일치 옵션을 사용하면 정확히 같은 값만 대상으로 제한할 수 있습니다.
4. 통합 문서 전체를 검색할 때는 더 신중해야 합니다
찾기 범위에서는 현재 시트뿐 아니라 통합 문서 전체를 검색할 수도 있습니다.
여러 월별 시트를 한 번에 검색해야 할 때 편리합니다.
예를 들어
1월
2월
3월
시트 모두에서 회사명을 변경해야 한다면 통합 문서 검색이 유용할 수 있습니다.
하지만 이 범위에서 ‘모두 바꾸기’를 실행하면 모든 시트에 영향을 줄 수 있습니다.
따라서 통합 문서 전체를 대상으로 할 때는
모두 찾기 → 결과 시트 확인 → 바꾸기
순서가 특히 중요합니다.
5. 와일드카드를 이용하면 비슷한 문자열도 찾을 수 있습니다
Excel의 찾기 및 바꾸기에는 와일드카드를 사용할 수 있습니다.
Microsoft 공식 안내에서 제공하는 대표 문자는 다음과 같습니다.
| 문자 | 의미 |
|---|---|
? | 문자 하나 |
* | 여러 문자 |
~ | ?, *, ~ 자체를 찾을 때 사용 |
예를 들어
s?t
는 가운데 글자가 하나인 문자열을 찾을 수 있습니다.
*서울
은 서울 앞에 여러 문자가 붙은 문자열을 찾는 식으로 사용할 수 있습니다.
하지만 별표(*)를 잘못 쓰면 너무 많은 데이터가 잡힐 수 있습니다
예를 들어 찾을 내용에
*회사*
를 넣으면 ‘회사’ 앞뒤에 어떤 문자가 붙더라도 검색 대상이 될 수 있습니다.
대량 파일에서는 생각보다 많은 셀이 검색될 수 있습니다.
따라서 와일드카드를 처음 사용할 때는 바로 바꾸지 말고 모두 찾기 결과부터 확인합니다.
6. 별표(*) 문자 자체를 찾고 싶다면 ~를 붙입니다
Excel에서 *는 와일드카드입니다.
따라서 셀 안에 실제 별표 문자를 찾고 싶은데 검색창에 단순히
*
만 입력하면 의도와 다른 검색 결과가 나올 수 있습니다.
Microsoft는 *, ?, ~ 같은 문자를 문자 그대로 찾을 때 ~를 앞에 붙이도록 안내합니다.
예를 들어 실제 *를 찾고 싶다면
~*
를 사용할 수 있습니다.
물음표 자체를 찾으려면
~?
를 사용할 수 있습니다.
7. 모든 공백을 없애고 싶다면 먼저 목적을 확인합니다
공백 제거에서 가장 많이 발생하는 실수는 ‘공백이면 전부 없애도 된다’고 생각하는 것입니다.
예를 들어
서울 강남구 테헤란로
에서 모든 공백을 없애면
서울강남구테헤란로
가 됩니다.
상품명
무선 키보드 블랙
도
무선키보드블랙
으로 바뀝니다.
따라서 공백 문제에서는 먼저 어떤 공백을 제거하려는지 구분해야 합니다.
경우 1
앞뒤에 잘못 들어간 공백
경우 2
단어 사이에 공백이 여러 개 들어감
경우 3
모든 공백을 없애야 하는 코드 데이터
이 세 가지는 해결 방법이 다릅니다.
8. 앞뒤 공백은 TRIM 함수가 더 적절할 수 있습니다
Microsoft의 TRIM 함수는 텍스트의 불필요한 공백을 정리하는 함수입니다.
앞뒤 공백을 제거하고 단어 사이에 여러 개의 일반 공백이 있다면 하나로 정리합니다.
예를 들어 A2에
서울 영업팀
이 들어 있다면
=TRIM(A2)
을 사용할 수 있습니다.
결과는 일반적으로
서울 영업팀
처럼 정리됩니다.
대량 파일에서는 원본 열에 바로 적용하지 않습니다
예를 들어 A열이 원본 거래처명이라면 B열에
=TRIM(A2)
를 입력합니다.
그다음 아래 행까지 채워 결과를 확인합니다.
| 원본 | TRIM 적용 |
|---|---|
가나다상사 | 가나다상사 |
서울 영업팀 | 서울 영업팀 |
부산지점 | 부산지점 |
결과가 맞다면 정리된 값을 복사한 뒤 필요에 따라 값 붙여넣기로 원본을 교체할 수 있습니다.
원본 열을 바로 덮어쓰지 않는 이유는 예상하지 못한 데이터가 바뀌는지 검수하기 위해서입니다.
9. TRIM으로 안 없어지는 공백도 있습니다
여기서 실무에서 자주 막히는 부분이 있습니다.
분명히 TRIM을 사용했는데도 공백처럼 보이는 문자가 남아 있을 수 있습니다.
Microsoft 공식 문서에 따르면 TRIM은 기본적으로 ASCII 값 32의 일반 공백을 정리하도록 설계됐습니다.
웹페이지 등에서 사용되는 줄 바꿈 없는 공백(nonbreaking space) 같은 다른 공백 문자는 TRIM만으로 제거되지 않을 수 있습니다.
따라서 웹사이트에서 복사한 데이터나 외부 시스템에서 가져온 자료는 단순한 일반 공백 문제와 다를 수 있습니다.
10. 웹에서 복사한 데이터라면 SUBSTITUTE를 함께 확인합니다
Microsoft의 SUBSTITUTE 함수는 특정 텍스트를 다른 텍스트로 바꾸는 함수입니다.
기본 구조는 다음과 같습니다.
=SUBSTITUTE(텍스트,기존문자,새문자)
예를 들어 A2에 있는 모든 하이픈을 제거한다면
=SUBSTITUTE(A2,"-","")
처럼 사용할 수 있습니다.
결과:
010-1234-5678
↓
01012345678
이 방식은 특정 문자를 규칙적으로 제거하거나 변경해야 할 때 유용합니다.
일반 바꾸기와 SUBSTITUTE의 차이
둘 다 텍스트를 바꾸지만 목적이 약간 다릅니다.
Ctrl+H 찾기 및 바꾸기
현재 데이터 자체를 직접 변경할 때 적합
SUBSTITUTE 함수
원본을 유지하면서 별도 열에서 변환 결과를 만들 때 적합
대량 파일을 정리하면서 결과를 먼저 검수하고 싶다면 SUBSTITUTE가 더 안전할 수 있습니다.
11. 특정 번째 문자만 바꾸려면 SUBSTITUTE의 선택 인수를 사용할 수 있습니다
Microsoft 공식 설명에 따르면 SUBSTITUTE에서는 몇 번째로 나타난 문자열만 바꿀지도 지정할 수 있습니다.
예를 들어 같은 문자가 여러 번 있지만 첫 번째 것만 교체해야 한다면 선택적으로 적용할 수 있습니다.
이는 ‘모든 같은 문자를 지우는 것’과 다릅니다.
대량 데이터에서는 이런 차이가 중요합니다.
12. 줄바꿈이나 제어 문자가 섞였으면 CLEAN도 검토합니다
외부 시스템에서 복사한 텍스트에는 눈에 보이지 않는 제어 문자가 포함될 수 있습니다.
이런 데이터는 셀 안에서 줄바꿈처럼 보이거나 조회 함수에서 값이 다르게 인식되는 원인이 될 수 있습니다.
Microsoft는 데이터 정리 예시에서 TRIM과 CLEAN을 함께 사용할 수 있다고 안내합니다.
예:
=TRIM(CLEAN(A2))
이렇게 보조 열에서 결과를 먼저 확인할 수 있습니다.
단, 모든 특수문자가 CLEAN 하나로 해결된다고 단정하면 안 됩니다.
문자의 종류에 따라 별도의 정리가 필요할 수 있습니다.
13. 공백을 전부 삭제해야 하는 데이터도 있습니다
예를 들어 코드가 원래
AB12345
여야 하는데
AB 123 45
처럼 잘못 들어왔다고 가정해 보겠습니다.
이 데이터에서 공백 자체가 불필요하다면
=SUBSTITUTE(A2," ","")
처럼 일반 공백을 모두 제거할 수 있습니다.
결과:
AB 123 45
↓
AB12345
이 방식은 상품코드, 관리번호 등 원래 공백이 없어야 하는 데이터에서 유용합니다.
하지만 주소나 사람 이름처럼 정상적인 공백이 필요한 데이터에는 그대로 사용하면 안 됩니다.
14. ‘모두 바꾸기’와 함수 중 어떤 것을 써야 할까?
다음 기준으로 구분하면 쉽습니다.
| 상황 | 추천 방식 |
|---|---|
| 회사명 하나를 다른 이름으로 변경 | Ctrl+H |
| 특정 코드만 일괄 교체 | Ctrl+H |
| 앞뒤 공백 정리 | TRIM |
| 단어 사이 여러 공백 정리 | TRIM |
| 모든 일반 공백 삭제 | SUBSTITUTE |
| 특정 문자만 제거 | SUBSTITUTE |
| 숨은 제어 문자 정리 | CLEAN 검토 |
| 결과를 확인한 후 원본 변경 | 보조 열 함수 |
가장 중요한 차이는 원본을 즉시 바꿀 것인가, 변환 결과를 먼저 확인할 것인가입니다.
15. 수식이 들어 있는 열에서는 더욱 주의합니다
대량 파일에는 단순 텍스트뿐 아니라 수식이 들어 있을 수 있습니다.
예를 들어 셀 화면에는
서울지점
이라고 보이지만 실제 수식은
=A2&"지점"
일 수 있습니다.
이 상태에서 검색 대상을 ‘값’으로 볼지 ‘수식’으로 볼지에 따라 결과가 달라질 수 있습니다.
Excel의 찾기 옵션에서는 검색 대상을 세부적으로 지정할 수 있습니다.
따라서 수식이 많은 파일에서는 단순히 보이는 값만 보고 모두 바꾸기를 실행하지 않습니다.
16. 수식 안의 셀 주소를 바꾸는 경우는 특히 조심합니다
예를 들어 찾을 내용에
A1
을 입력하고 모두 바꾸기를 실행한다고 가정해 보겠습니다.
수식 안에 같은 문자열이 들어 있다면 예상하지 못한 수식까지 영향을 받을 수 있습니다.
대용량 파일에서는 수식 변경이 숫자 결과까지 바꿀 수 있으므로 단순 텍스트 정리보다 위험도가 높습니다.
수식 안 내용을 변경해야 한다면 반드시 복사본에서 먼저 테스트합니다.
17. 대소문자가 중요한 코드라면 ‘대/소문자 구분’을 확인합니다
한국어 데이터에서는 크게 신경 쓰지 않는 경우가 많지만 영문 상품코드나 시스템 코드는 다를 수 있습니다.
예를 들어
abc01
과
ABC01
을 서로 다른 코드로 사용하는 시스템이라면 대소문자를 구분해야 합니다.
Excel 찾기 옵션에는 대/소문자 구분 기능이 있습니다.
영문 코드 정리에서는 이 설정을 먼저 확인합니다.
18. 0을 빈칸으로 바꾸는 작업도 함부로 하면 안 됩니다
예를 들어 매출 데이터의 0을 모두 빈칸으로 바꾸고 싶다고 가정해 보겠습니다.
단순히
찾을 내용: 0
바꿀 내용: 빈칸
으로 모두 바꾸면
100
2026-09-08
A001
등 다른 데이터에 포함된 숫자 0까지 영향을 받을 가능성이 있습니다.
이런 경우 정확히 값이 0인 셀만 대상으로 해야 합니다.
셀 내용 전체 일치 옵션을 활용하거나 작업 범위를 숫자 열로 제한하는 것이 좋습니다.
19. ‘없음’을 빈칸으로 만들 때도 범위를 제한합니다
예를 들어 상태 열에만
없음
이라는 값이 들어 있습니다.
이 값을 빈칸으로 만들고 싶다면 상태 열만 선택한 뒤 찾기 및 바꾸기를 실행합니다.
문서 전체에서 없음을 없애면 비고란의
특이사항 없음
까지
특이사항
처럼 바뀔 수 있습니다.
한 단어를 바꾸더라도 데이터의 의미까지 생각해야 합니다.
20. 대량 수정 후에는 개수가 맞는지 확인합니다
모두 바꾸기를 실행하면 Excel에서 몇 개의 항목이 변경되었는지 결과가 표시됩니다.
예를 들어 미리 428건이 있을 것으로 예상했는데 실제 변경 결과가 4,281건이라면 바로 결과를 검수합니다.
대량 작업에서는 숫자가 중요한 경고 신호가 됩니다.
변경 전 예상
약 430건
실제 결과
428건
→ 예상 범위와 비슷함
실제 결과
4,281건
→ 검색 범위 또는 조건이 너무 넓었을 가능성 확인
21. 변경 전후 개수를 기록하면 실수를 찾기 쉽습니다
예를 들어 작업 전에 다음처럼 간단히 적어둘 수 있습니다.
| 항목 | 변경 전 | 변경 후 |
|---|---|---|
㈜가나다 검색 결과 | 428 | 0 |
주식회사 가나다 | 15 | 443 |
※ 위 숫자는 설명용 예시입니다.
기존에 주식회사 가나다가 15건 있었고 428건을 새로 변경했다면 최종 결과가 443건이 되는 것이 자연스럽습니다.
이런 검산만 해도 대량 변경 오류를 빨리 발견할 수 있습니다.
22. 필터를 사용한 상태에서는 더 조심합니다
대용량 파일에서는 필터를 걸어 필요한 행만 보이게 만든 뒤 작업하는 경우가 많습니다.
이때 화면에 보이는 행만 수정될 것이라고 생각하면 위험할 수 있습니다.
찾기 및 바꾸기 작업을 실행하기 전에는 실제 선택 범위가 어디까지인지 다시 확인하는 것이 좋습니다.
특히 숨겨진 행이나 다른 영역까지 변경되면 안 되는 파일에서는 샘플 복사본으로 먼저 테스트합니다.
23. 한 번에 여러 정리를 하지 않는 편이 좋습니다
예를 들어 다음 작업을 동시에 하려는 상황이 있다고 가정해 보겠습니다.
- 회사명 통일
- 하이픈 삭제
- 공백 삭제
- 지점명 변경
한 번에 모두 처리하면 어느 단계에서 잘못됐는지 찾기 어려워집니다.
따라서 다음처럼 분리합니다.
1차: 회사명 변경
저장 또는 결과 확인
2차: 코드 하이픈 제거
결과 확인
3차: 불필요한 공백 정리
결과 확인
하나씩 변경해야 오류가 발생했을 때 되돌릴 범위를 알 수 있습니다.
대용량 파일이라면 이 순서가 가장 안전합니다
1단계
원본 파일을 복사합니다.
2단계
수정할 열 또는 범위를 먼저 선택합니다.
3단계
Ctrl+F 또는 Ctrl+H로 검색합니다.
4단계
바꾸기 전에 모두 찾기를 실행합니다.
5단계
검색된 개수와 위치가 예상과 맞는지 확인합니다.
6단계
필요하면 셀 내용 전체 일치·대소문자 구분·시트 범위를 설정합니다.
7단계
한두 개를 먼저 바꾸기로 시험합니다.
8단계
결과가 맞으면 모두 바꾸기를 실행합니다.
9단계
변경된 개수를 기록합니다.
10단계
다시 검색해 기존 값이 남아 있는지 확인합니다.
공백 문제라면 이 순서로 구분합니다
앞뒤 공백만 제거하고 싶다
TRIM
단어 사이 여러 공백도 하나로 정리하고 싶다
TRIM
모든 일반 공백을 없애고 싶다
SUBSTITUTE(A2," ","")
특정 문자만 바꾸고 싶다
SUBSTITUTE
외부 데이터의 숨은 문자가 의심된다
CLEAN과 TRIM 조합을 보조 열에서 검토
TRIM으로도 공백이 남는다
일반 공백이 아닌 특수 공백인지 확인
저장해두기 좋은 실수 방지표
| 작업 | 바로 모두 바꾸기? | 더 안전한 방법 |
|---|---|---|
| 거래처명 변경 | △ | 검색 결과 먼저 확인 |
| 상품코드 변경 | △ | 해당 코드 열만 선택 |
| 앞뒤 공백 제거 | X | TRIM 보조 열 |
| 모든 공백 삭제 | X | 데이터 성격 확인 후 SUBSTITUTE |
| 웹 복사 데이터 정리 | X | TRIM/CLEAN 및 특수문자 확인 |
| 수식 내부 텍스트 변경 | X | 복사본에서 테스트 |
| 통합 문서 전체 변경 | X | 모두 찾기로 시트별 결과 검수 |
자주 하는 실수
모두 바꾸기부터 누르기
수정 범위가 넓으면 예상하지 못한 데이터까지 변경됩니다.
현재 보이는 셀만 바뀐다고 생각하기
실제 선택 범위와 검색 범위를 먼저 확인해야 합니다.
모든 공백을 제거하기
주소, 상품명, 사람 이름의 정상 공백까지 없어질 수 있습니다.
TRIM이면 모든 공백이 제거된다고 생각하기
Microsoft 설명처럼 일반 공백이 아닌 문자는 남을 수 있습니다.
별표를 일반 문자처럼 검색하기
*는 와일드카드이므로 실제 별표를 찾으려면 ~*처럼 입력해야 합니다.
변경 건수를 확인하지 않기
예상보다 수십 배 많은 셀이 바뀌었다면 즉시 조건을 재검토해야 합니다.
마무리
엑셀 대용량 파일에서 텍스트를 한꺼번에 바꾸는 것은 어렵지 않습니다.
Ctrl + H
만 누르면 바로 실행할 수 있습니다.
하지만 행이 많을수록 중요한 것은 버튼을 누르는 방법이 아니라 변경 범위를 통제하는 방법입니다.
따라서 바로 모두 바꾸기를 누르기보다
수정 범위 선택
↓
모두 찾기로 대상 확인
↓
일치 조건 설정
↓
1~2개 테스트 변경
↓
모두 바꾸기
↓
변경 개수 검수
순서로 진행하는 것이 좋습니다.
공백 문제는 더 조심해야 합니다.
앞뒤의 불필요한 공백만 정리하려면 TRIM이 적합하고, 모든 일반 공백을 없애야 하는 코드라면 SUBSTITUTE를 사용할 수 있습니다.
웹이나 외부 시스템에서 복사한 데이터라면 일반 공백과 다른 문자가 섞여 있어 TRIM만으로 해결되지 않을 수도 있습니다.
결국 대량 데이터 정리에서 가장 중요한 원칙은 하나입니다.
먼저 무엇이 바뀔지 확인하고, 그다음 한꺼번에 바꿉니다.
이 순서만 지켜도 수천 행의 데이터를 빠르게 정리하면서 ‘모두 바꾸기 한 번으로 파일 전체를 다시 손봐야 하는 상황’을 크게 줄일 수 있습니다.
※ 이 글은 Microsoft Excel의 찾기 및 바꾸기, 와일드카드 검색, TRIM 및 SUBSTITUTE 함수 공식 문서를 기준으로 작성했습니다. Excel 버전과 운영체제에 따라 메뉴 표시 방식은 다를 수 있으며, 대량 데이터 변경 전에는 원본 파일의 별도 복사본을 만들어 작업하는 것이 좋습니다.
함께 읽으면 좋은 글
이전 단계: 엑셀 VLOOKUP #N/A 오류가 발생하는 원인과 해결법
현재 단계 확장: 엑셀에서 중복값을 찾아 정리할 때 원본 데이터를 보존하는 방법
다음 단계: 피벗 테이블에 새 데이터가 추가되지 않을 때 원본 범위를 자동으로 확장하는 방법
