엑셀 글자 추출 LEFT MID RIGHT: 상품코드에서 색상·사이즈 자동 분리하기

엑셀에서 상품코드 글자 추출을 검색하는 분들은 보통 이런 상황을 만납니다. 쇼핑몰 주문내역이나 물류 출고표를 받았는데, 상품코드 안에 품목, 출시월, 색상, 사이즈가 한꺼번에 붙어 있는 경우입니다.

예를 들어 TS2607BKM이라는 코드가 있을 때, 앞의 TS는 품목, 2607은 2026년 7월, BK는 검정, M은 사이즈를 뜻합니다. 눈으로 보면 대충 알겠는데, 행이 300개, 3,000개가 되면 하나씩 나눌 수 없습니다. 이럴 때 초보자가 가장 먼저 익혀두면 좋은 함수가 LEFT, MID, RIGHT입니다.

Q. 상품코드에서 앞자리, 중간 글자, 끝 글자를 어떻게 나누나요?

먼저 예제 표를 이렇게 만든다고 생각해 보겠습니다. 실제 파일에서는 주문내역 시트의 A열에 상품코드가 들어 있고, B열에는 상품명, C열에는 수량이 있다고 가정하겠습니다.

데이터는 3행에 제목이 있고, 실제 주문 데이터는 4행부터 9행까지 입력되어 있습니다. 결과는 D열부터 H열에 표시할 예정입니다.

예제 데이터는 아래처럼 입력되어 있다고 보겠습니다.

Q. LEFT 함수는 언제 쓰나요?

LEFT는 셀의 왼쪽부터 몇 글자를 가져오는 함수입니다. 상품코드에서 앞의 2글자가 품목코드라면 D4 셀에 아래 수식을 입력합니다.

=LEFT(A4,2)

이 수식의 뜻은 간단합니다. A4 셀의 왼쪽에서 2글자만 가져오라는 뜻입니다. A4에 TS2607BKM이 있다면 결과는 TS가 됩니다.

D4에 수식을 입력한 뒤, D4 셀의 오른쪽 아래 작은 네모를 잡고 D9까지 아래로 끌어내리면 나머지 행도 자동으로 계산됩니다. 이것을 보통 채우기 핸들이라고 부릅니다.

Q. MID 함수는 왜 숫자가 3개나 들어가서 헷갈리나요?

초보자가 가장 많이 헷갈리는 함수가 MID입니다. LEFT와 RIGHT는 왼쪽, 오른쪽이라 직관적인데 MID는 중간에서 뽑아야 하므로 시작 위치를 직접 알려줘야 합니다.

MID 함수는 이렇게 읽으면 됩니다.

=MID(글자를 가져올 셀, 시작 위치, 가져올 글자 수)

상품코드 TS2607BKM을 한 글자씩 번호로 보면 아래와 같습니다.

여기서 출시월 2607은 3번째 글자부터 4글자입니다. 그래서 E4 셀에는 우선 아래처럼 입력할 수 있습니다.

=MID(A4,3,4)

결과는 2607입니다. 다만 실무에서는 이 값을 그냥 2607로 두기보다 2026-07처럼 월 형태로 바꾸면 월별 집계할 때 훨씬 보기 좋습니다.

그래서 E4 셀에는 아래 수식을 추천합니다.

=TEXT(DATE(2000+MID(A4,3,2)*1,MID(A4,5,2)*1,1),"yyyy-mm")

조금 길어 보이지만 나누어 보면 어렵지 않습니다. MID(A4,3,2)는 26을 가져오고, MID(A4,5,2)는 07을 가져옵니다. DATE 함수로 2026년 7월 1일을 만든 뒤, TEXT 함수로 2026-07 모양으로 표시하는 방식입니다.

여기서 *1은 글자로 뽑힌 26, 07을 숫자로 계산되게 도와주는 역할입니다. 엑셀은 겉으로 숫자처럼 보여도 실제로는 글자로 취급할 때가 있어서, 날짜 계산에서는 이렇게 숫자로 바꿔주는 습관을 들이면 오류를 줄일 수 있습니다.

Q. 색상코드는 중간에 있는데 어떻게 뽑나요?

색상코드는 상품코드의 7번째부터 2글자입니다. 예를 들어 TS2607BKM에서 BK가 색상코드입니다.

F4 셀에 아래 수식을 입력합니다.

=MID(A4,7,2)

뜻은 A4 셀에서 7번째 글자부터 2글자를 가져오라입니다. A4가 TS2607BKM이면 F4 결과는 BK가 됩니다.

MID 함수가 헷갈릴 때는 상품코드를 한 글자씩 세어보는 것이 가장 확실합니다. 처음에는 느려 보여도, 몇 번 해보면 “아, 시작 위치와 글자 수만 정하면 되는구나” 하고 감이 잡힙니다.

Q. RIGHT 함수는 사이즈처럼 맨 끝 글자 뽑을 때 쓰나요?

맞습니다. RIGHT는 오른쪽 끝에서 몇 글자를 가져오는 함수입니다. 이 예제에서는 상품코드 맨 끝 1글자가 사이즈입니다.

G4 셀에 아래 수식을 입력합니다.

=RIGHT(A4,1)

A4가 TS2607BKM이면 결과는 M입니다. G4 수식을 G9까지 복사하면 각 상품의 사이즈가 자동으로 나옵니다.

단, 여기서 실무자가 자주 만나는 문제가 하나 있습니다. 사이즈가 S, M, L처럼 한 글자일 때는 RIGHT(A4,1)이 맞습니다. 그런데 XL, 2XL처럼 두 글자 이상인 사이즈가 섞이면 맨 끝 1글자만 가져오기 때문에 결과가 틀어집니다.

예를 들어 TS2607BKXL이라는 코드가 들어오면 RIGHT(A4,1)의 결과는 L만 나옵니다. 이런 경우에는 회사 상품코드 규칙을 먼저 확인해야 합니다. 사이즈가 항상 맨 뒤 2글자라면 RIGHT(A4,2)로 바꿔야 하고, 한 글자와 두 글자가 섞인다면 코드 규칙 자체를 다시 정리해야 합니다.

Q. BK, WH 같은 색상코드를 검정, 흰색으로 바꾸려면요?

코드만 뽑아도 되지만 보고서에는 보통 검정, 흰색, 네이비처럼 사람이 읽기 쉬운 이름이 필요합니다. 이럴 때는 작은 기준표를 만들어 놓고 VLOOKUP으로 찾으면 됩니다.

예제에서는 J3:K7 범위에 색상 기준표를 만듭니다. J열에는 색상코드, K열에는 색상명을 입력합니다.

이제 H4 셀에 아래 수식을 입력합니다.

=VLOOKUP(F4,$J$4:$K$7,2,FALSE)

이 수식은 F4에 있는 색상코드를 J4:K7 기준표의 첫 번째 열에서 찾고, 같은 행의 두 번째 열 값을 가져오라는 뜻입니다. F4가 BK라면 H4에는 검정이 표시됩니다.

여기서 꼭 봐야 하는 부분은 $J$4:$K$7입니다. 달러 표시가 붙어 있으면 수식을 아래로 복사해도 기준표 범위가 움직이지 않습니다. 초보자 파일에서 VLOOKUP 결과가 중간부터 이상해지는 이유는 이 범위를 고정하지 않아서인 경우가 많습니다.

Q. VLOOKUP에서 #N/A가 나오면 뭐부터 봐야 하나요?

#N/A는 “찾는 값이 기준표에 없다”는 뜻으로 보면 됩니다. 이 예제에서는 F열의 색상코드가 J열 기준표에 없을 때 발생합니다.

예를 들어 상품코드에 RD가 들어왔는데 J4:K7 기준표에 RD가 없다면 H열에는 #N/A가 나옵니다. 이때 수식이 틀렸다고 바로 지우지 말고, 먼저 기준표에 해당 코드가 있는지 확인해야 합니다.

보고서에서 오류 표시 대신 “코드확인”이라고 보이게 하려면 H4 수식을 아래처럼 바꿔도 좋습니다.

=IFERROR(VLOOKUP(F4,$J$4:$K$7,2,FALSE),"코드확인")

IFERROR는 앞의 수식에서 오류가 나면 뒤의 문구를 표시하는 함수입니다. 이 수식은 색상코드를 정상적으로 찾으면 색상명을 보여주고, 못 찾으면 코드확인이라고 표시합니다.

Q. 수식을 복사했는데 결과가 이상하면 어디를 확인하나요?

가장 먼저 확인할 것은 상품코드 자릿수가 일정한지입니다. 이번 예제는 상품코드가 항상 9글자라는 전제로 만들었습니다.

TS2607BKM은 9글자입니다. 그런데 중간에 TS-2607-BK-M처럼 하이픈이 들어간 코드가 섞이면 글자 위치가 달라집니다. 그러면 MID(A4,7,2)가 BK를 가져오지 못하고 엉뚱한 글자를 가져올 수 있습니다.

두 번째로 확인할 것은 앞뒤 공백입니다. 거래처나 쇼핑몰 시스템에서 내려받은 파일에는 상품코드 앞이나 뒤에 보이지 않는 공백이 붙어 있을 때가 있습니다. A4 셀을 클릭했을 때 수식 입력줄에서 커서가 한 칸 더 움직이는 느낌이 있으면 공백을 의심해 볼 수 있습니다.

세 번째는 코드 규칙 변경입니다. 예전에는 색상코드가 2글자였는데 새 상품부터 3글자로 바뀌었다면 기존 수식은 당연히 틀어집니다. 함수가 틀린 것이 아니라, 가져와야 하는 위치와 글자 수가 바뀐 것입니다.

Q. 품목코드도 이름으로 바꾸고 싶으면 같은 방식인가요?

네, 색상명과 같은 방식으로 처리하면 됩니다. 예를 들어 D열에 TS, PT, JK 같은 품목코드가 추출되어 있다면 별도의 품목 기준표를 만들고 VLOOKUP으로 품목명을 가져오면 됩니다.

예를 들어 M3:N6 범위에 아래 기준표를 만든다고 하겠습니다.

I열에 품목명을 표시하고 싶다면 I4 셀에 아래 수식을 입력합니다.

=IFERROR(VLOOKUP(D4,$M$4:$N$6,2,FALSE),"품목확인")

이렇게 해두면 상품코드에서 품목코드를 뽑고, 그 코드를 다시 품목명으로 바꾸는 흐름이 만들어집니다. 주문내역을 정리할 때 원본 코드는 유지하면서, 보고용 열을 옆에 붙이는 방식이라 실무에서 안전합니다.

Q. 전체 수식을 한 번에 정리하면 어떻게 되나요?

아래처럼 각 결과 열의 첫 번째 데이터 행인 4행에 수식을 입력한 뒤, 9행까지 복사하면 됩니다. 실제 파일에서 데이터가 1000행까지 있다면 같은 방식으로 1000행까지 복사하면 됩니다.

수식을 복사하기 전에 한 행만 먼저 맞춰보는 것이 좋습니다. A4의 상품코드를 눈으로 보고, D4부터 H4까지 결과가 예상대로 나오는지 확인한 다음 아래로 복사하면 실수를 줄일 수 있습니다.

Q. 이 기능은 어떤 업무에서 바로 쓰면 좋나요?

상품코드, 주문번호, 사번, 거래처코드처럼 일정한 규칙으로 만들어진 값에는 LEFT, MID, RIGHT가 자주 쓰입니다. 앞 몇 글자가 부서코드이고, 중간 몇 글자가 연월이고, 끝 글자가 구분값인 식의 데이터라면 거의 같은 방식으로 풀 수 있습니다.

단, 이 함수들은 위치가 고정된 코드에 특히 잘 맞습니다. “항상 앞 2글자가 품목”, “항상 3번째부터 4글자가 연월”, “항상 끝 1글자가 사이즈”처럼 규칙이 분명해야 합니다.

코드 길이가 들쭉날쭉하거나 중간에 하이픈, 공백, 추가 문자가 섞여 있다면 먼저 원본 규칙을 확인해야 합니다. 함수부터 넣기보다 샘플 5개 정도를 눈으로 보고, 몇 번째 글자부터 몇 글자를 가져와야 하는지 정하는 것이 순서입니다.

이럴 때 이렇게 쓰면 된다

왼쪽 몇 글자만 필요하면 LEFT, 중간의 정해진 위치에서 뽑아야 하면 MID, 오른쪽 끝 글자가 필요하면 RIGHT를 쓰면 됩니다.

뽑아낸 코드가 BK, WH처럼 사람이 바로 이해하기 어려운 값이라면 작은 기준표를 만들고 VLOOKUP으로 이름을 붙이면 됩니다. 오류가 보이면 IFERROR로 숨기기 전에, 기준표에 코드가 빠졌는지 먼저 확인하는 습관을 들이면 좋습니다.

상품코드가 일정한 자리수로 만들어진 파일이라면 오늘 예제처럼 A열 원본코드는 그대로 두고, 옆 열에 품목코드, 출시월, 색상, 사이즈를 나누어 붙이세요. 원본을 건드리지 않으면서 정렬, 필터, 피벗 집계까지 훨씬 편해집니다.