Extracting numbers from text and then adding together

Rich N

New Member
Joined
Oct 27, 2006
Messages
18
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.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
=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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
Aug 12, 2005
Messages
976
My formula works just fine without having to first extract the numbers from the text.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,773
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHI
1PlayerGame 1Game 2Game 3Game 4Game 5TotalAverage
2Tom2315no35125no220066.66666667
3Tim12450987414.8
4Ted15no12no0no27-
5Todd000no0000
6Trev13518no1516884
7
Cricket Averages
 

Rich N

New Member
Joined
Oct 27, 2006
Messages
18
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

Well-known Member
Joined
Aug 12, 2005
Messages
976

ADVERTISEMENT

I believe the OP said his set up was this:-
Book1
ABCD
1BarrySmith
223
315no
435
5125no
62
7200
Sheet2


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

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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
Aug 12, 2005
Messages
976
When you Enter the formula, don't just hit Enter...hold down your Ctrl and Shift keys and then press Enter.

HTH! :)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,773
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,599
Messages
5,541,166
Members
410,543
Latest member
ExcelGlenn
Top