Formula to find score by counting numbers in a cell

jack109

Board Regular
Joined
May 10, 2020
Messages
70
Office Version
  1. 365
Platform
  1. Windows
home_winlay_away (10).csv
ABCDE
1goal timesdesired result
2homeawayhomeaway
324 624521
4764811
530 754921
64832 35 41 6214
74 1902
815 6102
9721711
106130 52 5413
1100
129 3212 5122
138 423 6822
149010
1545 5967 7822
168301
1719 36 4403
18753511
19521211
Sheet1



Hi

Is there a formula that can count the numbers in a cell in order to work out a score? So instead of manually inputting the scores in col D & E a formula would work it out.

Cheers
 
In D3,try
Excel Formula:
=LEN(A3)-LEN(SUBSTITUTE(A3," ",""))+1*(A3<>"")
pull one right then down
 
Upvote 0
Enter this formula in cell D3 and copy down to E19:
Excel Formula:
=IF(A3="",0,LEN(A3)-LEN(SUBSTITUTE(A3," ",""))+1)
 
Upvote 0
Solution
Another option
Excel Formula:
=IF(A3="",0,ROWS(TEXTSPLIT(A3,," ")))
 
Upvote 0
Another option in D3 only
Excel Formula:
=MAP(A3:B19,LAMBDA(m,IF(m="",0,ROWS(TEXTSPLIT(m,," ")))))
 
Upvote 0
Last two work perfect, thank you.

athurbr yours works fine unless there is no numbers in a cell where it returns 1 instead of 0.
 
Upvote 0
You are welcome.
Glad we were able to help!
 
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