엑셀 VBA 문자가 포함된 숫자만 합치기

일정한 위치와 형태가 주어지지 않은 문자열에서 숫자를 추출한 후 합계를 구하기 위해서는 VBA 코드를 이용해야 합니다. 일반 엑셀 함수로 계산을 하기에는 한계가 있습니다.

 

 

▼ 아래 샘플에서 알 수 있듯이 문자가 들어간 영역이 일정하지 않습니다. 해당 문자를 VBA 코드로 제거하고 숫자만 추출해서 합계를 구해 보겠습니다.

 

▼ 전체 VBA 코드에서 부분별로 설명하겠습니다. 첫 번째로 사용할 값들의 변수를 정의하고 문자가 있는 셀 영역을 Range 함수로 가져옵니다.

Dim SumRange As Range
Dim SigleStr As String
Dim SumStr As String
Dim TotalNum As Integer

Set SumRange = Range("B3:B7")
TotalNum = 0

 

▼ 아래 반복문으로 사용한 For each 는 영역에서 셀을 하나씩 조회하기 위해 사용한 것입니다.

For Each i In SumRange
    
Next i

 

▼ 반복문 내부의 첫 번째 코드는 합계한 값을 저장할 변수의 초기화와 각 셀 값을 변수에 저장하는 코드로 구성이 됩니다.

'' 변수 초기화
SumStr = ""

CalData = i.Value
Debug.Print "> 원본 글자 : " & CalData

 

▼ 다음은 셀에서 가져온 문자열을 하나씩 나눠서 숫자인지 판단합니다. 그리고 문자는 버리고 숫자만 추출합니다.

'' 문자를 하나씩 분해 한다.
For j = 0 To Len(CalData)
    SigleStr = Mid(CalData, j + 1, 1)
    If IsNumeric(SigleStr) Then
        SumStr = SumStr + SigleStr
    End If
Next j

 

▼ 추출한 숫자는 Int 함수를 이용해서 숫자 형식으로 변환합니다. 보기에 숫자지만 형식은 문자이기 때문에 + 연산을 할 수 없기 때문입니다.

'' 추출한 값 합산하기
TotalNum = TotalNum + Int(SumStr)

 

 

 

▼ 함수 전체 코드는 다음과 같습니다.

Sub Text_Cal()

    Dim SumRange As Range
    Dim SigleStr As String
    Dim SumStr As String
    Dim TotalNum As Integer
    
    Set SumRange = Range("B3:B7")
    TotalNum = 0
    
    For Each i In SumRange
        
        '' 변수 초기화
        SumStr = ""
        
        CalData = i.Value
        Debug.Print "> 원본 글자 : " & CalData
        
        '' 문자를 하나씩 분해 한다.
        For j = 0 To Len(CalData)
            SigleStr = Mid(CalData, j + 1, 1)
            If IsNumeric(SigleStr) Then
                SumStr = SumStr + SigleStr
            End If
        Next j
        
        '' 추출한 값 합산하기
        TotalNum = TotalNum + Int(SumStr)
             
    Next i
    
    Debug.Print "합계 : " & TotalNum
    
End Sub

 

▼ 문자에서 숫자를 추출한 후 계산한 결과는 콘솔에 출력했습니다.

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 VBA 숫자를 문자로 변환하는 방법 ▶ 엑셀 VAB 값 숫자 여부 판단하는 IsNumberic 함수 사용하기 ▶ 엑셀 VBA Application.Evaluate 사용해서 수식 계산하기 ▶ 엑셀 VBA – Application.Evaluate 함수 이용해서 수식문자열계산하기 ▶ 엑셀 VBA 나머지를 구하기 위한 Mod 연산자와 몫 구하는 연산자 사용법