How to parse specific string from an excel cell (No numbers in cell)?

Question

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?

barry houdini · Accepted Answer · 2013-01-07 10:32:55Z

up vote 2 down vote accepted

With data in A2 try this formula in B2

=MID(A2,2,FIND("-",A2)-2)

and in C2

=SUBSTITUTE(REPLACE(A2,1,FIND("-",A2),""),"]","")

edited Jan 7 at 10:32

answered Jan 7 at 8:32

barry houdini
4,341128

	the =mid formula is returning the right text but in all the results the first letter is missing for example helsea, iverpool, ewcastle etc – Aasim Azam Jan 7 at 15:14
	I changed the =MID(A2,2,FIND("-",A2)-2) formula to =MID(A2,1,FIND("-",A2)-2) – Aasim Azam Jan 7 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),"")` – barry houdini Jan 7 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 – Aasim Azam Jan 7 at 16:13

Aasim Azam · Answer 2 · 2013-01-07 06:31:17Z

up vote 1 down vote

To do this I used the Text to columns feature under the Data tab and used "-" as the delimiter.

answered Jan 7 at 6:31

Aasim Azam
526

2 Answers