Excel Makro: Text in Spalten aufteilen mit maximal 132 Zeichen,
wobei Wörter nicht getrennt werden dürfen
vba Makro, Alt+F11
Option Explicit On
Const max_length = 132
Public Sub Text_in_Spalten_maxLength() '------------< Text_in_Spalten_maxLength() >------------ Dim sheet As Worksheet Set sheet = ActiveSheet
Dim cell As Range For Each cell In sheet.Range("A2:A100") Dim sText As String sText = cell.Text
Dim iRow As Integer iRow = cell.Row
If sText Like "" Then Exit Sub Dim arrWords arrWords = Split(sText, " ")
Dim iCol As Integer iCol = 2 Dim varWord Dim sTextMax As String
For Each varWord In arrWords sTextMax = sTextMax & " " & varWord If Len(sTextMax) < max_length Then
Else sTextMax = Replace(sTextMax, " ", "", 1, 1) sheet.Cells(iRow, iCol) = sTextMax sTextMax = "" iCol = iCol + 1 End If
Next sTextMax = Replace(sTextMax, " ", "", 1, 1) sheet.Cells(iRow, iCol) = sTextMax
Next
'------------</ Text_in_Spalten_maxLength() >------------ End Sub |