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

jack109

Board Regular
Joined
May 10, 2020
Messages
78
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1
2TIMESFIRSTSECOND
323 832383
417 451745
533 43 683368
6909090
7777777
810 611061
958 765876
10454545
1131 523152
12797979
1300
14161616
15141414
1648 904890
1724 492449
18262626
193 9339
2052655
Sheet1
Cell Formulas
RangeFormula
D3:D20D3=NUMBERVALUE(LEFT(B3,2))
E3:E20E3=NUMBERVALUE(IF(ISNUMBER(VALUE(RIGHT(B3,4))),RIGHT(B3,4),RIGHT(B3,3)))
 
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
BCDEFGHIJKLM
1
2GOAL TIMESFIRST GOALSECOND GOAL
3HOMEAWAYHOMEAWAYTIMETEAMHOMEAWAYTIMETEAM
423 8323 23HOME83 83HOME
517 4533173317HOME4545HOME
633 43 683333HOME4343HOME
7909090HOME
87717 40771717AWAY4040AWAY
910 6127102710HOME6161HOME
1058 7664586458HOME7676HOME
114540 70454040AWAY7070AWAY
1231 5215311515AWAY5252HOME
13797979HOME
14
151682 90168216HOME9090AWAY
161436143614HOME
1748 904848HOME9090HOME
1824 499 902499AWAY499049HOME
192661 70 75266126HOME7070AWAY
203 6 8933HOME66HOME
Goals (2)
Cell Formulas
RangeFormula
E4:F20E4=IF(B4:B20="","",--TEXTBEFORE(B4:B20," ",,,1))
G4:G20,L4:L20G4=BYROW(E4:F20,LAMBDA(r,INDEX(SORT(r,,,1),1)))
H4:H20,M4:M20H4=BYROW(E4:G20,LAMBDA(r,IF(MAX(r),XLOOKUP(INDEX(r,3),r,E3:G3),"")))
J4:K20J4=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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top