Excel에서 혼합된 영숫자 텍스트 문자열에서 숫자를 추출하려면 어떻게 해야 하나요?

이 튜토리얼에서는 Excel에서 혼합된 영숫자 텍스트 문자열에서 숫자를 추출하는 데 도움이 되는 세 가지 방법을 제공합니다.

수식을 사용하여 혼합된 영숫자 텍스트 문자열에서 숫자 추출하기
놀라운 도구를 사용하여 혼합된 영숫자 텍스트 문자열에서 숫자를 쉽게 추출하기
VBA 코드를 사용하여 혼합된 영숫자 텍스트 문자열에서 숫자 추출하기

수식을 사용하여 혼합된 영숫자 텍스트 문자열에서 숫자 추출하기

다음 수식을 적용하여 Excel의 텍스트 문자열 어디에서든 숫자를 추출할 수 있습니다.

1. 추출된 숫자를 출력할 빈 셀을 선택하고, 아래 수식을 입력한 다음 Enter 키를 누릅니다. 그런 다음 자동 채우기 핸들을 아래로 드래그하여 수식을 다른 셀에 적용합니다.

=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),"")

참고: 수식에서 A1은 숫자를 추출하려는 텍스트 문자열이 포함된 셀입니다. 필요에 따라 변경하십시오.

위 스크린샷에 표시된 대로 지정된 셀의 텍스트 문자열 어디서나 숫자가 추출됩니다.

Kutools for Excel로 혼합된 영숫자 텍스트 문자열에서 숫자를 쉽게 추출하기

이 섹션에서는 Kutools for Excel의 텍스트 추출 유틸리티를 추천합니다. 이 유틸리티를 사용하면 텍스트 문자열에서 숫자를 추출하는 것이 쉬워집니다.

1. Kutools > Text > Extract Text를 클릭하여 기능을 활성화합니다.

2. Extract Text 대화 상자에서 아래 설정을 수행하십시오.

2.1) Extract by location 탭을 클릭합니다 (이 탭은 기본적으로 활성화되어 있음);

2.2) 범위 섹션에서 버튼을 클릭하여 텍스트를 추출할 셀 범위를 선택합니다;

2.3) 옵션 섹션에서: 숫자 추출 옵션을 선택합니다;

2.4) 확인을 클릭합니다.

참고: 대화 상자의 왼쪽 하단에 있는 수식으로 삽입 확인란을 체크하면 결과가 수식으로 결과 셀에 삽입됩니다. 참조된 셀 값이 변경되면 결과도 자동으로 업데이트됩니다.

3. Extract Text 대화 상자에서 추출된 텍스트를 출력할 빈 셀을 선택하고 확인을 클릭합니다.

그러면 텍스트 문자열의 모든 위치에서 숫자가 추출됩니다. 스크린샷 보기:

VBA 코드로 혼합된 영숫자 텍스트 문자열에서 숫자만 유지하기

다음 VBA 코드는 지정된 범위에서 모든 비숫자 문자를 제거하고 셀에 숫자만 남게 하는 데 도움이 됩니다. 아래 단계를 따르십시오.

참고: 코드가 원본 범위를 직접 실행하므로 데이터 손실을 방지하기 위해 원본 범위의 사본을 보관하십시오.

1. Alt + F11 키를 동시에 눌러 Microsoft Visual Basic for Applications 창을 엽니다.

2. Microsoft Visual Basic for Applications 창에서 Insert > Module을 클릭합니다. 그런 다음 아래 VBA 코드를 Module 창에 복사하여 붙여넣습니다.

VBA 코드: 셀에서 모든 비숫자 문자 제거하기

Sub GetNumbers()
'Updated by Extendoffice 20210125
    Dim xRegEx As Object
    Dim xRg As Range
    Dim xCell As Range
    Dim xTxt As String
    On Error Resume Next
    xTxt = ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Pease select range:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Set xRegEx = CreateObject("VBScript.RegExp")
    With xRegEx
        .Pattern = "\D+"
        .IgnoreCase = True
        .Global = True
    End With
    xRg.NumberFormat = "@"
    For Each xCell In xRg
        xCell.Value = xRegEx.Replace(xCell.Value, "")
    Next
    Set xRegEx = Nothing
End Sub

3. F5 키를 눌러 코드를 실행합니다. 그러면 Kutools for Excel 대화 상자가 나타나며, 숫자만 유지하려는 혼합된 영숫자 텍스트 문자열이 포함된 범위를 선택하고 확인 버튼을 클릭합니다.

이제 선택한 범위의 모든 비숫자 문자가 즉시 제거되고 숫자만 남게 됩니다. 스크린샷 보기: