How to split text and numbers in excel

[tbones]

Hi all,
I am having trouble splitting text and numbers in excel.
I have managed successfully to separate the text before the numbers however when I try to separate the number ( which is a time) from the text to the right it comes up all wird . I have got a spreadsheet that I am using .
On the spreadsheet you have two teams playing each other with a time in the middle. I want to separate the two teams into two columns without the time being added

If you look at what i have tried so far it will be clear where the issue is.

Any advice would be great
Thank you

Separate text and numbers

 

[kweaver]

Here's a possible solution:

Book10
	A	B	C
1	Blackburn Rovers15:00West Bromwich Albion	Blackburn Rovers	West Bromwich Albion
2	Bristol City15:00Preston North End	Bristol City	reston North End
3	Middlesbrough15:00Millwall	Middlesbrough	ough15:00Millwall
4	Norwich City15:00Hull City	Norwich City	ty15:00Hull City
5	Plymouth Argyle15:00Huddersfield Town	Plymouth Argyle	00Huddersfield Town
6	Stoke City15:00Rotherham United	Stoke City	therham United
7	Swansea City15:00Birmingham City	Swansea City	0Birmingham City
8	Watford15:00Queens Park Rangers	Watford	ark Rangers
9	Leicester City12:00Coventry City	Leicester City	12:00Coventry City
10	Leeds United14:30Cardiff City	Leeds United	14:30Cardiff City
11	Sunderland17:00Ipswich Town	Sunderland	00Ipswich Town
12
13
14	Blackburn Rovers	West Bromwich Albion
15	Bristol City	Preston North End
16	Middlesbrough	Millwall
17	Norwich City	Hull City
18	Plymouth Argyle	Huddersfield Town
19	Stoke City	Rotherham United
20	Swansea City	Birmingham City
21	Watford	Queens Park Rangers
22	Leicester City	Coventry City
23	Leeds United	Cardiff City
24	Sunderland	Ipswich Town
Sheet1

Cell Formulas
Range	Formula
A14:A24	A14	=TEXTBEFORE(A1:A11,SEQUENCE(10,,0),,,1)
B14:B24	B14	=RIGHT(A1:A11,LEN(A1:A11)-(5+LEN(A14#)))
Dynamic array formulas.

 

[kweaver]

Since there are always times, you won't need those 3 commas and the 1 after the sequence.
I copied this from another need I had which just took the values from the left and sometimes there weren't any numbers after the text.

 

[tbones]

Here's a possible solution:

Book10
	A	B	C
1	Blackburn Rovers15:00West Bromwich Albion	Blackburn Rovers	West Bromwich Albion
2	Bristol City15:00Preston North End	Bristol City	reston North End
3	Middlesbrough15:00Millwall	Middlesbrough	ough15:00Millwall
4	Norwich City15:00Hull City	Norwich City	ty15:00Hull City
5	Plymouth Argyle15:00Huddersfield Town	Plymouth Argyle	00Huddersfield Town
6	Stoke City15:00Rotherham United	Stoke City	therham United
7	Swansea City15:00Birmingham City	Swansea City	0Birmingham City
8	Watford15:00Queens Park Rangers	Watford	ark Rangers
9	Leicester City12:00Coventry City	Leicester City	12:00Coventry City
10	Leeds United14:30Cardiff City	Leeds United	14:30Cardiff City
11	Sunderland17:00Ipswich Town	Sunderland	00Ipswich Town
12
13
14	Blackburn Rovers	West Bromwich Albion
15	Bristol City	Preston North End
16	Middlesbrough	Millwall
17	Norwich City	Hull City
18	Plymouth Argyle	Huddersfield Town
19	Stoke City	Rotherham United
20	Swansea City	Birmingham City
21	Watford	Queens Park Rangers
22	Leicester City	Coventry City
23	Leeds United	Cardiff City
24	Sunderland	Ipswich Town
Sheet1

Cell Formulas
Range	Formula
A14:A24	A14	=TEXTBEFORE(A1:A11,SEQUENCE(10,,0),,,1)
B14:B24	B14	=RIGHT(A1:A11,LEN(A1:A11)-(5+LEN(A14#)))
Dynamic array formulas.

Thats magic, that has done the trick for sure. Thank you ever so much it is much appreciated

 

[Fluff]

Another option for the 2nd team is

=TEXTAFTER(A1:A11,SEQUENCE(10,,0),-1)