최초 작성일: 1999-12-22
최종 수정일: 2026-09-29
조회수: 29 회
작성자: 권현욱 (엑셀러)
강의 제목: 문자열 함수 응용 예제

들어가기 전에

오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.

인사발령에 따른 격려의 메일/전화를 주신 분들께 이자리를 빌어 감사의 말씀을 드립니다.

어디서든 늘 최선을 다하도록 하겠습니다.

비가 오든 눈이 오든(심지어는 이렇게 딴 부서로 인사발령이 나도…) 엑셀이 있고 컴퓨터가 있는 한 강좌는 계속될 것입니다. 열심히들 하시기를…

질문 하나

엑셀로 클레임 자료를 관리하고 있는데 어려운 점에 봉착했습니다.

1-7월까지 자료는(신SAS 이전 자료) 제품코드 번호가 OOOOO OOOO로 표기되어 있고 그이후 자료는 OOOOOOOOO로 표기되어 있는 관계로 매크로로 자동 집계처리 함에 있어 동일 제품으로 인식이 되지 않아 중복처리되는 문제가 발생했습니다.

1. 동일코드로 인식할 수 있는 방법

2. OOOOOOOOO을 OOOOO OOOO으로 수천개의 데이터를 일괄 변경할 수 있는 방법(데이터 수가 많아 SPACE BAR로 개별적으로 처리하기에는 시간이 과다 소요)

3. OOOOO OOOO을 OOOOOOOOO로 데이터를 일괄 변경할 수 있는 방법

위 3가지 방법을 모두 가르쳐 주시면 더욱 좋고 아니면 한가지 방법이라도 가르쳐 주십시오.

감사합니다.

저자: 권현욱(엑셀러), 아이엑셀러 대표

필자는 Excel 컨설턴트, 작가, 그리고 크리에이터입니다. 현재 Microsoft Excel MVP이며, 『챗GPT+엑셀 업무자동화 정석』을 비롯한 10여 권의 도서를 집필했습니다. Excel 자동화 및 생산성 향상 분야에서 25년 넘는 경력을 보유하고 있습니다.

권현욱(엑셀러) 님의 최신 포스트:

최신 글을 불러오는 중...

문자열 함수 응용 예제

핵심 요약

조회 대상의 제품코드 형식이 서로 다르면 먼저 코드 형식을 통일해야 합니다.
하이픈을 없앨 때는 찾기/바꾸기를 이용하면 많은 데이터를 한 번에 정리할 수 있습니다.
반대로 하이픈을 추가할 때는 LEFT와 RIGHT 함수를 조합해 원하는 위치에 문자를 삽입할 수 있습니다.

그동안 가르쳐드린 문자열 함수를 활용한다면 충분히 해결하실 수 있을 것 같은데…

까짓거 복습하는 셈치고 다시 설명드리지요.

아래와 같은 자료가 있다고 가정하고…(휴~~ 자료만들기가 젤로 힘듭니다)

참고: 원문에서 나란히 놓여 있던 두 자료를 위아래 두 개의 표로 나누었습니다.

<자료1>

제품코드	1월판매수량
11097-2030	100
11097-2031	130
11113-0075	200
11065-0011	80
11065-0021	65
11097-3035	43
11113-0101	22
11113-1076	78

<자료2>

제품코드	2월판매수량
110973035	130
111130101	110
111131076	90
110972030	68
110972031	42
111130075	110
110650011	48
110650021	300

위에서 처럼 <자료1>에는 1월판매수량이 입력되어 있고, <자료2>에는 2월판매수량 자료가 있다고 가정을 합니다. 이 두개의 자료를 한 개의 파일(또는 시트)로 합친다고 할 경우 어떻게 해야 할까요?

그렇습니다! Vlookup()함수를 쓰면 되겠지요? 그런데 이 상태로는 올바른 결과값을 얻을수가 없겠지요?

<자료1>에서는 제품코드가 OOOOO-OOOO 형태로 되어있고 <자료2>에서는 OOOOOOOOO과 같은 형태로 되어 있기 때문이지요.

따라서 두 자료의 제품코드 포맷을 하나로 통일을 시켜준 후에야 vlookup() 함수를 써도 되겠지요?

실제 현업에서도 이런 경우가 비일비재 합니다. 입력하는 사람마다(또는 회사의 Server에서 자료를 다운 받을 때에도 마찬가지…) 입력포맷이 서로 다르기 때문이지요. 어쨌거나 저같으면 <자료1>의 제품코드에 있는 "-"를 없앤뒤 작업을 하겠지만(밑에서 설명드리겠지만 그게 훨씬 간편하니까요)

방법1 <자료1>에 있는 "-"을 없애는 방법

(1) 요 부분을 누릅니다. 그러면 열전체가 선택되지요?

아이엑셀러

참고: 편집 - 바꾸기 메뉴는 구버전 기준입니다. 최신 Excel에서는 홈 탭의 찾기 및 선택 - 바꾸기를 선택하거나 Ctrl + H 키를 누릅니다.

(2) "편집-바꾸기" 메뉴를 선택합니다.

아이엑셀러

(3) 그러면 "바꾸기" 대화상자가 나타나는데 "찾을 내용" 부분에는 "-"을(인용부호 빼고), "바꿀 내용" 부분에는 아무 것도 입력하지 않습니다. 그리고 "전자/반자 구분" 항목의 체크 표시를 없앤 후 "모두 바꾸기"를 선택합니다.

(4) 어떻습니까? 해당 열전체의 자료 중에 포함되어 있는 - 표시가 한번에 없어지지요?

이런 기능을 몰라서 몇 천개나 되는 레코드를 하나하나 수정한다면 얼마나 속상하는 일이겠습니까?

방법2 <자료2>에 "-"을 첨가하는 방법

이 방법은 귀에, 아니 눈에 못이 박히도록(?) 배운 문자열 함수를 활용하면 해결이 되겠지요?

이 부분에 대한 해설은 생략합니다. 문자열 함수의 상세한 설명은 이전 강좌파일을 찾아 보세요.

공식 부분을 보면 이해하실 수 있을 것입니다.

제품코드	제품코드2	공식
110973035	11097-3035	=LEFT(B98,5) & "-" & RIGHT(B98,4)
111130101	11113-0101	=LEFT(B99,5) & "-" & RIGHT(B99,4)
111131076	11113-1076	=LEFT(B100,5) & "-" & RIGHT(B100,4)
110972030	11097-2030	=LEFT(B101,5) & "-" & RIGHT(B101,4)
110972031	11097-2031	=LEFT(B102,5) & "-" & RIGHT(B102,4)
111130075	11113-0075	=LEFT(B103,5) & "-" & RIGHT(B103,4)
110650011	11065-0011	=LEFT(B104,5) & "-" & RIGHT(B104,4)
110650021	11065-0021	=LEFT(B105,5) & "-" & RIGHT(B105,4)

참고: 위 표의 공식에 쓰인 B98~B105는 원본 워크시트 기준 셀 주소이므로, 직접 적용하실 때에는 자신의 자료가 있는 셀 주소로 바꿔 입력하시기 바랍니다.

제 경우에는 <방법2>보다 <방법1>을 사용하고 있습니다.

다음 시간에…

정리 — 문자열 함수 응용 예제

구분	내용
문제	제품코드가 OOOOO-OOOO 형태와 OOOOOOOOO 형태로 섞여 있어 조회가 되지 않음
원칙	VLOOKUP을 쓰기 전에 두 자료의 제품코드 포맷을 통일
방법1	찾기/바꾸기로 하이픈(-)을 일괄 제거
방법2	LEFT와 RIGHT 함수로 하이픈을 삽입
공식	`=LEFT(B98,5) & "-" & RIGHT(B98,4)`
권장	작성자는 방법1을 사용