Split text to rows below

[thedeadzeds]

Hi Guys, is there a way to split the text below to rows below the text?

Testthisone Otherthanthis profittoday

All text is seperated by a space so the disired result would be:

Test this one
Other than this
profit today

 

[Fluff]

How about

Fluff.xlsm
	A
1
2	Testthisone Otherthanthis profittoday
3	Testthisone
4	Otherthanthis
5	profittoday
Smith, John

Cell Formulas
Range	Formula
A3:A5	A3	=TEXTSPLIT(A2,," ")
Dynamic array formulas.

 

[thedeadzeds]

Thanks Fluff, that did work but I've noticed some cells look like the below when you click off the cell so there is actually a space between both words on some but not on others. It looks like the words have been separated using alt and enter

Test Deposits New Creditor New Vehicle Vehicle Creditors Consigned Other Trade

 

[Fluff]

In that case try

=TEXTSPLIT(A2,,CHAR(10))

 

[thedeadzeds]

Perfect, is there a way to drag that formula down when there is data in the rows below? For example: At the moment I'm getting a spill error when I drag it

Data	Text Split Formula
Balance TestDocs Total GP	Balance
	Test Docs
	Total
	GP
Five	Five
Twelve	Twelve
Jim	Jim
Steve	Steve
Dave	Dave

 

[Fluff]

No you cannot drag it down, otherwise it will prevent the spill.
What exactly are you trying to do?

 

[thedeadzeds]

The data column is the column that needs splitting. As you can see some of the headers are in one cell, but the data in the amount column relates to splits. For example the 500 in B6 in the amount column relates to Profit in cell A5. The problem being there is data below the cells that need splitting. Hope this makes sense

Data	Amount	Desired new Data Column
Sales	500	Sales
Profit	100	Profit
GP	120.00%	GP
Sales Profit Corporate	50	Sales
	500	Profit
	100.00%	Corporate
Other Sales Other Profit Other GP%	17	Other Sales
	18	Other Profit
	100.00%	Other GP%
Collision Waiver GP %	41	Collision
	45	Waiver
	100.00%	GP %
Total Sales	25	Total Sales
Total Profit	25	Total Profit
Total GP%	100.00%	Total GP%
Other Income	-	Other Income
Total Sales	29	Total Sales
Total Profit	28	Total Profit
Total GP%	100.00%	Total GP%

 

[Fluff]

You will just have to put the formula into the 1st blank cell for each value that needs to be split.

 

[thedeadzeds]

ok thanks Fluff, I have 207 pages of this so was hoping to be able to drag it. No probs though your formula will still help speed the process up. Thanks agin

 

[Fluff]

Glad to help & thanks for the feedback.