스프레드시트 텍스트 데이터 정리 자동화 - 엑셀·구글 시트

CRM 데이터베이스에서 고객 이름 컬럼을 열면 " 김철수 ", "이영희

", "박 민준"처럼 공백과 제어 문자가 섞여 있는 경우가 흔하다. 이를 수동으로 하나씩 수정하는 대신 함수 하나로 일괄 처리할 수 있다.

핵심 답변

엑셀 텍스트 정리의 핵심 함수는 TRIM(앞뒤 공백 제거), CLEAN(인쇄 불가 문자 제거), SUBSTITUTE(특정 문자 교체), LEFT/RIGHT/MID(텍스트 일부 추출)입니다. 대량 처리에는 파워쿼리(Power Query)를 활용하고, 구글 시트에서는 Apps Script로 자동화 스크립트를 작성할 수 있습니다.

핵심 텍스트 정리 함수

조합 공식 (실무 필수)

=TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " ")))

이 공식은 일반 공백, 줄바꿈, Non-Breaking Space(웹에서 복사 시 자주 포함)를 모두 처리한다.

엑셀 파워쿼리로 대량 처리

1. [데이터] → [데이터 가져오기] → [표/범위에서]
2. 파워쿼리 편집기에서 텍스트 열 선택
3. [변환] → [형식] → [트리밍] (TRIM과 동일 효과)
4. 추가로 [값 바꾸기]로 특정 패턴 일괄 치환
5. [닫기 및 로드]로 결과를 시트에 반환

10,000행 이상 데이터도 파워쿼리는 몇 초 내에 처리한다.

구글 시트 Apps Script 자동화

javascript
function cleanTextColumn() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange("A2:A1000");
  const values = range.getValues();

  const cleaned = values.map(row => [
    row[0].toString()
      .trim()
      .replace(/s+/g, ' ')        // 연속 공백 단일화
      .replace(/[^ws가-힣]/g, '') // 한글·영문·숫자 외 제거
  ]);

  range.setValues(cleaned);
}

이 스크립트를 [확장] → [Apps Script]에 붙여넣고 실행하면 A열 전체를 일괄 정리한다. 트리거를 설정하면 새 데이터 추가 시 자동 실행도 가능하다.

실무 활용 사례

사례 1: CRM 데이터 정기 정리

고객 데이터베이스에 새 데이터가 입력될 때마다 이름, 이메일, 주소 컬럼에 공백·제어 문자가 섞이는 문제가 반복된다. Apps Script 트리거를 "편집 시 실행"으로 설정하면 새 행이 추가될 때 자동으로 TRIM·CLEAN이 적용되어 수동 정리 작업을 없앨 수 있다.

사례 2: 설문 응답 데이터 일괄 정제

Google Forms로 수집한 설문 응답은 응답자마다 앞뒤 공백이나 줄바꿈 처리가 다르다. 파워쿼리의 트리밍·정규화 기능을 적용하면 수천 개 응답을 일괄 처리해 분석 준비가 완료된 깔끔한 데이터셋을 만들 수 있다.

사례 3: 상품 데이터 마이그레이션

쇼핑몰 상품 데이터를 신규 플랫폼으로 이전할 때 상품명, 설명 컬럼에 전각 공백(U+3000), Non-Breaking Space(U+00A0) 등이 섞여 있는 경우가 많다. =TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " "))) 조합 공식으로 전처리하면 마이그레이션 오류를 크게 줄일 수 있다.

마무리

이 글에서 정리한 기준을 실제 작업에 적용할 때는 [텍스터브 글자수 세기](/tools/char-counter/) 도구로 분량을 직접 확인하면서 진행하면 더 정확하게 맞출 수 있다.