Fomulas with tex and numbers in same cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone help me with a fomula that contains numbers and text in the same
cell

example ct/2.5 ct/3.5 ct/6 12 8 acc/10

I need to add all these cells to get total hours...
 
Suppose your text/number combinations are in column A1- A10
Ifit is mote then alter the $1:$10 bit of the formula.

In B1 next to the first number type:-

=1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$10),1)),0),255)

It's an array formula so enter with CTRL+Shift+Enter.

Drag down and it will extract the numeric part of the cell which can then
simply be summed.

Mike
 
With your posted data in A1:F1
ct/2.5 ct/3.5 ct/6 12 8 acc/10

Try this:
G1: =SUMPRODUCT(--("0"&MID(A1:F1,FIND("/",A1:F1&"/")+1,255)))+SUM(A1:F1)

In that example, the formula returns 42.

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Back
Top