Solved: Dissect result

[wilg]

I have a formula that results in a single cell...

8.2 km / 15 mins

This is referance to a post log time ago on "Get distance"

Is there a way to extract the 8.2 numbervalue out of the cell to sum with other cells? It would be the first 3 digits.

[mikerickson]

What is the formula that results in 8.2 km / 15 mins ?

[shawnhet]

Assuming that the format is consistent throughout, something like this should work:

=LEFT(A1,FIND("km",A1)-2)

Cheers, :)

[wilg]

Hi shawnhet,that gives me the result i'm looking for thanks so much, but it returns it as "8.2" and I cannot sum this in quotes. Is there a way to convert to a num without " "?

[Brandtrock]

Simply multiply the existing formula by 1
=LEFT(A1,FIND("km",A1)-2)*1

OR

Simply add zero to the existing formula
=LEFT(A1,FIND("km",A1)-2)+0

[mikerickson]

What is the formula that results in that string?
It would be more robust to use the original data that feeds the function.

[Brandtrock]

What is the formula that results in that string?
It would be more robust to use the original data that feeds the function.

I would tend to agree with you on that.

If the formula producing the string is concatenating numbers and text to form the final string simply grabbing the original number is preferable to putting it into a string that needs to be searched and then converting it back into a number.

If the formula producing the string is concatenating text to make the final string (ie. taking the string 8.2 km from a cell, adding the " / ", and finally adding the string 15 mins from another cell) then it is STILL preferable to work with the original string although a conversion to numeric form is necessary.

Getting in the routine of dealing with the original data will save headaches along the way.

[wilg]

Hi guys. *1 ,did the job. Thanks so much.

[Brandtrock]

You are certainly welcome.

Would you mind giving us the formula that returned the string (8.2 km / 15 mins) that you were trying to manipulate?