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]

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.

I think your set up, if not already adopted by the OP, should be. Good job!

 

[Rich N]

Hi Peter,
just wanted to also thank you for all your help in solving my problem. In retrospect I've taken XLChampion's final piece of advice and gone with your solution because it is easier for this novice Excel user to understand.

The solutions are fantastic, guys, and much appreciated. It's hard to believe that after days of unsuccessful trial and error on my part I am now spoilt for choice. I'll know exactly where to come next time I'm stumped!

Thanks again and kind regards.
Rich.

P.S. No worries if you are too busy, but it would be nice to know for my future use the purpose of the two successive hyphens "--" as used in your formulae as I had not come across them before.

 

[ExcelChampion]

The double neg applies a mathematical operation on text that looks like a number thus converting it to an actual number.

As an experiment, in a cell, type '10 and then in the cell next to it apply the SUM function, for example, =SUM(A1). It should return 0 because '10 is text. Now, where you had the sum function type, =SUM(--(A1)). This should return 10.

This will also work also on =--(TRUE) which will return 1 and =--(FALSE) which returns 0.

 

[Rich N]

That's opened up a whole new can of worms

XLChampion, thank you for clarifying -- and sorry about the Tigers...

Unfortunately, the solution to my earlier headache has spawned two new problems!

I've just spent three hours messing with these without success and having admitted defeat have come crawling back begging for more help (sorry, yes, I do feel inadequate)!

Problem 1

In my tables I specify a batsmen's highest score for the season using the simple formula: =MAX($J5:$AG5)

This now obviously ignores scores followed by "no", standing for "not out". Hence in a list of scores of 34, 16, 14, 111no and 56, the highest score is returned as "56", rather than the correct "111" and even more preferably "111no".

I've been trying a combination of the MAX formula above, with the (--(SUBSTITUTE("0"&J5:AG5,"no",""))) Peter suggested earlier, but to no avail.

Problem 2

In my tables I specify how many 50s a batsman scored during the season (that is scores of more than 49 but less than 100).

I use this function, probably because I know no better, but it works:
=COUNTIF($J5:$AG5,">49")-COUNTIF($J5:$AG5,">99")

Unfortunately, again, the calcuations fail to count scores between 50 and 99 that are followed by "no". Hence scores of 53, 56no, 68no, 72 would only be tallied as two 50s instead of the correct four.

Gentlemen, any thoughts would be greatly appreciated.

Regards,
Rich.

 

[ExcelChampion]

Re_ Problem 1
=MAX(--(SUBSTITUTE(J5:AG5,"no","")))

confirmed w/ Ctrl + Shift +Enter

Re: Problem 2
=SUMPRODUCT(--(--(SUBSTITUTE(J5:AG5,"no",""))>=50),--(--(SUBSTITUTE(J5:AG5,"no",""))<=99))

Adjust ranges as necessary.

 

[Domenic]

Using Peter's approach...

For the first part:

=MAX(SUBSTITUTE("0"&$J5:$AG5,"no","")+0)

or

=INDEX($J5:$AG5,MATCH(MAX(SUBSTITUTE("0"&$J5:$AG5,"no","")+0),SUBSTITUTE("0"&$J5:$AG5,"no","")+0,0))

The first one returns the number only, whereas the second one returns 'no' along with the number. Note that both these formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

For the second part:

=SUMPRODUCT(--(SUBSTITUTE("0"&$J5:$AG5,"no","")+0>=50),--(SUBSTITUTE("0"&$J5:$AG5,"no","")+0<100))

Hope this helps!

 

[ExcelChampion]

Domenic,
I'm curious...why concatenate 0?

 

[Peter_SSs]

Domenic,
I'm curious...why concatenate 0?

It stops the formula failing if there are blank cells in the range.

 

[ExcelChampion]

Nice touch!

 

[Rich N]

Absolutely brilliant, I can't thank you both enough!

Unfortunately there are now no excuses for delaying the compilation of 16 seasons' worth of stats for Headliners Cricket Club (Sussex, England).
So I know what I will be doing tomorrow...

Thanks again.
Rich.