How do I split a single cell into separate rows copied down?

How do I split a single cell into separate rows copied down? My delimiter is “.”

For example: in one single cell I have the text:

There are times when okay does not feel right. You need to stand firm. Let it go. Be very strong. And, when you least expect it, everything will work out. Say hello to a new day.


I need the text cell split into separate down row cells using the delimiter "period" with the space removed text alignment Left . Like this:

There are times when okay does not feel right.
You need to stand firm.
Let it go.
Be very strong.
And, when you least expect it, everything will work out.
Say hello to a new day.

Can someone write me a macro or function?? I’m new to Excel.

Thanks, Nick [email protected]

Hi,

Not what you wanted but may help until someone comes up with an answer for you.

Say text is in A1. Then enter this in A2.
=SUBSTITUTE(A1,". ","."&CHAR(10))

Format cell as Wrap text. Paste Special values

This inserts a return after each full stop so the data appears as below but all in A2.

There are times when okay does not feel right.
You need to stand firm.
Let it go.
Be very strong.
And, when you least expect it, everything will work out.
Say hello to a new day.


VBA Noob

Originally Posted by Nick8888

How do I split a single cell into separate rows copied down? My delimiter is “.”

For example: in one single cell I have the text:

There are times when okay does not feel right. You need to stand firm. Let it go. Be very strong. And, when you least expect it, everything will work out. Say hello to a new day.


I need the text cell split into separate down row cells using the delimiter "period" with the space removed text alignment Left . Like this:

There are times when okay does not feel right.
You need to stand firm.
Let it go.
Be very strong.
And, when you least expect it, everything will work out.
Say hello to a new day.

Can someone write me a macro or function?? I’m new to Excel.

Thanks, Nick [email protected]

For a single A1 cell,
in B1 put =A1

in A2 put
=LEFT(B1,FIND(".",B1))
and in B2 put
=MID(B1,FIND(".",B1)+2,99999)

and formula-fill this pair down as far as required.

Then (the usual) copy A and paste special = values, delete Row 1 and column B.

---