엑셀 문자에서 숫자 만 뽑아 내기, 추출

 

 

1. TEXTJOIN 함수: 엑셀 숫자 추출의 마법 지팡이

TEXTJOIN 함수란 무엇인가요?

TEXTJOIN 함수는 Excel 2019 이후 버전에 등장한 텍스트 결합 함수계의 슈퍼스타입니다. 기존의 CONCATENATE 함수는 저리 가라 할 정도로 강력한 기능을 자랑하죠! 여러 텍스트를 하나로 합치는 것은 기본, 구분자 설정, 빈 셀 무시 등 다양한 옵션으로 데이터 처리의 효율성을 극대화합니다. 특히 텍스트에서 숫자 추출? TEXTJOIN 함수만 있다면 문제없습니다!

=TEXTJOIN(구분자, 공백처리, 텍스트1, [텍스트2], ...)

• 구분자: 결합되는 텍스트 사이에 들어갈 문자열입니다. 숫자만 깔끔하게 추출하고 싶다면 ""(빈 문자열)을 사용하세요!
• 공백처리: TRUE로 설정하면 빈 셀은 쿨하게 무시! FALSE라면 빈 셀도 포함합니다. 단일 셀에서 숫자를 추출할 땐 TRUE/FALSE 모두 결과에 영향을 주지 않습니다.
• 텍스트1, [텍스트2], ...: 숫자를 추출할 텍스트 또는 셀 범위를 입력합니다. 마법처럼 숫자가 뿅! 하고 나타날 원천이죠!

2. TEXTJOIN 함수와 다른 함수들의 콜라보: 찰떡궁합 시너지 효과!

텍스트 맨 끝에 숫자가 붙어있다면 RIGHT 함수 하나로 간단하게 해결! 하지만 텍스트 중간에 숫자가 숨어 있거나, 텍스트와 숫자가 뒤섞인 혼돈의 카오스라면…? TEXTJOIN 함수와 MID, ROW, INDIRECT, LEN, ISNUMBER 함수의 환상적인 콜라보가 필요합니다! 어벤져스급 조합으로 어떤 텍스트도 정복 가능!

1. 복잡한 텍스트에서 숫자 추출하기

예시 데이터: A열에 "Product_Code_A123", "Order#456", "Invoice-7890", "Item001234" 와 같이 뒤죽박죽 혼합된 데이터가 있다고 가정해 봅시다. B열에 A열에서 숫자만 쏙쏙 뽑아내서 표시하고 싶다면?

마법의 함수식: B2 셀에 아래 함수식을 입력하고 아래로 드래그!

=TEXTJOIN("",TRUE,IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))

각 함수의 역할 대공개:

1. LEN(A2): A2 셀 문자열 길이 측정! "Product_Code_A123"는 16!
2. INDIRECT("1:"&LEN(A2)): LEN 함수 결과를 활용! "1:16"처럼 텍스트 문자열 생성 후, INDIRECT 함수가 셀 범위 참조로 변환!
3. ROW(INDIRECT("1:"&LEN(A2))): 1부터 문자열 길이까지 숫자 배열 {1, 2, 3, ..., 16} 생성! 마법의 시작!
4. MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1): MID 함수는 A2 셀 문자열에서 각 위치의 문자를 하나씩 추출합니다. "P", "r", ..., "3"까지 순서대로!
5. MID(...) * 1: 추출된 문자가 숫자면 숫자형으로 변환, 숫자가 아니면 오류 값 반환! 텍스트 형식 숫자를 진짜 숫자로 바꾸는 핵심 마법!
6. ISNUMBER(MID(...) * 1): 각 문자가 숫자인지 판별! 숫자면 TRUE, 아니면 FALSE!
7. IF(ISNUMBER(...),MID(...),""): ISNUMBER 결과에 따라 숫자면 해당 숫자 반환, 아니면 빈 문자열 "" 반환!
8. TEXTJOIN("",TRUE,IF(...)): IF 함수 결과값들을 빈 문자열("") 구분자로 결합! 추출된 숫자들이 하나의 문자열로 짠!

결과: B2 셀엔 "123", B3 셀엔 "456", B4 셀엔 "7890", B5 셀엔 "1234"가 짜잔! ✨

2. 뽑아진 숫자의 문자를 계산 가능한 숫자로 바꾸기

위 1에서 만들어진(뽑아진) 숫자들은 숫자로 인식 되지 않고, 문자 이기에 숫자로 바꾸어야 합니다.  2가지 방법이 있읍니다.

  > *1 또는 +0을 함수식 결과에 붙여주면 텍스트 형식 숫자가 진짜 숫자로 바꾸기!

     보기:   =TEXTJOIN(...) * 1 또는 =TEXTJOIN(...) + 0

   > VALUE 함수를 써도 같은 기능을 합니다.

      보기: =VALUE(TEXTJOIN(...))