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.

 

[Peter_SSs]

.. 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?

Sure, though couldn't a goal scored in say minute 47 actually be in the first half (extra time)?

jack109.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
Goals (4)

Cell Formulas
Range	Formula
S5:S21	S5	=BYROW(P5:P21,LAMBDA(r,LET(g,--TEXTSPLIT(r," "),IFERROR(TEXTJOIN(" ",,FILTER(g,g<=45,"")),""))))
T5:T21	T5	=BYROW(P5:P21,LAMBDA(r,LET(g,--TEXTSPLIT(r," "),IFERROR(TEXTJOIN(" ",,FILTER(g,g>45,"")),""))))
V5:V21	V5	=BYROW(Q5:Q21,LAMBDA(r,LET(g,--TEXTSPLIT(r," "),IFERROR(TEXTJOIN(" ",,FILTER(g,g<=45,"")),""))))
W5:W21	W5	=BYROW(Q5:Q21,LAMBDA(r,LET(g,--TEXTSPLIT(r," "),IFERROR(TEXTJOIN(" ",,FILTER(g,g>45,"")),""))))
Dynamic array formulas.

 

[jack109]

Sure, though couldn't a goal scored in say minute 47 actually be in the first half (extra time)?

Yes but the data source will record this as 45, anything 46+ denotes a goal in the second half.

Thank you very much, you have certainly shaped my sheet and made everything so easier and hopefully I will learn from this help.

Last question, I promise lol. Does defining the range in the formulas make excel calculate much quicker than say dragging copying formulas down or is just to save time in copying and dragging?

thanks

 

[Peter_SSs]

I wouldn't say that the calculation is much faster, especially over relatively small ranges but it does save the copy or drag.
It can also mean that you do not have to adjust if data is subsequently added or removed.
Take this smaller sample like your data. Currently, it would be like this

jack109.xlsm
	P	Q	R	S
3	GOAL TIMES			HOME
4	HOME	AWAY		FIRST
5	23 83			23
6	17 45	33		17 45
7	33 43 68			33 43
8
9	16	82 90		16
10	14	36		14
Goals (5)

Cell Formulas
Range	Formula
S5:S10	S5	=BYROW(P5:P10,LAMBDA(r,LET(g,--TEXTSPLIT(r," "),IFERROR(TEXTJOIN(" ",,FILTER(g,g<=45,"")),""))))
Dynamic array formulas.

However, if we thought that more results might be added later we could do this right from the beginning
=BYROW(P5:P20,LAMBDA(r,LET(g,--TEXTSPLIT(r," "),IFERROR(TEXTJOIN(" ",,FILTER(g,g<=45,"")),""))))
The same actual results are returned and the rows down to row 20 appear blank but if data is added into any of those rows the results will automatically appear without changing the original formula (or dragging anything down further)

An issue with the above though is that those rows from 11 to 20 are actually occupied by the formula results and if the sheet was printed those empty rows would be included in the printout.

To improve that a new function TRIMRANGE has been introduced - you may or may not yet have it but it will be coming to all 365 users eventually.
It could be used like this
=BYROW(TRIMRANGE(P5:P20,2),LAMBDA(r,LET(g,--TEXTSPLIT(r," "),IFERROR(TEXTJOIN(" ",,FILTER(g,g<=45,"")),""))))
Used like this, any empty rows at the bottom of the P5:P20 range will be ignored and the formula will only spill down the required number of rows (& the print range would only extend to the bottom of the actual data) ..

.. but if new data is added below they extra result(s) will appear without altering the formula.

Better still is that there is also a shorthand way of writing the TRIMRANGE function ..

 

[jack109]

Thanks, I didn't realise that and I've just been tinkering using SUM with BYROW & TRIMRANGE, in other parts of the sheet. I didn't know that you could also use like that.

Once again, thank you for effort, time and solutions. I really appreciate it.

 

[Peter_SSs]

You're very welcome.