'셀 분리 VBA'에 해당되는 글 2건

네이버 지식인에 올라온 자료가 궁금해서 작성을 해봤다.

배열에 대한 이해도 좀 더 높일 겸해서 VBA 로 코드를 작성해 보면서 디버깅 기능을 이용하여 중간 중간 확인 작업도 해봤다.

매장데이터분리.xlsm

Sub 문자_숫자_분리추출()
'// Alt + F11 키를 눌러서 [삽입] - [모듈] 에 이 VBA 코드를 붙여넣기하고 F5 키를 눌러서 실행
    Dim rngC, rngAll As Range
    Dim v
    Dim k As Long
    Dim i, c, n As Integer
   
    Set rngAll = Range("A2:A" & Cells(Rows.Count, "A").End(3).Row)      '// 범위 구간 설정
    Range([B1], Cells(Rows.Count, "B").End(3)).Offset(1).Resize(, 2).ClearContents    '// 값을 뿌릴 영역 초기화
   
    For Each rngC In rngAll
        v = Split(rngC, "+")       '// + 를 구분자로 셀을 분리
        ReDim Num(1 To 1): ReDim Dat(1 To 1): n = 0   '// 배열 및 배열에 사용할 변수 초기화
        For i = LBound(v) To UBound(v)      '// + 구분자로 분리한 배열의 개수만큼 반복
            n = n + 1
            ReDim Preserve Num(1 To n): ReDim Preserve Dat(1 To n)     '// 기존값을 유지하면서 배열크기를 다시 설정
            For c = 1 To Len(v(i))                      '// 전체 문자길이 만큼 반복
                If IsNumeric(Mid(v(i), c, 1)) Then  '// 문자열이 숫자일 경우
                    Num(n) = Num(n) & Mid(v(i), c, 1)   '// 각 숫자를 합쳐감
                Else
                    Dat(n) = Dat(n) & Mid(v(i), c, 1)   '// 숫자가 아닌 것을 합쳐감
                End If
            Next c
            'Debug.Print "행 =" & rngC.Row & "  숫자 i =" & i & "  숫자 n =" & n & "  Dat =" & Trim(Dat(n)) & "  Num =" & Num(n)
           
            '// 셀에 뿌리는 작업   처음 k =0 Cells(k + 2, "B") 는 B2 셀
            Cells(k + 2, "B") = Trim(Dat(n))
            Cells(k + 2, "C") = Num(n)
            k = k + 1
        Next i
    Next rngC
   
    Set rngAll = Nothing    '// 메모리 비우기   
End Sub

InStr 를 이용한 셀 분리

이번에는 InStr 함수를 이용하여 셀 분리를 해보겠습니다.

범위 지정은 Range([A2], Cells(Rows.Count, "A").End(3))

구분자의 위치값을 반환하여 Left 함수 및 Mid 함수를 이용하여 원하는 구간을 잘라낼 수 있다.

Sub Cell_Split()
    Dim rngC    As Range   '// 한 Cell 씩 변하는 변수 지정
    Dim rngTarget As Range '// 대상 범위 지정변수
    Dim Split_L As Long    '// 구분자 위치 변수
    Dim deLimiter As String  '// 문자 구분자 변수
   
    Application.ScreenUpdating = False  '//화면 업데이트 (일시)정지
    Set rngTarget = Range([A2], Cells(Rows.Count, "A").End(3))
    '// A열 2행부터 값이 들어있는 마지막 행까지의 범위를 지정
  
    deLimiter = "/"       '//문자 구분자
    For Each rngC In rngTarget       
        Split_L = InStr(rngC.Value, deLimiter)
        '// InStr 함수 : 한 문자열 안에 특정 문자열이 처음으로 나타난 위치값을 반환       
        If Split_L > 0 Then
            rngC.Offset(0, 2).Value = Mid(rngC.Value, Split_L + 1, Len(rngC.Value) - Split_L)
            'rngC.Offset(0, 1).Value = Left(rngC.Value, Split_L - 1)  '// 구분자 앞부분을 표시
        End If
    Next rngC
    
    Set rngTarget = Nothing '// 변수 초기화
    Columns("C:G").AutoFit
End Sub

Cell_Split_VBA_instr.vbs

Cell_Split_VBA_instr.xlsm

rngC.Offset(0,2) 의 의미는 rngC 변수로부터 우측으로 2열 이동하라는 의미다.

만약 현재 셀에다가는 구분자의 왼쪽 값을 표시하고 싶다면

rngC.Offset(0,1).Value = Mid(rngC.Value, Split_L + 1, Len(rngC.Value) - Split_L)

rngC.Value = Left(rngC.Value, Split_L - 1)

로 변경해주면 된다.

우측에 표시될 값부터 먼저 표기한 이유는 위아래 순서를 변경해보면 알아요.

하지만, split 함수를 이용하면 훨씬 더 깔끔하게 해결할 수 있습니다.

첨부파일에 아래 코드를 추가로 포함시켜 놓았습니다.

Sub 셀분리()
    Dim rngC, rngAll As Range
    Dim v
    Set rngAll = Range([A2], Cells(Rows.Count, "A").End(3))
    Range([C2], Cells(Rows.Count, "D")).ClearContents   '// 표시할 영역 데이터 전부 지우기
    On Error Resume Next
    For Each rngC In rngAll
        v = Split(rngC, "/")
        Cells(rngC.Row, "C") = Trim(v(0))
        Cells(rngC.Row, "D") = Trim(v(1))
    Next rngC
    Set rngAll = Nothing
    Columns("C:G").AutoFit
End Sub