How to use the number in a cell, which is mixed

[Nerdio]

I have a number of columns which are imported data and include numbers and text in the cells;

Eg.

5.06 km	10.8 km/h
6.5 km	11 km/h
6.04 km	11.1 km/h

<tbody>
</tbody>

I want to be able to do things with the numbers like, sum a range, find a range average, find max and min. All of which I am quite happy with, BUT... The cells have text in as well as numbers, so it does not work as I want. How do I make excel treat the cell as numeric, and keep displaying the text? Or, how do I extract the number from the cell for the purposes of SUM etc.

I don't really want to have to manipulate the cells, and split the text out, as this will become time consuming on the import.

Thanks in anctipation

 

[VoG]

To extract a number

=LEFT(A1,FIND(" ",A1)-1)+0

 

[Nerdio]

To extract a number

=LEFT(A1,FIND(" ",A1)-1)+0

Thanks for the quick reply.

I don't think I had considered what the solution might look like, so I am now a little unsure how to incorporate this into a range. For example, how would I incorporate that into

SUM(A1:A10)

Many thanks in anticipation

 

[VoG]

You cannot (easily) do that.

I should have been more explicit - use a helper column to extract the numbers then do your SUM on that column.

 

[Nerdio]

You cannot (easily) do that.

I should have been more explicit - use a helper column to extract the numbers then do your SUM on that column.

Thanks for that clarification, this is what I was wondering if I might end up doing. Thanks for your time, it is much appreciated.

 

[robw]

Assuming the cells in your example are in A1:B3

The first column numbers are summed by =SUMPRODUCT(--SUBSTITUTE(A1:A3,"km",""))
and the second by =SUMPRODUCT(--SUBSTITUTE(B1:B3,"km/h",""))


...it does, though, rely on the units remaining consistent in the columns

Cheers
Rob

 

[Nerdio]

Assuming the cells in your example are in A1:B3

The first column numbers are summed by =SUMPRODUCT(--SUBSTITUTE(A1:A3,"km",""))
and the second by =SUMPRODUCT(--SUBSTITUTE(B1:B3,"km/h",""))


...it does, though, rely on the units remaining consistent in the columns

Cheers
Rob

This is great, thanks. I must confess that my Excel skills are not as good as I would like, so I am really not quite sure how this all works. This is what I think...

SUBSTITUTE replaces "km" with "", so we are left with a number.

I am not sure what the -- does in front of it though, but SUMPRODUCT works on a 2D range.

I would like to understand what the '--' does really to see if I can adapt this to work with MAX(), and MIN().

Many thanks again.

 

[robw]

the -- forces excel to convert the character string it is left with after doing the substitution (it really is still a char string at this point), into a number....because SUMPRODUCT works on numbers rather than character strings. You can get the same sort of effect using +0, as in ...
=SUMPRODUCT(SUBSTITUTE(A1:A3,"km","")+0)
...but I think -- looks cooler .

I suspect you may have problems getting MIN and MAX to work...but good luck with it
Cheers
Rob