How to sum a series of numbers in alphanumeric strings - in a single formula?

[michaelg]

Team,

Working on a roster, to allow people to (a) specify a shift type and (b) record TOIL (time off in lieu) gained or taken in the same cell.

Basic layout for the roster is;

Name | 1Jan | 2Jan | etc

The "codes" for the shifts can be 1 or 2 characters i.e. "D" = dayshift, "CN" covering nightshift.

People can record TOIL by adding a number after the shift code i.e. D3 = earned/gained 3 hours of TOIL. "D-3" spent 3 hours of TOIL.

I can simply create a formula that (a) strips the numbers from the string (b) detect if a "-" is used and do this for all 31 days of the month, and then total all 31 cells.

But is there a way to sumtotal all 31 days using a single cell formula using arrays?

regards
Michael

 

[michaelg]

Team,

Maybe something like...

=SUM(IFERROR(VALUE(SUBSTITUTE(A2:A32," in","")),""))

but replace " in" with all the code combinations(?) i.e. D N CD CN etc?

regards
Michael

 

[michaelg]

Team,

=SUM(IFERROR(VALUE(SUBSTITUTE(SUBSTITUTE(C35:AG35,"D",""),"N","")),""))

Looks like nested substitute works, but I have 21 shift codes, is there anyway to avoid nesting and use some sort of array constant?

regards
Michael

 

[KolGuyXcel]

Looks like all the 31 cells have only 1 or may be 2 digits at most AT THE END of the alphanumeric value in each cell. If so, there's a different approach to get the SUM.

Try: (as array formula, using CTRL+SHIFT+ENTER)
=SUM(IFERROR(VALUE(RIGHT(C35:AG35,2)),VALUE(RIGHT(C35:AG35,1))))

 

[michaelg]

[SOLVED]

I learned I can replace the "old text" and "new text" parameters with ranges. As I already had my list of codes in a column on another sheet as a key, I've used this as the input for my "old text" input.

{=SUM(IFERROR(VALUE(SUBSTITUTE(C35:AG35,Key!A3:A23,"")),""))}

So,

- C35:AG35 = the 1-31 days of the month for a particular person for a particular month.
- Key!A3:A23 = are the 21 shift codes I need to blank out which were in a key anyway.

Entered as CTRL+SHIFT+ENTER

End result, users can enter things like N6 on one day and D-6 on other day. And the formula will simply SUM 6 and -4 together to produce the result 2.

Hope this helps others.

regards
Michael