VBA - Breaking a cell string into individual cells based on length

Question

I have a cell with a string of different lengths. I want split them into individual cells with a length of, say, 3 characters.

A cell with ABCCBA should end up ABC CBA in 2 different cells.

While a cell with ABCDABCDAB should end up ABC DAB CDA B in 4 different cells.

Is there any convenient way to do this?

---

I was looking at

' Finding number of cells
Segments = WorksheetFunction.RoundUp(Len(Range("A1").Value) / 3, 0)

' Split base on character length
For n = 1 to Segments
    Cells(2, n) = Range("A1").Characters(n, 3)
Next n

But it doesn't seem to work.

Answer 1

A simple macro to split the string in to 3 lettered strings and write into columns next to the data range

Sub Split()
Dim Checkcol As Integer
Dim currentRowValue As String
Dim rowCount As Integer
Dim splitval As Integer
Dim i As Integer, j As Integer

Checkcol = 1 'Denotes A column
rowCount = Cells(Rows.Count, Checkcol).End(xlUp).Row
For currentRow = 1 To rowCount
      currentRowValue = Cells(currentRow, Checkcol).Value
      splitval = Int(Len(currentRowValue) / 3) + 1 'Find the number of 3 letter strings
       j = 0
        For i = 1 To splitval 'Loop through each value and write in next columns
           j = (i - 1) * 3 + 1
           Cells(currentRow, Checkcol + i).Value = Mid(currentRowValue, j, 3)
       Next
Next

End Sub