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)))

 

[Peter_SSs]

Would this be any use?

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

Cell Formulas
Range	Formula
D3:E20	D3	=LET(a,TAKE(TEXTSPLIT(B3&" "," "),,2),IFERROR(--a,a))
Dynamic array formulas.

 

[jack109]

Yes, thats brilliant thank you

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[jack109]

sorry to be a pain but is there a way that this can be done using two formulas instead? Im encountering a problem not with the formula but how Im trying to get use the data

 

[jack109]

Here is more data which will show what I'm trying to achieve. Basically I want to know who scored the first and second goals, I have no problem achieving that with the first but its when Im trying to determine who scored the second goal if any. The second goal columns I & J have been entered manually to show my expected result.

Maybe the formula I've marked as the solution is the answer, maybe I looking at using it in the wrong way?

Any help would once again be greatly appreciated.

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

Cell Formulas
Range	Formula
E4:F20	E4	=IF(B4="","",NUMBERVALUE(LEFT(B4,2)))
G4:G20	G4	=IF(AND(E4="",F4=""),"",IF(E4<F4,"HOME","AWAY"))
K4:K20	K4	=IF(AND(I4="",J4=""),"",IF(I4>J4,"HOME","AWAY"))

 

[jack109]

After some searching, I have come up with this, which seems to be working. Would be interested if there are "cleaner" formulas to sue or if I've made any mistakes.

cheers

goaltimes.xlsm
	A	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
Sheet1

Cell Formulas
Range	Formula
E4:F20	E4	=IFERROR(NUMBERVALUE(INDEX(TEXTSPLIT(B4,," "),1)),"")
G4:G20,L4:L20	G4	=IF(MIN(E4:F4)=0,"",(MIN(E4:F4)))
H4:H20	H4	=IF(AND(E4="",F4=""),"",IF(E4<F4,"HOME","AWAY"))
J4:K20	J4	=IFERROR(NUMBERVALUE(INDEX(TEXTSPLIT(B4,," "),2)),"")
M4:M20	M4	=IF(L4="","",IF(L4=J4,"HOME","AWAY"))

 

[Scott Huish]

You could always use -- instead of NUMBERVALUE

 

[Peter_SSs]

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.