excelvbastringsplitsubstitution

Extracting text after nth of a character and put in next row , under first row in Excel VBA Code


I'm very new in VBA maybe can some one help me with my problem. I have in my rows (this example)

A1: 52/ATL/340/M/50/C
A2: 52/IST/620/M/DBX/200/D

I need first split after 4th "/" to the next row with adding the first 6 strings "52/ATL/" to "50/C" - basically: "52/ATL/50/C"

B1: 52/ATL/340/M
B2: 52/ATL/50/c
B3: 52/IST/620/M
B4: 52/DBX/200/D

I try it with SUBSTITUTE , but my problem - this function not in VBA code, and I don't know how to split after 4th "/"and put down with text from first"/" and second "/" .


Solution

  • Microsoft documentation:

    Split function

    Join function

    Range.Resize property (Excel)

    Sub Demo()
        Dim i As Long, j As Long
        Dim arrData, arrRes, iR As Long, aTxt
        arrData = ActiveSheet.Range("A1").CurrentRegion.Value
        ReDim arrRes(1 To UBound(arrData) * 2, 0)
        For i = LBound(arrData) To UBound(arrData)
            aTxt = Split(arrData(i, 1), "/")
            j = UBound(aTxt)
            If j >= 3 Then
                iR = iR + 1
                arrRes(iR, 0) = Join(Array(aTxt(0), aTxt(1), aTxt(2), aTxt(3)), "/")
                If j = 5 Then
                    iR = iR + 1
                    arrRes(iR, 0) = Join(Array(aTxt(0), aTxt(1), aTxt(4), aTxt(5)), "/")
                ElseIf j = 6 Then
                    iR = iR + 1
                    arrRes(iR, 0) = Join(Array(aTxt(0), aTxt(4), aTxt(5), aTxt(6)), "/")
                End If
            End If
        Next i
        ActiveSheet.Columns(3).Clear
        ActiveSheet.Range("C1").Resize(iR).Value = arrRes
    End Sub