엑셀에서 특정 텍스트 기준으로 셀 내용 자동 분리하기: 한 줄 데이터도, 여러 블록 데이터도 한 번에 정리

데이터 설계 없이 데이터를 관리하다 보면, 한 셀에 정보가 길게 섞여 들어오는 경우가 많습니다.

예를 들어 충전기 설치 데이터나 설비 목록과 같이,  지상 벽부형충전기 AC단상 5핀 완속 3 나이스차저 같은 값은 얼핏 보기엔 단순하지만, 실제로는 “설치 형태 + 커넥터/전원 + 속도(완속/급속) + 수량 + 사업자”가 한 문장에 붙어 있습니다. 더 까다로운 건 한 셀에 여러 블록이 연달아 들어오는 경우죠. … 완속 13 입주자대표회의 … 완속 11 입주자대표회의 … 급속 6 입주자대표회의처럼 섞여 있으면, 텍스트 나누기를 한 번에 끝내기 어렵습니다.

이럴 때 가장 실무적으로 강력한 방법이 “완속/급속”을 기준점으로 삼는 겁니다. 이유는 간단합니다. 설치 문구는 매번 바뀌어도, 속도 키워드인 “완속/급속”은 대부분 고정으로 등장합니다. 즉, 변동이 큰 문장 속에서 변하지 않는 기준점을 하나 잡는 전략입니다.

1) 단일 문장(대부분의 행): 4칸으로 깔끔하게 분리

목표는 아래처럼 나누는 것입니다.

• 원문: 지상 벽부형충전기 AC단상 5핀 완속 3 나이스차저
• 결과: 지상 벽부형충전기 AC단상 5핀 / 완속 / 3 / 나이스차저

이 방식은 엑셀 365의 동적배열 함수로 구현하면 편합니다. 핵심 흐름은 “완속 또는 급속”의 위치를 먼저 찾고, 그 이전은 “앞부분”, 이후는 공백 기준으로 토큰화해서 완속(급속), 수량, 업체로 나누는 구조입니다. 특히 업체명이 “주 에버온”처럼 여러 단어인 경우도 고려해서, 두 번째 토큰(수량) 이후는 전부 합쳐 업체명으로 만들면 실무에서 덜 깨집니다.

출처 - 네이버 지식인 문의 (엑셀 실습파일)

 

아래 수식을 B3에 넣으면 B:E로 자동 펼쳐집니다.

=LET(
 t, TRIM(A3),
 pW, IFERROR(SEARCH("완속", t), 10^9),
 pF, IFERROR(SEARCH("급속", t), 10^9),
 p, MIN(pW, pF),
 IF(p=10^9, "",
   LET(
     pre, TRIM(LEFT(t, p-1)),
     post, TRIM(MID(t, p, 999)),
     tok, TEXTSPLIT(post, " "),
     HSTACK(
       pre,
       INDEX(tok, 1),
       IFERROR(INDEX(tok, 2), ""),
       IFERROR(TEXTJOIN(" ", TRUE, DROP(tok, , 2)), "")
     )
   )
 )
)

 

✅ 결과 예시
A3 = 지상 벽부형충전기 AC단상 5핀 완속 3 나이스차저
→ 지상 벽부형충전기 AC단상 5핀 | 완속 | 3 | 나이스차저

2) 팁: 수식 복붙이 싫다면 LAMBDA로 ‘내 함수’ 만들기

정리 작업은 한 번 하고 끝나는 게 아니라 반복됩니다. 매번 긴 수식을 붙여넣기 싫다면, 엑셀의 이름 관리자에서 LAMBDA로 등록해두면 “내가 만든 함수”처럼 쓸 수 있습니다. 예를 들어 =CHG_SPLIT4(A2) 같은 형태로 바로 호출할 수 있어, 현업 파일 유지보수에 훨씬 유리합니다.

 

매번 긴 수식 붙여넣기 싫다면, 이름 관리자에서 LAMBDA로 등록하세요.

1. 수식 탭 → 이름 관리자 → 새로 만들기
2. 이름: CHG_SPLIT4
3. 참조 대상(수식)에 아래 입력 → 확인

=LAMBDA(x,
 LET(
  t, TRIM(x),
  pW, IFERROR(SEARCH("완속", t), 10^9),
  pF, IFERROR(SEARCH("급속", t), 10^9),
  p, MIN(pW, pF),
  IF(p=10^9, "",
    LET(pre, TRIM(LEFT(t, p-1)),
        post, TRIM(MID(t, p, 999)),
        tok, TEXTSPLIT(post, " "),
        HSTACK(pre, INDEX(tok,1), IFERROR(INDEX(tok,2),""), IFERROR(TEXTJOIN(" ",TRUE,DROP(tok,,2)),""))
    )
  )
 )
)

 

이후 B3에 이렇게만 쓰면 됩니다:
=CHG_SPLIT4(A3)

(복수 블록 추출도 동일하게 CHG_SPEEDLIST 같은 이름으로 등록하면 끝)

3) 확장 아이디어 (현업 데이터 정리에서 성능/정확도 올리는 팁 10+)

 

1. 업체명 사전 테이블(나이스차저/한국전력/파킹클라우드/휴맥스이브이…)을 만들어, 추출된 “업체”를 표준명으로 매핑
2. 직접입력, AC단상, AC3상, 5핀, 7핀 같은 키워드도 동일 방식으로 별도 컬럼 추출해 정규화(정형화)
3. “수량”이 숫자가 아닌 경우(공란/문자 포함) 대비해 IF(ISNUMBER(--num),num,"") 형태로 데이터 품질 체크
4. 복수 블록 결과를 한 셀에 합치지 말고, TOCOL(cleaned,1)로 세로로 펼쳐 피벗/집계가 쉬운 구조로 만들기
5. 블록 경계가 지상/지하 외에 더 있다면(예: 옥외/옥내/지하1층) 경계 키워드 리스트를 확장
6. 아예 Power Query로 “완속/급속 기준 분해 → 행으로 확장” 파이프라인 구축(대량 데이터에 유리)
7. 입력 데이터에 이상 공백(두 칸 이상)이 많다면 TRIM 전에 SUBSTITUTE(x,CHAR(160)," ")로 NBSP 제거
8. 같은 셀에 “완속/급속”이 없으면 빈 값 처리(현재 수식 포함)로 오류 전파 방지
9. 추출 결과(업체/수량)로 중복 설치/중복 업체 체크 로직(COUNTIFS)까지 연결
10. 최종적으로는 “단지/구역/설치유형/속도/수량/사업자”를 분리해 데이터 모델(Power BI)로 바로 투입
11. (추가) 셀 안에 “완속/급속”이 붙어있는 케이스(공백 없음) 대비해 SUBSTITUTE 앞뒤에 공백 보정 로직 추가
12. (추가) 업체명이 2단어 이상(예: 주 에버온)인 케이스는 4분리 수식이 이미 지원하지만, 표준화 테이블로 최종 정리 권장

 

텍스트를 분리해두면 끝이 아니라, 여기서부터가 가치입니다. “업체명 표준화 테이블”을 만들어 나이스차저/한국전력/파킹클라우드/휴맥스이브이 같은 표기를 하나로 통일하면, COUNTIFS나 피벗에서 중복이 사라지고 보고가 빨라집니다. 수량이 숫자가 아닌 경우를 잡아내는 품질 체크 컬럼(숫자 변환 여부)까지 붙이면 데이터 신뢰도도 올라갑니다. 결과적으로 “속도별 설치대수”, “운영사별 물량”, “지상/지하 설치 비율” 같은 지표를 Power BI로도 바로 넘길 수 있습니다.

 

데이터가 지저분할수록, ‘변하지 않는 기준점’을 잡아 쪼개는 게 가장 강한 전략입니다. 이번 케이스에선 그 기준점이 “완속/급속”이었고, 그 하나만 잘 잡아도 분리·요약·집계까지 매끄럽게 이어집니다.

텍스트나누기.xlsx

0.02MB