On Wednesday, June 26, 2013 3:09:05 AM UTC-7, Claus Busch wrote:
Hi Howard,



So with the line above I would want it to wind up like this.



HZG MY AG AGHTO TYMVKTZ.



K RHT'M ZGM YIJ; K'A



then try:



Sub Test2()

Const maxLen As Integer = 26

Dim Str1 As String

Dim i As Integer



For i = 1 To 7 Step 3

Str1 = ""

Str1 = IIf(Len(Cells(i, 1)) maxLen, Left(Cells(i, 1), _

InStrRev(Cells(i, 1), " ", maxLen)), Cells(i, 1))

Cells(i, 1).Offset(1, 0) = Replace(Cells(i, 1), Str1, "")

Cells(i, 1) = Str1

Next



Columns("A").TextToColumns Destination:=Range("B1"), _

DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _

Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _

Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _

Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), _

Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), _

Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _

Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), _

Array(25, 1), Array(26, 1)), TrailingMinusNumbers:=True

End Sub

Regards

Claus Busch



Very nice, that has me right where I need to be.
I'll do some local tweaking on the sheet, but getting that "max 26 char on two lines split" is awesome to me. Love it.

Thank again, Claus.

Regards,
Howard