splitting text in a cell

[bj6264]

Hi, I have a cell containing text that I need to export into another application. however this application only takes 40 characters and needs the cells to wrap to a new row. ie if my cell is 83 characters long, I need 40 characters on the first row, 40 on the second row & 3 in the third. Can another help?
Thanks

 

[dominicwellsuk]

If it's a one off, you can just use the text to columns feature under Data. Have a play with that and see if it suits.

 

[Peter_SSs]

Do you mean something like this?
Formula in A2 is copied down.

Excel Workbook
	A
1	This has a lot of text all in one cell. The text needs to be split into a number of cells. Each cell should have a a maximum of 40 characters.
2	This has a lot of text all in one cell.
3	The text needs to be split into a number
4	of cells. Each cell should have a a max
5	imum of 40 characters.
6
Long Text

 

[Sektor]

Activate cell with text.

[COLOR="Blue"]Sub[/COLOR] SplitText()

    [COLOR="Blue"]Dim[/COLOR] iLen [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], s [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR], i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], iShift [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR], j [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    
    iShift = 1
    [COLOR="Blue"]With[/COLOR] ActiveCell
        [COLOR="Blue"]If[/COLOR] Len(.Value) > 40 [COLOR="Blue"]Then[/COLOR]
            iLen = Len(.Value)
            [COLOR="Blue"]For[/COLOR] i = 1 [COLOR="Blue"]To[/COLOR] iLen
                s = s & Mid(.Value, i, 1)
                [COLOR="Blue"]If[/COLOR] i [COLOR="Blue"]Mod[/COLOR] 40 = 0 [COLOR="Blue"]Then[/COLOR]
                    ActiveCell.Offset(iShift, 0) = s
                    s = ""
                    iShift = iShift + 1
                [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
            [COLOR="Blue"]Next[/COLOR]
            [COLOR="Blue"]If[/COLOR] iLen [COLOR="Blue"]Mod[/COLOR] 40 <> 0 [COLOR="Blue"]Then[/COLOR]
                j = Fix(iLen / 40)
                [COLOR="Blue"]If[/COLOR] j * 40 > iLen [COLOR="Blue"]Then[/COLOR]
                    .Offset(iShift, 0) = Mid(.Value, j * 40, iLen - j * 40)
                [COLOR="Blue"]Else[/COLOR]
                    .Offset(iShift, 0) = Right(.Value, iLen - 40 * j)
                [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
            [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[bj6264]

Many thanks, thats great!

 

[Sektor]

Notice I corrected code.