Need to split text into separate cells at a given length

[Jay@biketorqueracing]

Hi There

For the csv file I am working on to upload I have a cell full of text for each product which is the products description. The area that i need to upload it into can only take 40 chars per line and there is no word wrapping in place. I need some way to split the text in the cell at the space closest to 40 chars and shove the rest in the next cell along, then i can repeat until all the text is split up. The text ranges from 340 chars upwards.

Hope some one can help

[Pete_UK]

I think you'll need to post a few examples in a sample file which follows the same format that you are working with - the FAQ describes how to attach a file to a post.

Hope this helps.

Pete

[amy_d2]

Hi jay,

you can use formula in cell as mentioned below column wise see attached file

First Column =LEFT(B4,40), Second Column =MID(B4,41,40),Third column,=MID(B4,81,40), Fourth Column =MID(B4,121,40),Fifth Column =MID(B4,161,40),Sixth column =MID(B4,201,40),seventh column =MID(B4,241,40), eighth column =MID(B4,321,40)


Thanks,
A

Hi There

For the csv file I am working on to upload I have a cell full of text for each product which is the products description. The area that i need to upload it into can only take 40 chars per line and there is no word wrapping in place. I need some way to split the text in the cell at the space closest to 40 chars and shove the rest in the next cell along, then i can repeat until all the text is split up. The text ranges from 340 chars upwards.

Hope some one can help

[Jay@biketorqueracing]

Yeah that is close but how do I tell it to find the space closest to the 40 char limit so i do not get a word cut in half ? I thought if it leaves the fist set in place and dumps the remaining in the next cell then i can use the same formula on the next set

ZARDNOTESTEST.csv

This is the first line from the file I am playing with

[sunflowers]

Hi Jay@biketorqueracing!

Can you upload your sample file? I think it can make us to understand easily what do you want to do with data in this file. Thanks!

[Jay@biketorqueracing]

Its been added to the previous post

[sunflowers]

Sorry, but you dont describe your request. In your attach file have two rows not blank, I dont know what is original data, what is the result that you desire. It's difficult to understand it!

[Jay@biketorqueracing]

ZARDNOTESTEST.csv Try that

[Ghozi Alkatiri]

try this first formula at B1

=MID($A1,(COLUMNS($A:A)-1)*40+1,40)

copy to right and down

[Ghozi Alkatiri]

try this first formula at B1

=MID($A1,(COLUMNS($A:A)-1)*40+1,40)

copy to right and down

[István Hirsch]

Hope this works for you too. In the code fc is the first cell (now A1), lc is the last cell of the range (now A3), dst (now B1) is the cell where the split cells begin.

Sub splittxt()
Dim c As Range
Dim fc, lc, dst As String
Dim start, ostart, pstart As Long
fc = "a1"
lc = "a3"
dst = "b1"
    Application.ScreenUpdating = False
        For Each c In ActiveSheet.Range(fc, lc)
            start = 1
            pstart = 1
            For i = 1 To Len(c)
                ostart = start
                start = InStr(start + 1, c, " ")
                If start - pstart > 40 Then
                c = Application.WorksheetFunction.Replace(c, InStrRev(c, " ", pstart + 40), 1, "#")
                start = ostart
                pstart = ostart
                End If
            Next
        Next
        Application.DisplayAlerts = False
        Range(fc, lc).Select
        Selection.TextToColumns Destination:=Range(dst), DataType:=xlDelimited, other:=True, otherchar:="#"
        Application.DisplayAlerts = True
        Selection.Replace what:="#", replacement:=" "
    Application.ScreenUpdating = True
End Sub