[VBA] 텍스트에서 특정 문자만 추출하는 3가지 방법 /VBA InStr(), Mid() / 함수 LEN(), LEFT(),RIGHT(),MID(),FIND() / 엑셀 메뉴

안녕하세요 케이네입니다.

엑셀에서 데이터를 정리할때 문자열중 특정 문자만 추출해서 새로운 열을 만들고 싶을때가 있어요. 그럴땐 엘셀의 함수에서는 FIND 함수와 LEN, MID, LEFT, RIGHT함수를 적절히 적용하면 가능하지요!

 

예시 : A1 셀의 문자열에서 출발지를 나타내는 문자와, 도착지를 나타내는 문자를 구분해서 새로운 셀에 정리

하이픈 "-" 을 기준으로 좌우 문자를 나누는 수식이에요. 사용하시는 문자열에 적용하시면 좋겠습니다.

 

=LEFT(A1,FIND("-",A1)-1)

=RIGHT(A1,LEN(A1)-FIND("-",A1))

함수 수식

 

결과

LEN() 함수는 문자열의 길이를 세어줍니다.

FIND() 함수는 찾는 문자의 시작점 (문자의 몇번째에서 찾는 글자가 시작되는지) 을 알려줍니다.

 

예를들어 아래와 같이 FIND("-",A1) A1셀의 하이픈의 위치를 물어보면, 숫자 12를 반환합니다. 셀의 문자 12번째에서 하이픈 "-"을 발견했다는 뜻이지요. 

 

 

LEFT()와 RIGHT()함수는 셀에서 글자를 몇개를 추출할것인지를 지정하는 함수에요. 왼쪽에서 가져오고싶을때는 LEFT(가져오고싶은 셀, 가져올 글자 수) 로 지정하시면 되고 RIGHT()도 사용법은 마찬가지 입니다.

 

MID()함수는 오른쪽도 왼쪽도 아닌 중간 어딘가에 있는 글자를 가지고 오고싶을때 사용해요. 

MID(가져올 셀, 몇번째 글자부터 가지고 오는지, 가지고올 글자의 수)

예를들어 아래의 셀에서 인보이스 번호만 가져오고 싶을때의 수식은 MID(A7,9,11) 입니다. 주의점은 띄어쓰기 한 부분도 문자로 카운트 합니다.

 

MID() 수식

 

MID()함수 적용 결과

 

근데 이러한 문자를 추출하는 작업이 무수히 많을때는 엑셀 함수만으로 전부 적용하기가 매우 귀찮을때가 있어요.

그럴때는 VBA로 아래와 같이 하는 방법도 있어요. 엑셀 함수에서 사용하는 FIND() 함수는 VBA에서는 InStr()이 동일한 기능을 합니다. 찾는 문자열의 위치를 반환하지요. VBA에서는 InStr()과 Mid()를 적절히 활용하면 문자열을 추출할 수 있어요.

 

Sub Data()
 For i = 1 To 10
    If InStr(Cells(i, 1), "INV NO.") > 0 Then
     Cells(i, 2).Value = Mid(Cells(i, 1), 9, 11)
    End If
Next i
End Sub

 

잘 활용하면 인보이스의 형식이 변환되어도 문자열을 가져올 수 있도록 활용 가능할것 같아요. 예를들어 "/"를 찾아서 그 뒤에 나오는 글자수를 (LEN() 활용) 뺀 나머지 문자열을 불러오거나 하는것도 가능하겠네요. 추출해야 하는 데이터가 매우 많다면 FOR문에서 i를 Range("A1").End(xlDown) 등으로 설정해 보는것도 좋겠어요. 그리고 IF문에서 예외의 경우는 ElseIf를 사용합니다.

 

데이터가 잘 정리된 상태라면 데이터 탭에 있는 텍스트나누기를 활용하는게 편리하기는 해요. 

 

 

하지만 추출해야 하는 문자열이 규칙성이 없는데, 그마저도 매주, 매달 작업해야 하는일이 있다면 VBA로 한번 짜두시는것을 추천합니다!