split the text in a cell into rows

**rrakkki** · 11-09-2011, 11:40 AM

hi,
I'm new to excel.
I want a macro to split text in a cell to rows and the cell formated to wrap text with a fixed length and the text should be splitted till the end of the text in line one of a cell.
Pls find the attached example.
test.xlsx

In the above example, the text should be splitted in A1 cell till "It is mainly concerned with recording of financial data relating to the " and the second line in cell one into new row i.e.,"business operations in a significant and orderly manner." and if a cell contain third line then it should be splitted into third row and so on so forth...

Your help is greatly appreciated....

**pike** · 11-10-2011, 03:53 AM

Hi rrakkki
why not just use the "text to columns" feature with fixed lenght?

**venkat1926** · 11-10-2011, 04:24 AM

try this macro(assuming it does not exceed 210 characters(that is it can run up to three rows)
(this is not fixed length but very nearly as fixed length may split a word)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I am sending you the file "rrakki.xlsm" after running the macro "test"
you can run undo and again run test.

**rrakkki** · 12-01-2011, 10:41 AM

thanks for ur help..
but when i replaced the text with another text.. i'm getting error.."Unable to get the search property of the worksheetfunction class....
more over... the text in a cell is not fixed...it can be any number of words and the column width is "72", if the data in a cell exceed the specified width then it should be splitted into next "row" and i have 21 rows...that needs to be splitted at one time... and each cell of a row should not cross column width of "72"
here i'm attaching a new sheet...
Please help me....