Extracting numbers from text and then adding together

[Rich N]

Hi,
Sorry for posting what seems like such a ridiculously simple query, but despite spending several hours poring through old MrExcel message boards I'm afraid I still need help.

I am using Excel to collate the averages for my cricket team. All the players are listed in column A, followed by their scores for each individual game throughout the year and ending with a total runs column for the season.

My problem arises when I have to give an indication that a player scored runs but was "not out", which impacts on the overall averages because a player who is not out is credited with the runs scored but not charged with an innings. So a player who made 15 not out is credited with "15no" in my workbook.

As a simple example: Player's name (Barry Smith) appears in A1, then his five scores: 23, 15no, 35, 125no, 2 in cells A2:A6. SUM($A2:$A6) entered in A7 ignores the cells that also include text (the "nos") and gives me a total of 60. Is there a formula I can enter in A7 to disregard the "nos" and just read the digits in those cells to come up with the grand total of 200? The run totals can be 1, 2 or 3 digits and I can split the "no" from the number (i.e. "15 no" instead of "15no") if this makes things easier.

Many thanks.
Richard Neale.

 

[ExcelChampion]

=SUM(IF(RIGHT($A$2:$A$6,2)="no",--(LEFT($A$2:$A$6,LEN($A$2:$A$6)-2)),$A$2:$A$6))

Confirmed w/ Ctrl+Shift+Enter

 

[Jonmo1]

I think you will have to add a collumn to remove the no from the cells...

if your numbers are in a2:a6
then collumn b2:b6 should contain this formula

=LEFT(A2,LEN(A2)-2)
=LEFT(A3,LEN(A3)-2)
etc...

and do the sum for collumn B
=sum(B2:B7)

 

[ExcelChampion]

My formula works just fine without having to first extract the numbers from the text.

 

[Peter_SSs]

Rich N

Welcome to the Mr Excel board!

Your post was a little confusing about layout but see if this helps.

Formula in G2 (copied down):
=SUMPRODUCT(--(SUBSTITUTE("0"&B2:F2,"no","")))
Formula in H2 (copied down):
=IF(COUNT(B2:F2)=0,"-",G2/COUNTIF(B2:F2,">=0"))

Mr Excel.xls
	A	B	C	D	E	F	G	H	I
1	Player	Game 1	Game 2	Game 3	Game 4	Game 5	Total	Average
2	Tom	23	15no	35	125no	2	200	66.66666667
3	Tim	12	45	0	9	8	74	14.8
4	Ted	15no	12no		0no		27	-
5	Todd	0	0	0no	0	0	0	0
6	Trev	135	18no		15		168	84
7
Cricket Averages

 

[Rich N]

Hi guys, thank you for your swift responses.

ExcelChampion, you are a star. You've sorted in minutes what has been baffling me for days!

I will be supporting the Tigers in tonight's World Series as a thank you.
Good luck!

 

[ExcelChampion]

I believe the OP said his set up was this:-

Book1
	A	B	C	D
1	BarrySmith
2	23
3	15no
4	35
5	125no
6	2
7	200
Sheet2

If that is the case, then my formula will do exactly what he asked.

Edit: Great! You got it working! GO TIGERS!!!

 

[Jonmo1]

ExcelChamp,

when I put your formula in my sheet, it returns #VALUE!

But when I bring up the function wizard with your formula in it, it calculates and works.

Something wrong?
Using Excel 2002 and XP SP2

 

[ExcelChampion]

When you Enter the formula, don't just hit Enter...hold down your Ctrl and Shift keys and then press Enter.

HTH!

 

[Peter_SSs]

I believe the OP said his set up was this:-
..
If that is the case, then my formula will do exactly what he asked.

1. The OP also said "All the players are listed in column A, followed by their scores for each individual game throughout the year and ending with a total runs column" which to me suggested a layout more like what I posted because it did have a total runs column. However, only the OP can ultimately decide what layout they have and what suggestions they wish to adopt.

2. Your formula certainly does what you suggested and I certainly did not challenge that. I was merely suggesting an alternative that requires less function calls, is shorter to enter, does not require the Ctrl+Shift+Enter that puts some users off and makes use of the SUMPRODUCT function that I understand is generally slightly faster than an equivalent array formula.

3. I also addressed the 'averages' issue that was not specifically asked but alluded to in the OP's third paragraph.