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

Question

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 "/" .

Answer 1

• Use Split function to get substrings, then concat the new item

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