Excel 와일드카드 일괄 치환 튜토리얼: 정규 표현식으로 여러 xlsx 파일을 동시에 수정하기

여러 Excel 통합 문서에 동일한 규칙의 번호, 코드 또는 키워드가 존재할 때 파일을 하나씩 열어 찾아 바꾸는 작업은 시간이 오래 걸릴 뿐만 아니라 누락되기 쉽습니다. 이 문서에서는 병원 CCN 번호와 CBSA 코드를 일괄 수정하는 예를 들어 HeSoft Doc Batch Tool 을(를) 사용하여 여러 xlsx 파일을 가져오고, “수식을 사용하여 텍스트 유사 검색”을 통해 와일드카드 정규식을 설정한 뒤 서로 다른 일치 규칙에 대해 각각 대체 내용을 지정하는 방법을 소개합니다. 문서의 작업 절차에 따라 여러 통합 문서의 셀 텍스트를 한 번에 처리하여 반복 작업을 줄이고, 처리 전후 효과 비교, 규칙 대응 관계 및 백업 권장 사항을 통해 일괄 바꾸기의 정확성과 통제 가능성을 높일 수 있습니다.

수십 개에서 수백 개의 Excel 통합 문서에 수정해야 할 번호, 제품 코드, 지역 코드 또는 고정 형식 키워드가 포함된 경우 Excel 자체의 찾기 및 바꾸기 기능으로 하나씩 처리하면 많은 반복 작업이 발생합니다. 특히 대상 내용이 완전히 동일하지 않고 공통 형식을 가지는 경우, 예를 들어 030으로 시작하는 모든 6자리 번호, 46으로 시작하는 모든 5자리 코드와 같은 경우 일반적인 정확한 찾기로는 한 번에 처리하기 어렵고, 수동 필터링은 누락되기 쉽습니다.

이 문서에서 해결하려는 문제는 와일드카드 정규 표현식을 활용하여 여러 xlsx 파일에서 특정 규칙에 맞는 셀 내용을 동시에 찾아 바꾸는 방법입니다. 예제에서 사용한 오피스 소프트웨어는 HeSoft Doc Batch Tool 이며, Word, Excel, PowerPoint, PDF 등 오피스 문서를 대상으로 하는 일괄 처리 도구로 자리매김하고 있습니다. 이번에는 그중 “Excel에서 키워드 찾기 및 바꾸기” 기능을 사용하여 여러 Excel 파일을 같은 작업에 추가한 뒤, 수식 퍼지 찾기 방식을 통해 일괄 치환을 완료합니다.

1. 적용 시나리오: 어떤 Excel 내용이 와일드카드 일괄 치환에 적합한가

정확한 찾기는 완전히 같은 텍스트를 처리하는 데 적합합니다. 예를 들어 모든 “옛 회사 이름”을 “새 회사 이름”으로 바꾸는 경우입니다. 그러나 실제 업무 데이터에서는 처리할 내용이 구조만 같고 구체적인 문자는 완전히 일치하지 않는 경우가 많으므로, 이때는 와일드카드나 정규 표현식 형태의 퍼지 규칙을 사용하는 것이 더 적합합니다.

일반적인 사용 사례는 다음과 같습니다.

• 동일한 접두사를 가진 직원 번호, 고객 번호, 병원 번호 또는 프로젝트 번호를 일괄 치환합니다.
• 여러 Excel 보고서의 지역 코드, 부서 코드, 분류 코드를 통일된 체계로 일괄 전환합니다.
• 외부 발송 데이터를 만들 때 특정 형식에 맞는 번호를 테스트 값이나 비식별화 값으로 일괄 치환합니다.
• 여러 xlsx 통합 문서가 옛 코딩 규칙을 사용하고 있어 규칙에 따라 새 자리 표시 코드로 변경해야 합니다.
• 템플릿, 월간 보고서, 대장, 보관 테이블이 많아 한 번에 여러 파일을 대상으로 찾기 및 바꾸기를 수행해야 합니다.

Excel 파일을 하나씩 여는 방식과 비교하면, 일괄 처리의 가치는 클릭 횟수를 줄이는 것뿐만 아니라 모든 파일에 동일한 찾기 조건을 적용할 수 있다는 점에 있습니다. 규칙이 명확하고 파일 수가 많은 작업일수록 이 방식은 결과 일관성을 유지하는 데 더 유리합니다.

2. 결과 미리 보기: 처리 전후 무엇이 달라졌는가

1. 처리 전 Excel 데이터

처리 전 워크시트에는 Hospital CCN, Hospital Name, CBSA, CBSA Name 등의 필드가 포함되어 있습니다. 스크린샷에서 Hospital CCN 열에는 030006, 030010, 030011, 030064 등 030으로 시작하는 6자리 번호가 있고, CBSA 열에는 46060 등 46으로 시작하는 5자리 코드가 연속으로 나타납니다. 빨간 화살표는 이번에 일괄 처리해야 할 두 데이터 영역을 표시합니다.

이 번호들은 구체적인 끝자리 숫자가 달라 전체 키워드 하나만 입력해서 정확히 치환할 수는 없지만, “030 + 세 자리 숫자”와 “46 + 세 자리 숫자”라는 안정적인 형식을 가집니다. 따라서 각각 퍼지 찾기 규칙을 작성할 수 있습니다.

2. 처리 후 Excel 데이터

처리가 끝나면 원래 030으로 시작하는 규칙에 일치한 Hospital CCN 번호는 111111로, 46으로 시작하는 규칙에 일치한 CBSA 코드는 22222로 일괄 치환됩니다. 스크린샷의 노란색 영역은 치환 결과를 명확히 보여 주며, 규칙에 맞지 않는 010039, 010052, 29300 등은 그대로 유지됩니다.

이는 규칙이 열 전체를 무차별로 덮어쓰는 것이 아니라, 표현식에 맞는 셀 텍스트를 찾아 치환을 수행함을 보여 줍니다. 실제 사용 시에는 먼저 사본으로 테스트하여 표현식의 적용 범위가 예상과 일치하는지 확인한 뒤 정식 파일을 처리해야 합니다.

3. 작업 단계: 여러 Excel 파일 일괄 찾기 및 바꾸기

1단계: Excel 키워드 찾기 및 바꾸기 기능으로 들어가기

HeSoft Doc Batch Tool 를 열고 왼쪽 기능 분류에서 “Excel 도구”를 선택합니다. 메인 화면에 여러 Excel 일괄 처리 기능이 표시되면, “Excel에서 키워드 찾기 및 바꾸기”를 찾아 클릭합니다. 이 기능의 설명은 Excel 파일 내용에서 키워드를 일괄로 찾아 바꾸는 것으로, 이번 여러 파일 치환 작업과 대응합니다.

이 단계의 목적은 전용 Excel 내용 치환 작업으로 들어가는 것이며, 파일 이름 바꾸기나 다른 문서 도구를 사용하는 것이 아닙니다. 진입하면 인터페이스는 기록 선택, 처리 옵션 설정, 저장 위치 설정, 처리 시작 순서로 안내합니다.

2단계: 처리할 여러 xlsx 통합 문서 추가하기

“처리할 기록 선택” 페이지에서 “파일 추가”를 클릭해 통합 문서를 하나씩 선택하거나, “폴더에서 파일 가져오기”를 클릭해 같은 디렉터리의 파일을 한꺼번에 추가할 수 있습니다. 스크린샷 예제에서는 product_list.xlsx, team-participant-list.xlsx, test.xlsx 세 파일을 한 번에 추가했으며, 목록에는 파일 이름, 경로, 확장자, 만든 날짜, 수정한 날짜가 함께 표시됩니다.

추가한 뒤에는 파일 수와 경로를 먼저 확인하여 수정할 필요가 없는 통합 문서가 작업에 포함되지 않도록 합니다. 파일을 잘못 선택한 경우 목록 오른쪽의 삭제 작업으로 제거할 수 있고, 다시 선택해야 한다면 페이지의 “비우기”를 사용할 수도 있습니다. 파일 목록이 올바른지 확인한 후 “다음”을 클릭합니다.

3단계: 처리 범위와 셀 유형 정하기

“처리 옵션 설정” 페이지에서 먼저 Excel 처리 범위를 설정합니다. 예제에서는 워크시트 셀의 번호를 수정해야 하므로 “셀 텍스트”를 선택했으며, 워크시트 Sheet 이름이나 도형의 텍스트가 아닙니다.

페이지에는 워크시트 이름 조건, 셀 열 이름 조건, 셀 수식 유형, 수식이 포함된 셀 처리 방식, 데이터 유형 등의 옵션도 제공됩니다. 예제에서는 워크시트 이름과 열 이름 조건을 끈 상태로 유지하여 특정 Sheet나 특정 열을 추가로 제한하지 않음을 나타냈고, 수식 유형은 “제한 없음”을 선택했으며, 처리할 셀 데이터 유형은 “텍스트”를 선택했습니다. 이러한 설정은 실제 파일에 맞게 조정해야 하며 기계적으로 그대로 따라 해서는 안 됩니다.

예를 들어 텍스트 번호만 처리하려면 텍스트로 우선 제한해야 합니다. 번호가 원본 통합 문서에서 숫자로 저장된 경우에는 실제 데이터 유형에 맞게 다시 판단해야 합니다. 수식 셀과 관련된 경우에는 수식 계산 결과 값과 수식 표현식을 구분하여 치환 대상을 잘못 선택하지 않도록 해야 합니다.

4단계: 수식 퍼지 찾기를 선택하고 표현식 입력하기

“키워드 옵션 설정” 영역에서 찾기 방식을 “정확한 텍스트 찾기”에서 “수식 퍼지 텍스트 찾기”로 전환합니다. 스크린샷의 찾기 목록에는 두 줄의 규칙이 입력되어 있습니다.

1. 030\d{3}: 030 뒤에 세 자리 숫자가 바로 오는 텍스트를 찾습니다. 예: 030006, 030010, 030148
2. 46\d{3}: 46 뒤에 세 자리 숫자가 바로 오는 텍스트를 찾습니다. 예: 46060

오른쪽 “치환 후 키워드 목록”에는 같은 행 번호에 맞춰 111111과 22222를 입력합니다. 즉, 첫 번째 찾기 규칙은 첫 번째 치환 내용에, 두 번째 찾기 규칙은 두 번째 치환 내용에 대응합니다.

이 단계는 전체 작업에서 가장 중요합니다. 왼쪽과 오른쪽 목록의 순서가 반드시 일치해야 하며, 그렇지 않으면 CCN 번호가 CBSA의 목표 값으로 바뀔 수 있습니다. 스크린샷에서 “문자 대소문자 무시”는 선택하지 않았으며, 이 예제는 숫자를 처리하므로 대소문자 옵션은 결과에 영향을 주지 않습니다. 규칙에 영문자가 포함된다면 업무 요구에 따라 사용 여부를 결정해야 합니다.

5단계: 저장 위치를 설정하고 처리 시작하기

규칙을 확인한 후 “다음”을 클릭하여 “저장 위치 설정”으로 들어갑니다. 검토 편의와 원본 데이터 보호를 위해 결과를 별도의 새 디렉터리에 저장하는 것이 좋으며, 백업 없이 유일한 원본을 바로 덮어쓰지 않아야 합니다. 저장 위치 설정을 마친 뒤 계속해서 “처리 시작”으로 들어가 인터페이스 안내에 따라 작업을 실행합니다.

처리가 끝난 후에는 출력 통합 문서를 최소 한 개 이상 표본 검사해야 합니다. 111111과 22222가 나타났는지 검색하고, 원래 030 또는 46 규칙에 맞지 않던 번호가 그대로 유지되었는지 확인합니다. 작업 파일 수가 많다면 파일 목록에 따라 여러 통합 문서를 표본 대조할 수도 있습니다.

4. 표현식과 치환 목록은 어떻게 대응해야 하는가

일괄 규칙은 일반적으로 줄 단위 대응 방식을 사용합니다. 첫 줄의 찾기 내용은 첫 줄의 치환 내용에, 두 번째 줄은 두 번째 줄에 대응하며, 이와 같이 이어집니다. 따라서 여러 규칙을 입력할 때는 먼저 외부 텍스트에서 “찾기 규칙—치환 결과” 매핑을 정리한 뒤 소프트웨어 화면에 복사해 넣어 순서가 어긋날 확률을 낮추는 것이 좋습니다.

\d는 숫자를 나타내고, {3}은 앞의 숫자 패턴이 연속으로 세 번 나타남을 의미합니다. 따라서 030\d{3}은 030에 세 자리 숫자가 더해진 형태와 일치합니다. 이 표현식을 단순히 “030” 세 문자만 찾는 것으로 이해해서는 안 되며, 완전한 형식을 설명하는 것입니다.

실제 번호 자릿수가 다르다면 규칙을 그에 맞게 조정하고, 먼저 소량의 테스트 파일로 검증해야 합니다. 표현식을 너무 넓게 쓰면 수정하지 말아야 할 데이터까지 일치할 수 있고, 너무 좁게 쓰면 대상 내용을 놓칠 수 있습니다.

5. 자주 묻는 질문과 주의 사항

1. 겉보기에 같은 번호인데 치환되지 않는 이유는 무엇인가

먼저 셀의 실제 데이터 유형을 확인합니다. Excel에서 숫자로 보이는 내용은 텍스트일 수도, 숫자 값일 수도, 수식 계산에서 왔을 수도 있습니다. 스크린샷의 처리 설정에서는 텍스트만 선택했으므로, 텍스트가 아닌 유형은 같은 방식으로 처리되지 않을 수 있습니다. 번호 앞뒤에 공백이 있는지, 선행 0이 Excel에서 이미 사라졌는지, 자릿수가 표현식에 맞는지도 확인해야 합니다.

2. 지정한 워크시트나 지정한 열만 처리할 수 있는가

인터페이스에서 “워크시트 이름이 다음 조건을 충족해야 함”과 “셀 열 이름이 다음 조건을 충족해야 함” 두 스위치를 볼 수 있습니다. 작업 범위를 좁혀야 한다면 페이지에서 제공하는 조건 설정에 따라 제한할 수 있으며, 조건 작성법이 익숙하지 않다면 먼저 복사한 파일에서 테스트하는 것이 좋습니다.

3. 여러 규칙이 어긋나 치환되는 이유는 무엇인가

가장 흔한 원인은 왼쪽과 오른쪽 목록의 줄 번호가 일대일로 대응하지 않는 것입니다. 규칙을 추가, 삭제, 조정한 후에는 각 줄을 다시 확인해야 합니다. 표현식 자체만 확인하지 말고 대응하는 치환 텍스트도 함께 확인해야 합니다.

4. 새 디렉터리에 저장하라고 권장하는 이유는 무엇인가

일괄 작업은 여러 파일에 영향을 주므로 규칙을 잘못 쓰면 다시 작업해야 할 범위도 커집니다. 원본 파일을 보존하고 결과를 새 디렉터리에 출력하면 처리 전후 차이를 비교하기 쉽고, 문제를 발견했을 때 규칙을 다시 구성하기도 편리합니다.

5. 정식 데이터에 바로 사용해도 되는가

대표적인 통합 문서 한두 개를 복사해 소규모 테스트를 먼저 진행하는 것이 좋습니다. 일치 수량, 치환 결과, 수식과 서식에 이상이 없는지 확인한 뒤 전체 파일을 추가합니다. 재무, 의료, 고객, 프로젝트 데이터의 경우 소속 조직의 데이터 관리 및 승인 요구 사항도 준수해야 합니다.

6. 요약

HeSoft Doc Batch Tool 의 “Excel에서 키워드 찾기 및 바꾸기” 기능을 사용하면 여러 xlsx 통합 문서를 같은 처리 작업에 넣고, 수식 퍼지 텍스트 찾기를 활용해 공통 형식을 가진 번호를 식별할 수 있습니다. 이 예제에서는 030\d{3}과 46\d{3} 두 규칙을 통해 각기 다른 번호를 111111과 22222로 치환하여 파일별, 워크시트별 반복 작업을 피했습니다.

여러 Excel 대장, 업무 보고서 또는 외부 발송 데이터를 처리 중이라면 먼저 파일을 백업하고 소량의 샘플로 표현식을 검증한 뒤 전체 통합 문서를 일괄 가져오는 것이 좋습니다. 처리 범위, 데이터 유형, 규칙 대응 관계를 올바르게 설정하면 Excel 와일드카드 일괄 치환을 더 안정적으로 완료하여 반복 작업을 줄이고 파일 처리 효율을 높일 수 있습니다.