Formula help - Pulling 3rd & 4th number from string but blank if no 3rd & 4th number

[jack109]

Hi

Im extracting the times of 1st and 2nd goals from times that are a text string. The first formula works fine but the second works unless there is no second goal or if both goals are scored with single digit numbers or single and double digits. Is there a way to amend the formula so that if there is no second goal? The last two rows are an example of when there single and double digit goals causing an issue too.

Any help would be grateful

GOALTIMES.xlsm
	A	B	C	D	E
1
2		TIMES		FIRST	SECOND
3		23 83		23	83
4		17 45		17	45
5		33 43 68		33	68
6		90		90	90
7		77		77	77
8		10 61		10	61
9		58 76		58	76
10		45		45	45
11		31 52		31	52
12		79		79	79
13				0	0
14		16		16	16
15		14		14	14
16		48 90		48	90
17		24 49		24	49
18		26		26	26
19		3 9		3	39
20		5	26	5	5
Sheet1

Cell Formulas
Range	Formula
D3:D20	D3	=NUMBERVALUE(LEFT(B3,2))
E3:E20	E3	=NUMBERVALUE(IF(ISNUMBER(VALUE(RIGHT(B3,4))),RIGHT(B3,4),RIGHT(B3,3)))

 

[jack109]

Here are some alternatives. Some are longer formulas than yours, but none of them need to be copied down the rows. Just put them in the row 4 cells & make sure the ranges in the formulas cover all the data rows.

BTW, is it possible that both teams could score within the same minute? If so, what should happen in the "TEAM" column?

25 02 27.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
1
2	GOAL TIMES			FIRST GOAL					SECOND GOAL
3	HOME	AWAY		HOME	AWAY	TIME	TEAM		HOME	AWAY	TIME	TEAM
4	23 83			23		23	HOME		83		83	HOME
5	17 45	33		17	33	17	HOME		45		45	HOME
6	33 43 68			33		33	HOME		43		43	HOME
7	90			90		90	HOME
8	77	17 40		77	17	17	AWAY			40	40	AWAY
9	10 61	27		10	27	10	HOME		61		61	HOME
10	58 76	64		58	64	58	HOME		76		76	HOME
11	45	40 70		45	40	40	AWAY			70	70	AWAY
12	31 52	15		31	15	15	AWAY		52		52	HOME
13	79			79		79	HOME
14
15	16	82 90		16	82	16	HOME			90	90	AWAY
16	14	36		14	36	14	HOME
17	48 90			48		48	HOME		90		90	HOME
18	24 49	9 90		24	9	9	AWAY		49	90	49	HOME
19	26	61 70 75		26	61	26	HOME			70	70	AWAY
20	3 6 89			3		3	HOME		6		6	HOME
Goals (2)

Cell Formulas
Range	Formula
E4:F20	E4	=IF(B4:B20="","",--TEXTBEFORE(B4:B20," ",,,1))
G4:G20,L4:L20	G4	=BYROW(E4:F20,LAMBDA(r,INDEX(SORT(r,,,1),1)))
H4:H20,M4:M20	H4	=BYROW(E4:G20,LAMBDA(r,IF(MAX(r),XLOOKUP(INDEX(r,3),r,E3:G3),"")))
J4:K20	J4	=LET(a,TEXTAFTER(TEXTBEFORE(B4:B20&" "," ",2,,1)," "),IFERROR(--a,a))
Dynamic array formulas.

Thank you, both scoring in the same minute is possible but I'm not sure If any of the data Im crunching it has happened. If that is the case it would be void and discounted as the source of the data doesn't determine who actually scored first in that scenario. It does happen but not frequent enough that it would skew and results Im looking for in the data.

 

[Peter_SSs]

If that is the case it would be void

If you wanted to allow for the same time for first goal or same time for second goal per team then you could perhaps alter the 'TEAM' column formula like this.

jack109.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
1
2	GOAL TIMES			FIRST GOAL					SECOND GOAL
3	HOME	AWAY		HOME	AWAY	TIME	TEAM		HOME	AWAY	TIME	TEAM
4	23 83			23		23	HOME		83		83	HOME
5	17 45	33 45		17	33	17	HOME		45	45	45	VOID - EQUAL
6	33 43 68			33		33	HOME		43		43	HOME
7	90			90		90	HOME
8	77	17 40		77	17	17	AWAY			40	40	AWAY
9	10 61	10		10	10	10	VOID - EQUAL		61		61	HOME
10	58 76	64		58	64	58	HOME		76		76	HOME
11	45	40 70		45	40	40	AWAY			70	70	AWAY
12	31 52	15		31	15	15	AWAY		52		52	HOME
13	79			79		79	HOME
14
15	16	82 90		16	82	16	HOME			90	90	AWAY
16	14	36		14	36	14	HOME
17	48 90			48		48	HOME		90		90	HOME
18	24 49	9 90		24	9	9	AWAY		49	90	49	HOME
19	26	61 70 75		26	61	26	HOME			70	70	AWAY
20	3 6 89			3		3	HOME		6		6	HOME
Goals (3)

Cell Formulas
Range	Formula
E4:F20	E4	=IF(B4:B20="","",--TEXTBEFORE(B4:B20," ",,,1))
G4:G20,L4:L20	G4	=BYROW(E4:F20,LAMBDA(r,INDEX(SORT(r,,,1),1)))
H4:H20,M4:M20	H4	=BYROW(E4:G20,LAMBDA(r,IF(MAX(r),IF(INDEX(r,1)=INDEX(r,2),"VOID - EQUAL",XLOOKUP(INDEX(r,3),r,E3:G3)),"")))
J4:K20	J4	=LET(a,TEXTAFTER(TEXTBEFORE(B4:B20&" "," ",2,,1)," "),IFERROR(--a,a))
Dynamic array formulas.

 

[jack109]

Thank you , one more thing does excel have the capability to split the data so that any goals scored between 1-45 would go into a first half column and any goals scored between 46-90 into a second half column. Like the example below?

ALERTS-GJL-52.xlsm
	P	Q	R	S	T	U	V	W
3	GOAL TIMES			HOME			AWAY
4	HOME	AWAY		FIRST	SECOND		FIRST	SECOND
5	23 83			23	83
6	17 45	33		17 45			33
7	33 43 68			33 43	68
8	90				90
9	77	17 40			77		17 40
10	10 61	27		10	61		27
11	58 76	64			58 76			64
12	45	40 70		45			40	70
13	31 52	15		31	52		15
14	79				79
15
16	16	82 90		16				82 90
17	14	36		14			36
18	48 90				48 90
19	24 49	9 90		24	49		9	90
20	26	61 70 75		26				61 70 75
21	3 6 89			3 6	89
Sheet1

If not, thank you so much for your assistance, its certainly made my data crunching easier.