A large quantity of data has been captured with units of measurement included in same cell as values. e.g. 65 kg or 1.2 metres



I have tried =AVERAGE(VALUE(LEFT(A13:A66,)))

But get a #VALUE error.

Please can someone explain how to property use the VALUE and LEFT functions or provide the correct formula.

Something like =(SUMPRODUCT(--LEFT(A1:A2,FIND(" ",A1:A2))))/COUNTA(A1:A2)

( adapt range as needed)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Can't get that SUMPRODUCT formula to work Pecoflyer.

If helper cells are acceptable I would put in B13 the formula:

LOOKUP(9^9,--LEFT(A13,ROW($1:$100))) and copy down to B66 and then use =AVERAGE(B13:B66).

If helper cells are not acceptable the following array formula seems to work:

=AVERAGE(IF(ISNUMBER(--MID($A$13:$A$66,1,FIND(" ",$A$13:$A$66)-1)),--MID($A$13:$A$66,1,FIND(" ",$A$13:$A$66)-1)))


Edit: My apologi to Pecoflyer. SUMPRODUCT formula does work if no empty cells, otherwise it doesn't.