formula to add number comined with text
 

i am currently using a basic weekly diary in excel and need a formula that
will count cells which contain numbers along with text

Row 1 - w/c 12/7/09 12/14/09 12/21/09 12/28/09 Total
Col A
days 3mtw 5 1w 4mtwt
13 days


I need formula that will give total of days across each month as above not
sure if countif formula will work for this or not

each cell will have the number of days used in that week followed by initial
of the day I need the formula to count the number that is at the start of the
text e.g 3mtw

is this possible to do?

formula to add number comined with text
 

This array* formula will do it:

=SUM(--(LEFT(B2:E2)))

* An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual <Enter. If you do this
correctly then Excel will insert curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
need to edit the formula you must use CSE again.

Hope this helps.

Pete


On Dec 22, 9:57*am, ian_ni wrote:
i am currently using a basic weekly diary in excel and need a formula that
will count cells which contain numbers along with text

Row 1 - * w/c 12/7/09 * 12/14/09 * * 12/21/09 * *12/28/09 * * * * *Total Col A

days * * * * * * * * *3mtw * * * * 5 * * * * * * * 1w * * * * * * *4mtwt * *
* * * 13 days

I need formula that will give total of days across each month as above not
sure if countif formula will work for this or not

each cell will have the number of days used in that week followed by initial
of the day I need the formula to count the number that is at the start of the
text e.g 3mtw

is this possible to do?