sum numeric data ignore text in same cell (beginner user)

Question

I have a data eg: row 1 to 100 consist of data eg: 1day; 1 day; 5 day; 6 days.

I need to sum up the total of days but the total came out "0".

Please help.

Answer 1

Hi,

Let suppose that your row of data starts in cell A1. Use below formula in B1:

      =SUM((LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3,4,5,6,7,8,9})

Hope that helps.

iliru.

Answer 2

Hi,

See the snip below. The ARRAY formula in A3 is this. see below for how to enter an ARRAY formula.

=SUM(--LEFT(A1:D1,FIND(" ",A1:D1)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter

and not just Enter. If you do it correctly then Excel will put curly brackets

around the formula {}. You can't type these yourself. If you edit the formula

you must enter it again with CTRL+Shift+Enter.

Answer 3

Enter below formula as an array formula  (CTRL+SHIFT+ENTER) in cell B2:

=SUM(--TRIM(IFERROR(LEFT(A2:A10,FIND("d",A2:A10)-1),0)))

Presumed that the alphanumerics in each cell of column A start with a number and are followed by the text "day"/"days" with or without a preceding space (ie. there could be a space or not between the number and text). Note: the formula is to be entered as an array formula by pressing the 3 keys simultaneously (Ctrl, Shift & Enter). Please update the range as required.

Regards,

Amit Tandon

www.globaliconnect.com

Answer 4

Hi MikeH,

I thought the data was in the same cell. Am I wrong?

iliru.

Answer 5

I don't know but I read it as the data are in a row. The OP will let us know in due course.