Splitting a cell based on character count,

[g8rfn]

Hi everyone,

I have a whole bunch of cells with super long strings of text. What I need is a macro or some automated way to split each long cell into multiple cells that have a MAXIMUM of only 40 characters each. Also, when it splits the characters, I don't want it to chop off the words either. Is this even possible in Excel? So, for example, my first cell in the list has 197 characters, so I would need that split into 5 cells, or possibly even 6 depending on how close to 40 it could split each string into.

My mind is going numb... If there weren't 1800 of these, I would jsut do it manually... Heck, I may have even been done by now if I wasn't still trying to figure out how to do it "easily."

 

[g8rfn]

For example... Here is one of the strings that gets cut off.

"Bobby Labonte name and or likeness used under license by Petty Marketing Company, LLC. Cheerios and Betty Crocker are trademarks of General Mills used under license."

When I run the code it drops the very last part: license."

 

[Scott Huish]

OK, try this:

Sub splitup40()
Dim c As Range, z As String, tmp As String, j As Long, k As Integer
k = -1
For Each c In Range("A1", Range("A65536").End(xlUp))
    z = Trim(c)
    Do
        tmp = Trim(Left(z, 40))
        j = 40
        If Right(tmp, 1) <> " " And (Mid(z, 41, 1) <> " " And Len(z) > 40) And Right(tmp, 1) <> "." And Right(tmp, 1) <> "," Then j = InStrRev(tmp, " ")
        tmp = Left(tmp, j)
        If tmp <> "" Then Range("B65536").End(xlUp).Offset(1 + k, 0) = tmp
        k = 0
        z = Trim(Mid(z, j + 1))
    Loop Until tmp = ""
    k = 1
Next
End Sub

 

[g8rfn]

Pure Genius....

1800 lines, 100% accuracy.

A++++ Thank you sooooo much!

 

[hariprasad]

There is still some slight wacky-ness with some of them... But, I can't seem to narrow down what the problem is. In any event, I'd say about 60-75% of them look correct.

Thanks again. This has saved me a TON of time.

Hi,

IS there a way to split the numbers where the count exceeds 500 but the number will not cut off. Any help would be appreciated.

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]21427000896,21352000781,20000051815,9463314602,9460870992,9460869022,9460867816,9460867256,9460866680,9460866604,9453051094,9448927745,9448301941,9448297308,9448296275,9448295245,9448294265,9448294045,9446156665,9444186159,9444181305,9439602057,9437809730,9437383528,9434288167,9431568138,8002979395,8001801569,8001445075,7901663769,800043480,625882645,310085422,310085322,210465522,110459422,110441122,110427822,110427722,110427622,110427522,110422222,110422122,110422022,110421922,110421622,110421522,110421222,110401522,70006466,70006413,70006411,70005057,30023392,30023300,30021412,30016815,30014675,30010312,30010281,30010272,30008619,30008616,30005569,20014424,10000349,8000018,3088299,3078419,415495[/TD]
[/TR]
</tbody>[/TABLE]

 

[hariprasad]

Hi,

IS there a way to split the numbers where the count exceeds 500 but the number will not cut off. Any help would be appreciated.

Ex: [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]21427000896,21352000781,20000051815,9463314602,9460870992,9460869022,9460867816,9460867256,9460866680,9460866604,9453051094,9448927745,9448301941,9448297308,9448296275,9448295245,9448294265,9448294045,9446156665,9444186159,9444181305,9439602057,9437809730,9437383528,9434288167,9431568138,8002979395,8001801569,8001445075,7901663769,800043480,625882645,310085422,310085322,210465522,110459422,110441122,110427822,110427722,110427622,110427522,110422222,110422122,110422022,110421922,110421622,110421522,110421222,110401522,70006466,70006413,70006411,70005057,30023392,30023300,30021412,30016815,30014675,30010312,30010281,30010272,30008619,30008616,30005569,20014424,10000349,8000018,3088299,3078419,415495[/TD]
[/TR]
</tbody>[/TABLE]

 

[turkanet]

Thanks a lot, very useful