# Extracting numbers from text and then adding together

#### Rich N

##### New Member
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.

### 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
=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
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
My formula works just fine without having to first extract the numbers from the text.

#### Peter_SSs

##### MrExcel MVP, Moderator

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

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!!!

#### Jonmo1

##### MrExcel MVP
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
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
..
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.

Replies
18
Views
2K
Replies
9
Views
640
Replies
6
Views
265