Split Text String With Mid to Next Column

[exc4libur]

Hello all

I want to break a text string of 80,000 by 32,767 characters which would split it by 3 (Round[80k/33k]) columns. But I can't seem to split the string correctly. This is where i got so far, but its wrong.

Any help would be appreciated, thank you

If Len(Text) > 32767 Then
txtSplit = Round(Len(Text) / 32767, 0)
For i = 1 To txtSplit
Text = Mid(Text, (32767 * (txtSplit - i) + 1) - 32767, 32767)
ActiveSheet.Cells(NextRow, 2 + i).Value = Text
Next i
Else

 

[Chihiro]

Your txtSplit will be equal to 2 due to your rounding calculation.

You'll need to round up. But since VBA does not have ROUNDUP function. Use Mod & Int function.

As best practice, avoid using variable that is used in function/method (i.e. instead of TEXT use iText, myText or some such).

So something like below.

If Len(iText) > 32767 Then
    If Len(iText) Mod 32767 > 0 Then
        txtSplit = Int(Len(iText) / 32767) + 1
    Else
        txtSplit = Len(iText) / 32767
    End If
    For i = 1 To txtSplit
        ActiveSheet.Cells(NextRow, 2 + i).Value = Mid(iText, 32767 * (i - 1) + 1, 32767)
    Next i
Else
End If

 

[exc4libur]

Your txtSplit will be equal to 2 due to your rounding calculation.

You'll need to round up. But since VBA does not have ROUNDUP function. Use Mod & Int function.

As best practice, avoid using variable that is used in function/method (i.e. instead of TEXT use iText, myText or some such).

So something like below.

If Len(iText) > 32767 Then
    If Len(iText) Mod 32767 > 0 Then
        txtSplit = Int(Len(iText) / 32767) + 1
    Else
        txtSplit = Len(iText) / 32767
    End If
    For i = 1 To txtSplit
        ActiveSheet.Cells(NextRow, 2 + i).Value = Mid(iText, 32767 * (i - 1) + 1, 32767)
    Next i
Else
End If

Hi Chihiro,
Thank you for responding. Do you know a way I could break the string by a number of characters to columns, for instance 3 characters example:
String:
iText = "aaafffzzz"

Results:
Column A = "aaa"
Column B = "fff"
Column C = "zzz"

This is the part I am having difficulty with!!!

 

[Chihiro]

Something like this?

Sub Test()
Dim iText As String
Dim iDiv As Long

iText = "aaafffzzz"
iDiv = 3
If Len(iText) > iDiv Then
    If Len(iText) Mod iDiv > 0 Then
        txtSplit = Int(Len(iText) / iDiv) + 1
    Else
        txtSplit = Len(iText) / iDiv
    End If
    For i = 1 To txtSplit
        ActiveSheet.Cells(1, i).Value = Mid(iText, iDiv * (i - 1) + 1, iDiv)
    Next i
Else
End If
End Sub

 

[exc4libur]

Something like this?

Sub Test()
Dim iText As String
Dim iDiv As Long

iText = "aaafffzzz"
iDiv = 3
If Len(iText) > iDiv Then
    If Len(iText) Mod iDiv > 0 Then
        txtSplit = Int(Len(iText) / iDiv) + 1
    Else
        txtSplit = Len(iText) / iDiv
    End If
    For i = 1 To txtSplit
        ActiveSheet.Cells(1, i).Value = Mid(iText, iDiv * (i - 1) + 1, iDiv)
    Next i
Else
End If
End Sub

Thank you very much Chihiro!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!