Mr_Huang wrote:
thank you for your help,
however, i'm pretty new to vb in excel.
How can I run this formula? Cannot find this in the run menu of
Microsoft VB - Marco dialogs.

It's not a formula, it's a visual basic (for applications) procedure,
you need to enter the VBA editor:
Tools - Macro - Visual Basic Editor

On the upper left side of the window that opens, you'll see a project
explorer window. In that, right click and select Insert - Module.

On the properties explorer on the lower left, choose a name to your
liking for the module.

Now paste the code I posted before into the editor window on the right side.
When you want to call that code from an excel sheet, you could for
example create a button on your sheet, and link the function with that,
with the desired parameters:
View - Toolbars - Control Toolbox (I don't have an english office
available atm, the menu names might not be 100% precise). From there,
select a command button. Double click it to enter the OnClick event
procedure stub. In that procedure, between the two lines that Excel
pre-created for you, just enter the call to the TextToRows function, for
example
TextToRows 1, 2, 1, 500, vbCr
(convert column 1, put values into column 2, read data from row 1 to row
500, look for carriage return (vbCr) as delimiter in the source values.)

Then, all you need to do in your sheet, is click on the button and voilá
- the text in column 1 is put into multiple rows in column 2.

You could also get the parameters for the function from cells in your
excel sheet, e.g.

TextToRows Range("E1"), Range("F1"), Range("G1"), Range("H1"), vbCr

HTH,

Lars