Split a Column into 2, use a character limit that does not cut words in half.

[vishesh10667]

Title says it all.

I have huge columns of data that can only be 30 characters long. I want to use a formula to split the column into two. The original column will have a character limit of 30, and should not cut words in half. If the Length will be more than 30 while including the entire last word of the cell, that word should be the first displayed in adjacent cell in the 2nd column.

The 2nd column should also be 30 characters long. And rest in 3 column


I usually use a Text to Columns method, but dont know how to make it not cut off the words. Any ideas?

Thanks in advance

 

[vishesh10667]

Hard to read much in that image but it looks like the code has processed some of the rows. Can you post the text of what is in the next row in column A (looks like that might be row 9?) as I assume there must be something different/unusual about what is in that cell.

It is showing a runtime error 9 that the subscript is out of range

 

[Peter_SSs]

It is showing a runtime error 9 that the subscript is out of range

Yes, I could read that much before. What is the answer to the question I asked about it?

.. it looks like the code has processed some of the rows.
Can you post the text of what is in the next row in column A (looks like that might be row 9?) as I assume there must be something different/unusual about what is in that cell.

 

[Peter_SSs]

can you send me the revised ?

The revised is just the formulas that I gave in post 7 and again in post 9.
Have you tried what I suggested in post 18?

 

[vishesh10667]

The revised is just the formulas that I gave in post 7 and again in post 9.
Have you tried what I suggested in post 18?

Yes i have tried it, but it is not working

 

[vishesh10667]

Yes, I could read that much before. What is the answer to the question I asked about it?

Binoj bhavan,EdavakodeSreekariyam p.o

Some of the address there is no space in between, i think because of this it is not capturing

 

[Peter_SSs]

Binoj bhavan,EdavakodeSreekariyam p.o

Thanks. Try making this change in the code.

ReDim result(1 To Len(s) / CharsPerLine + 1)
ReDim result(1 To Len(s))

 

[vishesh10667]

The revised is just the formulas that I gave in post 7 and again in post 9.
Have you tried what I suggested in post 18?

I have tried it is not working.

 

[Peter_SSs]

I have tried it is not working.

Is it the same "not working" as in post 15?
If so, try copying the formulas from here instead.
For cell B1:

=IF(LEN(A1)<=30,A1,LEFT(A1,FIND("#",SUBSTITUTE(LEFT(A1,31)," ","#",LEN(LEFT(A1,31))-LEN(SUBSTITUTE(LEFT(A1,31)," ",""))))-1))

For cell C1:

=IF(LEN(A1)<=61,REPLACE(A1,1,LEN(B1)+1,""),LEFT(REPLACE(A1,1,LEN(B1)+1,""),FIND("#",SUBSTITUTE(LEFT(REPLACE(A1,1,LEN(B1)+1,""),31)," ","#",LEN(LEFT(REPLACE(A1,1,LEN(B1)+1,""),31))-LEN(SUBSTITUTE(LEFT(REPLACE(A1,1,LEN(B1)+1,""),31)," ",""))))-1))

For cell D1:

=TRIM(SUBSTITUTE(A1,TRIM(B1&" "&C1),""))

If it a different "not working" then please explain in what way it is "not working"


How did the macro go when you made the change in post 27?

 

[vishesh10667]

=IF(LEN(A1)<=61,REPLACE(A1,1,LEN(B1)+1,""),LEFT(REPLACE(A1,1,LEN(B1)+1,""),FIND("#",SUBSTITUTE(LEFT(REPLACE(A1,1,LEN(B1)+1,""),31)," ","#",LEN(LEFT(REPLACE(A1,1,LEN(B1)+1,""),31))-LEN(SUBSTITUTE(LEFT(REPLACE(A1,1,LEN(B1)+1,""),31)," ",""))))-1))

It is just showing the below error

 

[Peter_SSs]

It is just showing the below error

Hmm, you must be using a very old Excel version. You really should state that in any questions you ask as helpers will generally expect something newer than that.