Deal with trailing unit in Excel UDFs?

Question

Possible Duplicate:
Using Excel Built-in Functions on top of the UDF

My Excel UDF currently returns a string which consists of numeric data and unit, e.g. 1234 kg. It's straightforward and easy for users to read, but the problem is that it will hinder some basic calculations with Excel built-in functions, like Sum(), Average(), etc.

With the above formatting, i.e. 1234 kg, using formula =Sum() will not bring the correct result unless the trailing unit kg is removed.

So my questions, is there any way that we could still allow calculations while keeping the units there? Can Excel identify 1234 kg as a numeric value instead of a string value?

Answer 1

You can extract the number with string manipulation
=VALUE(LEFT(A1,FIND(" kg",A1)))
will return 1234 from "1234 kg" in cell A1
but this means you have to manipulate the contents of the UDF cell

I would be simpler if:

• your UDF returned a numeric value (1234)
• then you apply a custom format to the cells with the UDF to add the " kg" while keeping the cell as a number (for example Custom Format of #### " kg")