split contents of cell into other cells

[ronie85]

in cell B1 I have

'Stoke 0-1 Aston Villa' how do I get this contents into 5 different cells. F1 'Stoke', G1 '0', H1 '-', I1 '1', J1 'Aston Villa'

<tbody>
</tbody><colgroup><col></colgroup>

 

[VoG]

Try Date Text to Columns, delimited with Space as the delimiter. This will split Aston Villa into two (probably not a bad idea).

 

[ronie85]

I am pasting this from a website on a weekly basis and want the formula to be in place to strip the cell down into different cells. I want to make it as straight forward as possible.

 

[ronie85]

To get Stoke in a separate cell I can use =LEFT(B4,FIND("-",B4)-2) but I don't know how to get any of the other contents in the other cells

 

[Momentman]

Is this close to what you want? i typed in the dash(-) manually

Excel 2010
	A	B	C	D	E	F
1	Stoke 0-1 Aston Villa	Stoke	0	-	1	Aston Villa
2	Stoke City 2-4 Stoke city	Stoke City	2	-	4	Stoke city
3	man utd 10-2 Arsenal	man utd	10	-	2	Arsenal
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(A1,FIND("-",A1)-3))
C1	=MID(A1,FIND("-",A1)-2,2)
E1	=MID(A1,FIND("-",A1)+1,FIND(" ",A1,FIND("-",A1))-FIND("-",A1)-1)
F1	=MID(A1,FIND(" ",A1,FIND("-",A1))+1,99)

 

[ronie85]

That worked!! Thanks a lot! Plus I'm pleased that United beat Arsenal 10-2 lol

 

[Momentman]

Well, the "10" was only to show that the formula works with double figures

Glad to help