Excel에는 셀의 문자열에서 숫자를 추출하는 기본 제공 기능이 없습니다. 그 반대의 경우도 마찬가지입니다. 숫자 부분을 제거하고 영숫자 문자열에서 텍스트 부분을 추출합니다.
그러나 이것은 Excel 기능이나 간단한 VBA 코드를 사용하여 수행할 수 있습니다.
제가 무슨 말을 하는지 먼저 말씀드리겠습니다.
아래와 같은 데이터 세트가 있고 문자열에서 숫자를 추출하려고 한다고 가정합니다(아래와 같이).
선택하는 방법은 사용 중인 Excel 버전에 따라 달라집니다.
- 2016 이전 버전의 Excel의 경우 약간 더 긴 수식을 사용해야 합니다.
- Excel 2016의 경우 새로 도입된 TEXTJOIN 기능을 사용할 수 있습니다.
- 모든 버전의 Excel에서 사용 가능한 VBA 방법
내용
Excel의 문자열에서 숫자 추출(Excel 2016 수식)
이 수식은 새로 도입된 TEXTJOIN 함수를 사용하기 때문에 Excel 2016에서만 작동합니다.
또한 이 수식은 텍스트 문자열의 시작, 끝 또는 중간에서 숫자를 추출할 수 있습니다.
이 섹션에서 설명하는 TEXTJOIN 공식은 모든 숫자를 함께 넣습니다.예를 들어 텍스트가 "10장의 티켓은 $200"이면 결과는 10200입니다.
다음과 같은 데이터 세트가 있고 각 셀의 문자열에서 숫자를 추출하려고 한다고 가정합니다.
아래 수식은 Excel에서 문자열의 숫자 부분을 제공합니다.
=TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),""))
이것은 배열 수식이므로 " 컨트롤 + 쉬프트 + 엔터 " 대신 Enter를 사용합니다.
이 수식은 텍스트 문자열에 숫자가 없으면 공백(빈 문자열)을 반환합니다.
이 공식은 어떻게 작동합니까?
이 공식을 분석하고 작동 방식을 설명하겠습니다.
- ROW(INDIRECT(“1:”&LEN(A2))) – 수식의 이 부분은 XNUMX부터 시작하는 일련의 숫자를 제공합니다.수식의 LEN 함수는 문자열의 총 문자 수를 반환합니다.在“成本為 100 美元”的情況下，它將返回 19。因此，公式將變為 ROW(INDIRECT(“1:19”)。然後 ROW 函數將返回一系列數字 – {1;2;3 ;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
- (MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1) - 수식의 이 부분은 #VALUE의 배열을 반환합니다!문자열 기반 오류 또는 숫자.문자열의 모든 텍스트 문자는 #VALUE!오류 및 모든 값은 그대로 유지됩니다.이것은 MID 함수에 1을 곱할 때 발생합니다.
- IFERROR((MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1)*1),”) – IFERROR 함수를 사용하면 모든 #VALUE가 제거됩니다!오류, 숫자만 남습니다.이 부분의 출력은 다음과 같습니다 - {"";"";"";"";"";"";"";"";"";"";"";"";"";" ";"";"";1;0;0}
- =TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")) – TEXTJOIN 함수는 이제 단순히 문자열 문자를 결합합니다. (숫자만) 빈 문자열은 무시합니다.
전문가 팁:수식 일부의 출력을 확인하려면 셀을 선택하고 F2 키를 눌러 편집 모드로 진입한 후 출력할 수식 부분을 선택하고 F9 키를 누르면 된다.결과를 즉시 볼 수 있습니다.그런 다음 Control + Z를 누르거나 Esc를 누르는 것을 잊지 마십시오.엔터를 누르지 마십시오.
동일한 논리를 사용하여 영숫자 문자열에서 텍스트의 일부를 추출할 수도 있습니다.다음은 문자열에서 텍스트 부분을 가져오는 공식입니다.
=TEXTJOIN("",TRUE,IF(ISERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),MID(A2,ROW(INDIRECT("1:"&LEN) (A2))),1),""))
이 공식에 대한 약간의 변경은 IF 함수가 오류에 대해 MID 함수에서 얻은 배열을 확인하는 데 사용된다는 것입니다.오류이면 값을 유지하고, 그렇지 않으면 공백으로 바꿉니다.
그런 다음 TEXTJOIN을 사용하여 모든 텍스트 문자를 결합합니다.
注意: 이 공식은 유효하지만 휘발성 함수(INDIRECT 함수)를 사용합니다.즉, 거대한 데이터 세트와 함께 사용하는 경우 결과를 제공하는 데 시간이 걸릴 수 있습니다.Excel에서 이 수식을 사용하기 전에 백업을 만드는 것이 가장 좋습니다.
Excel의 문자열에서 숫자 추출(Excel 2013/2010/2007용)
Excel 2013. 2010. 또는 2007을 사용하는 경우 TEXTJOIN 수식을 사용할 수 없으므로 복잡한 수식을 사용해야 합니다.
아래와 같은 데이터 세트가 있고 각 셀의 문자열에서 모든 숫자를 추출하려고 한다고 가정합니다.
아래 공식은 다음을 수행합니다.
=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {"0","1","2","3","4","5","6","7"," 8","9"}, "")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("$1:$"&LEN(A2)))), 1))* ROW(INDIRECT("$1:$"&LEN(A2))),0), ROW(INDIRECT("$1:$"&LEN(A2))))+1,1) * 10^ROW(INDIRECT ("$1:$"&LEN(A2)))/10),"")
이 수식은 텍스트 문자열에 숫자가 없으면 공백(빈 문자열)을 반환합니다.
이것은 배열 공식이지만,필요 없음그것을 사용하려면 "Control-Shift-Enter"를 사용하십시오.이 공식에는 간단한 입력이 가능합니다.
이 공식에 대한 크레딧은 놀라운 것입니다.미스터 엑셀 포럼.
다시 말하지만, 이 수식은 위치에 관계없이 문자열의 모든 숫자를 추출합니다.예를 들어 텍스트가 "10장의 티켓은 $200"이면 결과는 10200입니다.
注意: 이 공식은 유효하지만 휘발성 함수(INDIRECT 함수)를 사용합니다.즉, 거대한 데이터 세트와 함께 사용하는 경우 결과를 제공하는 데 시간이 걸릴 수 있습니다.Excel에서 이 수식을 사용하기 전에 백업을 만드는 것이 가장 좋습니다.
VBA를 사용하여 Excel에서 텍스트와 숫자 분리
텍스트와 숫자를 자주 분리해야 하는 경우(또는 텍스트에서 숫자를 추출해야 하는 경우) VBA 방법을 사용할 수도 있습니다.
간단한 VBA 코드를 사용하여 Excel에서 사용자 정의 UDF(사용자 정의 함수)를 만든 다음 길고 복잡한 수식 대신 해당 VBA 수식을 사용하기만 하면 됩니다.
VBA에서 두 가지 수식을 만드는 방법을 보여 드리겠습니다. 하나는 숫자를 추출하고 다른 하나는 문자열에서 텍스트를 추출합니다.
Excel의 문자열에서 숫자 추출(VBA 사용)
이 부분에서는 문자열에서 숫자 부분만 가져오는 사용자 지정 함수를 만드는 방법을 보여 드리겠습니다.
다음은 이 사용자 지정 함수를 만드는 데 사용할 VBA 코드입니다.
함수 GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1)) then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = 결과 종료 함수
이 함수를 만든 다음 워크시트에서 사용하는 단계는 다음과 같습니다.
이제 워크시트에서 GetText 함수를 사용할 수 있습니다.코드 자체에서 모든 무거운 작업을 수행했으므로 =GetNumeric(A2) 공식을 사용하기만 하면 됩니다.
이렇게 하면 문자열의 숫자 부분이 즉시 제공됩니다.
이제 통합 문서에 VBA 코드가 포함되어 있으므로 .xls 또는 .xlsm 확장자로 저장해야 합니다.
이 수식을 자주 사용해야 하는 경우 개인 매크로 통합 문서에 저장할 수도 있습니다.이렇게 하면 사용하는 모든 Excel 통합 문서에서 이 사용자 지정 수식을 사용할 수 있습니다.
Excel의 문자열에서 텍스트 추출(VBA 사용)
이 부분에서는 문자열에서 텍스트 부분만 가져오는 사용자 지정 함수를 만드는 방법을 보여 드리겠습니다.
다음은 이 사용자 지정 함수를 만드는 데 사용할 VBA 코드입니다.
함수 GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) then Result = Result & Mid(CellRef, i, 1 ) 다음 i GetText = 결과 종료 기능
이 함수를 만든 다음 워크시트에서 사용하는 단계는 다음과 같습니다.
이제 워크시트에서 GetNumeric 함수를 사용할 수 있습니다.코드 자체에서 모든 무거운 작업을 수행했으므로 =GetText(A2) 수식을 사용하기만 하면 됩니다.
이렇게 하면 문자열의 숫자 부분이 즉시 제공됩니다.
이제 통합 문서에 VBA 코드가 포함되어 있으므로 .xls 또는 .xlsm 확장자로 저장해야 합니다.
이 수식을 자주 사용해야 하는 경우 개인 매크로 통합 문서에 저장할 수도 있습니다.이렇게 하면 사용하는 모든 Excel 통합 문서에서 이 사용자 지정 수식을 사용할 수 있습니다.
