[VBA] 특정 문자 앞 텍스트,문자열 추출 Left, Right, InStr, InStrRev, Split 함수 '5' 런타임 오류 프로시저 호출 또는 인수가 잘못되었습니다.

오늘 주제는 여러 셀에 걸쳐 있는 다중 문자열에서 특정 문자 앞, 뒤의 텍스트, 문자열 추출을 어떻게 처리해야 되는지에 대한 것입니다. VBA에서 특정 문자 또는 기호 이전 이후의 문자열을 추출하는 방식은 Left, Right, Split 함수 등이 있습니다. 특정 문자 또는 기호가 문자열의 어디에 위치하는지를 확인해야 되는데, 이를 확인하기 위한 함수가 InStr, InStrRev이며, 이 두 함수들은 서로 함께 사용됩니다.

1. 방법

아래 방법은 VBA에서 특정 문자(괄호 개방문자, “(“)를 인식하여 해당 문자 왼쪽의 텍스트/문자열을 추출하는 방법입니다.

Left("문자열", InStr(문자열, "(") - 1)

예를 들어, “사과(대)”라는 문자열이 있다고 했을 때, 이 문자열을 “사과”로 바꾸고 싶다면 위의 문자열 부분에 “사과(대)”를 삽입하여 코드를 실행하면 “사과(대)”가 “사과”로 추출되는 것을 의미합니다.

2. 다른 해결방법

VBA로 처리하지 않을 경우, 엑셀 내장 함수인 Mod, Find, Len, Left 함수 등을 사용해도 결과값은 동일하게 처리할 수 있지만 이 함수들이 시트 내에서 계속 연산처리를 하게 되면 시트가 느려지고, 무거워지는 원인이 될 수 있습니다.

3. 실질적 환경

위에서 설명한 Left, InStr를 이용한 텍스트/문자열 추출 방식은 아래와 같이 단일 셀 문자열 처리 과정에서 문제없이 동작합니다.

Sub text_extract()

Cells(2, 2).Value = Left(Cells(2, 1).Value, InStr(Cells(2, 1).Value, "(") - 1)

End Sub

하지만 우리가 VBA를 통해서 현실적으로 처리하고자 하는 환경은 다음과 같이 여러 문자열 중 “(“가 포함된 텍스트를 인식해서 “(” 뒤의 문자열을 제거하여 문자열을 추출하는 방식일 것입니다.

아래와 같이 문자열을 추출하는 것이 최종 목표가 될 것입니다.

4. 주의사항

이를 처리하기 위한 방법은 A열에 순환문을 적용하여 A2부터 A10까지 셀을 반복하여 구문을 처리하면 될 거라는 생각은 쉽게 하실 겁니다.

문제는 InStr 함수가 문자열을 탐색하는 과정에서 위의 예시처럼 “(“가 포함된 문자열을 찾아서 “(” 문자 이전의 텍스트를 추출하는 것을 알아서 잘 처리해주지 못한다는 것입니다.

예를 들어, 특정 문자에 “(“가 포함되지 않았는데, 위의 코드를 실행할 경우 다음과 같은 오류(‘5’ 런타임 오류가 발생하였습니다: 프로시저 호출 또는 인수가 잘못되었습니다.)가 발생하며, 코드 실행이 중단됩니다.

VBA가 편리하고 쉬워서 좋지만 이렇게 막연한 버그 메시지를 보여주는 것은 참 답답할 수 밖에 없는 것 같습니다.

이러한 오류의 가장 큰 원인은 InStr 함수가 문자열을 탐색하는 과정에서 문자열에 찾고자 하는 문자 “(“가 없을 경우 발생하게 됩니다.

순환문을 반복하는 과정에서 InStr 함수가 특정 문자열(예: “사과(대)”가 아니라 “사과”)을 찾아봤더니 “(“가 없는데, 여기에 -1을 한 결과값으로 반환하려고 하니 반환할 값이 없다고 하면서 오류를 발생시키는 것입니다.

아마 이정도에서 이미 눈치를 채신 분들은 어떻게 처리를 하면될지 이미 눈치채셨을지도 모르겠습니다.

방법은 문자열을 탐색하는 과정에서 “(“가 포함된 문자열과 포함되지 않은 문자열에 대한 조건을 구분하여 처리하는 것입니다.

위의 예를 통해 살펴보면, A2셀부터 A6셀까지 처리와, A7셀부터 A10셀까지의 처리를 구분하여 처리를 한다고 생각하면 됩니다.

5. 최종결과

Sub textextract_in_textarray()

    Application.ScreenUpdating = False          '화면 업데이트 (일시)정지

    Dim k As Integer                            '루프용 변수

'       특수기호 "(" 이전 텍스트를 남기고 나머지를 제거한 여러 문자열에 걸쳐 추출하는 코드 반복할 열은 To 뒤의 숫자로 조정
        For k = 2 To 10

'       "문자열추출" 시트의 문자열추출 행의 값이 없을 경우에 문자열 추출 수행, 값이 이미 존재하는 경우 매번 반복하여 연산처리 하지 않도록 하기 위한 조건문
        If IsEmpty(Sheets("문자열추출").Cells(k, 2).Value) = True Then

'           순환문을 반복하는 동안 특수기호 "(" 가 포함된 문자열인지 체크
            If InStr(Sheets("문자열추출").Cells(k, 1).Value, "(") > 0 Then

'           InStr에 -1 파라미터를 적용할 수 있으며, 별도 조건문으로 분기처리하지 않을 경우 특수기호 "("가 문자열 내에 없을 경우 음수값이 발생해서 오류가 발생
            Sheets("문자열추출").Cells(k, 2).Value = Left(Sheets("문자열추출").Cells(k, 1).Value, InStr(Sheets("문자열추출").Cells(k, 1).Value, "(") - 1)

'           순환문을 반복하는 동안 특수기호 "(" 가 포함되지 않은 문자열에 대한 처리
            Else

'           "("가 문자열 내에 포함되지 않았으므로 그대로 값 복사
            Sheets("문자열추출").Cells(k, 2).Value = Sheets("문자열추출").Cells(k, 1).Value

            End If

        End If
        Next k

    Application.ScreenUpdating = True          '화면 업데이트 활성화 설정 (성능 개선 코드)

End Sub

위의 코드 중 아래 부분이 핵심이 되는 코드 부분입니다. InStr()의 결과값이 0보다 크다는 것은 해당 문자(“(“)가 문자열에 포함된 것을 탐색하는 조건이라고 보면 됩니다.

If InStr(Sheets("문자열추출").Cells(k, 1).Value, "(") > 0 Then

문자열에 "("가 포함된 경우 실행할 구문

else

문자열에 "("가 포함되지 않은 경우 실행할 구문

물론 해당 문자가 문자열에 포함되었는지를 탐색하는 다른 방법들은 많이 있으니 이를 응용해도 될 겁니다.

특정 문자 이후의 문자열을 추출하는 방식도 개념은 위와 동일하게 조건처리를 해주는 것은 동일하며, Left함수가 아닌 Right 함수를 이용하면 처리할 수 있습니다.

기타 관련하여 궁금한 사항이 있으면 언제든지 댓글로 내용 남겨주시면 답변 드리겠습니다. 고맙습니다.

예제파일 다운로드

이 블로그 검색

UNCLESOON'S BLOG