Seperating Numbers from Letters in Excel

[Guest]

Okay here's a challenge (well it was for me!)

I am creating a staff planner and need to count part-time staff hours.

the data that is entered has two variable
a7.5
a = type of absence;
T = training
S = sick
AL = Annual Leave
L = Lieu time
C = compassionate
CA = Carer's Leave

the number = the number of hours booked off

How do I seperate the two to make an additional column recognise the initial
letter and then total all occurences of that letter.

e.g. (Cell N11)= "AL5.5" + (Cell N15) = "AL2.5" would show in the Annual
Leave column as 8 hours leave (I would then reduce a further total column to
by the total)

If you need to view the planner go to www.tbgb.co.uk/plannerv1.xls

Any suggestions to improve on this would be most welcome.

Tel

 

[Guest]

I'm sure there is a beeter formula for this & somebody willoffer it soon. In
the meantime, this works SO LONG AS your letter codes are all in caps

Put your codes in B1:B6, then put this formula in B1 & copy it down (adjust
the references to N1:N4 to match your data range)

=SUMPRODUCT(--(LEFT($N$1:$N$4,LEN(B2))=B2),IF(NOT(ISERROR(SUBSTITUTE($N$1:$N$4,B2,"")*1)),SUBSTITUTE($N$1:$N$4,B2,"")*1))

 

[Guest]

Sorry, that was supposed to be "put the formula in C1 & copy down"

 

[Guest]

Sorry, been a long day & forgot one important thing. This is an array
formula & needs to be entered by pressing Ctrl-Shift-Enter

 

[Guest]

Many thanks Duke,

Tried copying it down but there's some fixed references here so it looks
like I have to manually adjust all the formulae per cell!!! Ho Hum such
is life lol

Terry

 

[daddylonglegs]

Any suggestions to improve on this would be most welcome.

I couldn't view your spreadsheet, I got an error but I'd suggest that
it would be much simpler if you put the codes and associated hours in
two adjacent cells - then you could use some less complex and more
easily maintained SUMIF formulas, e.g

SUMIF(A$1:A$10,"AL",B$1:B$10)

 

[Biff]

I tried to look at your file but I got an error 404: file not found.

Biff

 

[Peo Sjoblom]

With your data in A2:A11 and the type of absence in B2

=SUMPRODUCT(--(LEFT(SUBSTITUTE($A$2:$A$11,"CA","^^"),LEN(B2))=IF(B2="CA","^^",B2)),--(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(0&SUBSTITUTE($A$2:$A$11,"T",""),"S",""),"AL",""),"CA","^^"),"C",""),"L",""),"^^","")))

having said that you should really put the absence in one cell and the hours
on another, assume the type was in A2:A11, the hours in B2:B11 and the
criteria in C2

=SUMIF($A$2:$A$11,C2,$B$2:$B$11)

you could easily extract the numbers from the range by using

=--MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),1024)

and copy down