엑셀 텍스트에서 숫자만 추출하는 초간단 방법 (TEXTJOIN 함수 활용)

 

 

데이터 분석의 핵심, 바로 텍스트에서 숫자만 쏙쏙! 뽑아내는 기술! 엑셀에서 텍스트와 숫자가 뒤섞인 데이터를 분석할 때, 필요한 숫자만 추출하는 것은 매우 중요합니다. TEXTJOIN 함수를 필두로 다양한 함수들을 조합하여 마법처럼 숫자만 뽑아내는 방법을 알려드립니다. 복잡한 수식? 걱정 마세요! 단계별 설명과 실용적인 예시를 통해 누구나 쉽게 따라 할 수 있습니다. 지금 바로 엑셀 마스터로 레벨업! ✨

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 함수의 환상적인 콜라보가 필요합니다! 어벤져스급 조합으로 어떤 텍스트도 정복 가능!

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

예시 데이터: 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"가 짜잔! ✨

추가 꿀팁: 숫자 형태 유지하기

*1 또는 +0을 함수식 결과에 붙여주면 텍스트 형식 숫자가 진짜 숫자로 변신! (ex: =TEXTJOIN(...) * 1 또는 =TEXTJOIN(...) + 0) VALUE 함수(=VALUE(TEXTJOIN(...)))를 사용해도 같은 효과!

3. 엑셀 숫자 추출, 실무 활용: 데이터 분석 능력 UP!

TEXTJOIN 함수는 텍스트 데이터 처리에 없어서는 안 될 필수템! 특히 위에서 소개한 숫자 추출 기술은 실무에서 정말 다양하게 활용될 수 있습니다. 복잡한 텍스트 데이터에서 원하는 정보만 쏙쏙 뽑아내 분석하는 능력, 데이터 기반 의사결정 시대에 필수죠! TEXTJOIN 함수 마스터하고 데이터 분석 전문가로 거듭나세요! 궁금한 점이나 더 알고 싶은 내용이 있다면 언제든 댓글 남겨주세요! ??

다양한 시나리오별 숫자 추출 전략

• 특정 문자 앞뒤 숫자 추출: 만약 특정 문자(예: "_") 앞 또는 뒤의 숫자만 추출해야 한다면, FIND 함수를 활용하여 해당 문자의 위치를 찾고, LEFT, RIGHT, MID 함수와 조합하여 원하는 숫자를 추출할 수 있습니다. 예를 들어, "ABC_123_DEF"에서 "_" 앞의 숫자를 추출하려면 =MID(A1,FIND("_",A1)+1,LEN(A1)-FIND("_",A1)) 와 같은 수식을 사용할 수 있습니다.
• 여러 개의 숫자 분리 추출: 텍스트에 여러 개의 숫자가 포함되어 있고 각각을 분리하여 추출해야 하는 경우, FIND 함수와 함께 LEN, SUBSTITUTE 함수를 조합하여 각 숫자의 시작과 끝 위치를 정확하게 파악하고 추출하는 방법을 사용할 수 있습니다. 예를 들어, "ABC123DEF456GHI"에서 "123"과 "456"을 각각 다른 셀에 추출해야 한다면, 첫 번째 숫자는 =MID(A1,FIND("1",SUBSTITUTE(A1&" "," "," ",1)),3), 두 번째 숫자는 =MID(A1,FIND("4",SUBSTITUTE(A1&" "," "," ",1)),3) 와 같은 수식을 활용할 수 있습니다. (단, 이 방법은 숫자의 자릿수가 고정되어 있다는 전제하에 사용 가능합니다.)
• 불규칙적인 패턴 처리: 숫자의 위치나 형식이 불규칙적인 경우, VBA (Visual Basic for Applications)를 사용하여 매크로를 작성하는 것이 효율적일 수 있습니다. VBA를 사용하면 복잡한 조건과 패턴에도 유연하게 대처하여 원하는 숫자를 정확하게 추출할 수 있습니다.

TEXTJOIN 함수의 한계와 대안

• 엑셀 버전 호환성: TEXTJOIN 함수는 Excel 2019 이상 버전에서만 사용 가능합니다. 이전 버전에서는 CONCATENATE 함수와 배열 수식을 조합하여 유사한 기능을 구현할 수 있지만, 수식이 복잡해지고 계산 속도가 느려질 수 있습니다.
• 대량 데이터 처리: TEXTJOIN 함수는 대량의 데이터를 처리할 때 성능 저하가 발생할 수 있습니다. 이 경우, 파워 쿼리(Power Query) 또는 파워 피벗(Power Pivot)과 같은 고급 기능을 활용하여 데이터를 효율적으로 처리하는 것이 좋습니다. 파워 쿼리는 데이터 변환 및 정제에 특화된 기능으로, 텍스트에서 숫자를 추출하는 작업을 효율적으로 수행할 수 있습니다. 파워 피벗은 대량 데이터 분석에 최적화된 기능으로, TEXTJOIN 함수보다 빠르게 숫자 추출 및 분석 작업을 수행할 수 있습니다.

4. 마무리: 데이터 분석의 새로운 지평을 열다

TEXTJOIN 함수는 강력한 기능과 다양한 활용 가능성을 제공하는 엑셀 데이터 처리의 필수 도구입니다. 특히 숫자 추출 기능은 실무에서 빈번하게 발생하는 데이터 정제 및 분석 작업에 매우 유용합니다. TEXTJOIN 함수를 마스터하고 데이터 기반 시대를 이끌어갈 전문가로 발돋움하세요! ⭐️ 궁금한 점이나 의견, 추가적인 팁 공유는 언제나 환영합니다! 댓글로 자유롭게 소통해주세요! ??