엑셀(Excel) - 문자(텍스트)와 숫자 분리하기(사용자함수)

 

 

 

 

이번에는 엑셀의 VBA(Visual Basic for Applications)를 활용해 사용자 함수를 이용해 다양한 상황에 적용해보자.

 

 

마지막 숫자 그룹만 분리하기

먼저 문자열을 한 번 보자.

모두 상품명, 단위, 가격으로 이루어진 문자열이다. 여기서 가격정보만 추출한다면 엑셀 내장 함수에서는 불가능하기 때문에 사용자함수를 직접 만들어야 한다.

 

지리산 메뚜기쌀(10kg)-32800
큐원 하얀설탕 3kg 5340원
청량초 (300g)1봉-1980
더맘스)양념깻잎4kg - 13900원
삼양)불닭볶음면5입 3980

 

 

(1) VBA에서 사용자 함수 추가

 

 

 

먼저 엑셀 리본메뉴에서 개발 도구를 클릭하여 '모듈'을 삽입해준다.

모듈이 삽입되면 코드를 입력할 수 있는 입력창이 나온다.

 

 

(2) 마지막 그룹 숫자만 분리하는 사용자 함수 코드 

먼저 정규표현식을 구성한다.

(\d+(?:\.\d+)?)(?![\d\D]*\d)

• \d+: 하나 이상의 숫자 혹은 문자와 일치 확인
• (?:\.\d+)?: 옵션으로 소수점과 그 뒤에 오는 하나 이상의 숫자와 일치 확인
• (?![\d\D]*\d): 문자열 끝에서 숫자를 발견할 때까지 뒤에 더 이상 숫자가 없음을 확인(부정형 전방 탐색)

 

그럼 이제 이 정규식을 가지고 사용자 함수를 만들어보자.

함수명은 "LASTNUM"으로 정하고 String 타입의 변수를 1개 받는 것으로 설정했다.

Function LASTNUM(strinput As String) As String '마지막 숫자찾기

    Dim strExtract As String
    Dim REX As Object
    
    Set REX = CreateObject("vbscript.regexp")

    With REX
        .Global = True
        .Pattern = "(\d+(?:\.\d+)?)(?![\d\D]*\d)"
        
        If .TEST(strinput) Then
            strExtract = .Execute(strinput)(0).SubMatches(0)
            LASTNUM = strExtract
        Else
            LASTNUM = ""
        End If
    
    End With
    
End Function

 

이 때, 함수가 동작하지 않는 예외 처리가 필요한데, 이 경우에는 간단하게 공백으로 변환했다.

 

**참고로 구글 스프레드시트(Google SpreadSheet)에서는 정규표현식을 내장함수로 제공하고 있다.

 

 

 

(3) 시트에서 사용자 함수 확인하기

 

만들어진 사용자함수를 이제 사용해보자.

하나의 셀을 변수로 잡는다.

 

채우기 핸들로 아래 행까지 내려주면 마지막 숫자 그룹만 추출된 것을 알 수 있다.

B열에는 사용자 서식을 넣어서 천 단위 콤마, 화폐 단위 등을 붙여주면 실제 업무에서도 활용할 수 있다.

 

 

첨부파일

문자_숫자분리하기_사용자함수_예시.xlsm

0.02MB

 

 

혹시 이보다 간단한 문자, 숫자 분리라면 아래 포스팅을 참고해보자.

 

2023.02.22 - [Excel] - 엑셀(Excel) - 문자(텍스트)와 숫자 분리하기(내장함수)

엑셀(Excel) - 문자(텍스트)와 숫자 분리하기(내장함수)