제품코드에서 정보 추출하기

모델명 부여 기준이나 제품코드 생성 기준으로 다양한 길이의 복잡한 제품코드들이 생성됩니다. 분류코드들이 따라서 정리되어 있으면, 분류코드들로만 분류해서 보고서 생성이 가능합니다만, 대개 분류코드들은 옛날에 만들어져서 현재의 보고서 생성시 필요한 분류기준과는 거리가 있게 마련입니다.

때문에, 결국 제품코드에서 분류 기준들을 추출해야 하며, 이 때 필요한 노하우를 적어봅니다.

1. 구분기호 전 후로 나누기

1) 엑셀 데이터 기능 사용

엑셀 상단 메뉴 중 '데이터'를 보면 중간쯤에 '텍스트 나누기' 기능이 있습니다. 엑셀에서 .xls나 .xlsx 이외의 파일을 열때 나타나는 텍스트 나누기 기능을 시트내 '열'에 대해 적용할 수 있습니다. A열을 '텍스트 나누기' 하면 A, B, C열에 순차적으로 들어가므로, 뒤 열을 비우고 실행하세요. 구분 기호('-' 등) 갯수만큼 자동으로 나눠집니다.

2) 엑셀 함수 사용

나눈 데이타를 A열에 준비합니다.

B열에 find() 함수로 구분기호 ("-" 등)를 찾아 그 위치를 기록합니다.

C열에 left(A2,B2-1)로 구분기호 좌측 문자를 기록합니다.

D열에 right(A2,len(A2)-B2)으로 구분기호 우측 문자를 기록합니다.

구분기호가 여러개이면, 최대 가지수를 고려해서 위 작업을 반복합니다.

2. 숫자와 문자 구분하기

1) 숫자&문자 구성에서 숫자와 문자로 분리하기

  A2셀에 숫자-문자 혼합 데이타가 들어있다면, 아래 수식을 B2셀에 넣어서, 좌측에서부터 알파벳 중에 처음 나오는 문자의 위치(자리)를 표시합니다.

=MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))

A2셀의 데이타 중 B2셀 값 위치의 좌측은 숫자, 우측은 문자가 됩니다.

2) 문자&숫자 구성에서 문자와 숫자로 분리하기

A2셀에 문자-숫자 혼합 데이타가 들어있다면, 아래 수식을 B2셀에 넣어서, 왼쪽에서 처음 나오는 숫자 위치를 표시하게 합니다.

=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))

A2셀의 데이타 중 B2셀 값 위치의 좌측은 문자, 우측은 숫자가 됩니다.

만약, 숫자&문자&숫자 구성이거나, 문자&숫자&문자 구성이라면, 1단계로 숫자, 문자&숫자나, 문자, 숫자&문자로 나눈 후, 나머지에 대해서 위 내용을 적용하면 됩니다.

3. 여러번 나오는 문자 중 마지막 위치 알아내서 제거하기

제 경우에는 NTSC, PAL을 구분하는 코드 N 또는 P를 제외한 코드를 만들어야 합니다. substitute()함수를 쓸 수 있으면 간단한데, 제품코드 중간에 N 또는 P가 중복해서 들어갈 수 있어서, 복잡한 해결책이 필요합니다. 다행히 N,P 코드는 제품코드의 맨 뒤에 나오는 구조라서, 가장 마지막의 N이나 P만 제외하면 됩니다.

"N" 또는 "P" 제외하기

먼저, A2셀의 값에서 "N"이 몇개인지 확인하는 수식을 B2셀에 입력합니다.

원래 코드와 "N"이 모두 제거된 코드의 길이를 비교해서 "N"의 개수를 알아냅니다.

=LEN(A2)-LEN(SUBSTITUTE(A2,"N",""))

A2셀에 있는 "N"중 B2셀에 있는 순서의 값만 특수문자(여기서는 "?")로 대체하여, 그 위치를 찾는 다음 수식을 C2셀에 입력합니다. substitute()함수의 마지막 옵션이 해당 순서에 찾아지는 문자만 바꿔라 나는 뜻입니다. 예를 들어 B2값이 2이면, 두번째 "N"만 "?"로 바꿉니다. "?"로 바꾸는 이유는 제품 코드에서 절대 사용하지 않는 문자로 바꾸어 그 위치를 검색하기 위함입니다.

=FIND("?",SUBSTITUTE(A2,"N","?",B2))

이제, A2셀의 값에서 "P"가 몇개인지 확인하는 수식을 D2셀에 입력합니다.

=LEN(A2)-LEN(SUBSTITUTE(A2,"P",""))

A2셀에 있는 "P"중 B2셀에 있는 순서의 값만 특수문자(여기서는 "?")로 대체하여, 그 위치를 찾는 다음 수식을 E2셀에 입력합니다.

=FIND("?",SUBSTITUTE(A2,"P","?",D2))

이제, "N"과 "P"중 어느것이 마지막에 나오는지 알 수 있으므로, F2셀에 다음과 같이 입력하여 마지막 "N" 또는 "P"를 제외한 코드를 확보합니다.

=IF(C2<E2,SUBSTITUTE(A2,"P","",D2),SUBSTITUTE(A2,"N","",B2))

A2에 있을 제품코드에 "N"이나 "P"가 없는 경우에는 에러가 발생하므로, iferror() 함수로 적절히 둘러싸주세요.

   

- 계속 추가됩니다 -