I have the following data in cells
1 [Chelsea-QPR]
2 [Liverpool-Sunderland]
3 [NewcastleUtd-Everton]
I want to separate the team names into two different cells. How can I do this?
Add a comment
|
2 Answers
With data in A2 try this formula in B2
=MID(A2,2,FIND("-",A2)-2)
and in C2
=SUBSTITUTE(REPLACE(A2,1,FIND("-",A2),""),"]","")
answered Jan 7, 2013 at 8:32
-
the =mid formula is returning the right text but in all the results the first letter is missing for example helsea, iverpool, ewcastle etc Jan 7, 2013 at 15:14
-
I changed the =MID(A2,2,FIND("-",A2)-2) formula to =MID(A2,1,FIND("-",A2)-2) Jan 7, 2013 at 15:15
-
I was assuming that the brackets were part of the text in those cells - hence why the MID function used 2 not 1, if the brackets are not there then you can also simplify the second formula , SUBSTITUTE is not required so that becomes just
=REPLACE(A2,1,FIND("-",A2),"")Jan 7, 2013 at 16:08 -
I see, I didn't have time to format the text as cells and columns so I did this in a hurry. I understand. Thanks Jan 7, 2013 at 16:13
To do this I used the Text to columns feature under the Data tab and used "-" as the delimiter.