Splitting out text in cell A1

[p4nny]

Hi

I have a series of sentences with bullet points all in cell A1 of a workbook.

I would like to split out so each bullet point has its own cell in column A.

The second bullet point would go into cell A2 and so on.

Is this possible? Appreciate any help.

 

[edmitchell]

Hi

it should be possible, can you post an example of what you have in A1?

Ed

 

[p4nny]

Hi
Thanks. This is all in cell A1. Basically would like the second point to go into cell A2. I have some instances where there are multiple bullet points

• There is a lack of uncertainty in my area as to what this looks like for us.
• Roll things out in comms as soon as they are know

Appreciated

 

[István Hirsch]

Try this:

Excel Workbook
	A
2	There is a lack of uncertainty in my area as to what this looks like for us. Roll things out in comms as soon as they are know
3	There is a lack of uncertainty in my area as to what this looks like for us.
4	Roll things out in comms as soon as they are know
Sheet1

 

[p4nny]

Perfect thanks -

Would you kindly mind explaining how the formula works?

 

[István Hirsch]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1
2											*	1	s	t											*	2	n	d
3											|	x	x	x	x	x	x	x	x	|	|	z	z	z	z	z	z	z	z	|
4												mid($A2,1*10,10)										mid($A2,2*10,10)
5											10										20										30
Sheet1

First, the Substitute function substitutes each line break (char 10) with 500 spaces (now for demonstration, with 10 spaces), so a long line is produced where each original line is separated by10 spaces. That is, each consecutive 10 character long part of this long line will contain one original substring, embedded in spaces. After trimming we get the substring itself. The start of the substrings is located at (ROW(A3)-2)*10 =10, (ROW(A4)-2)*10 = 20 character and so on. Dragging down the formula in column A from cell A3 to A4, A5 etc., ROW() will increase by 1, so in A3 the first, in A4 the second original line will be displayed.