Can i make custom function which changes the format of the cell with the formula

Hello friends, am creating a custom function for my sheet,
Call this function mm2cm, which will convert millimeters to centimeters.

Now say the value in cell A1 is 50.00 mm and i enter =mm2cm(A1) in cell B1

The result in B1 is 5,

i want the value in B1 to show as 5.00 cm

meaning as if it has been formatted to 0.00 cm

Can anyone help me with this

My VBA Code so far is as follows

Please Login or Register  to view this content.

Thanks for your reply, i know i can do that, but was looking to automize the process with the function only

As Trevor says, a function cannot affect the formatting of a cell, so you will have to return a text value with the "cm" tagged on to the end of the number. However, that could affect other calculations elsewhere in the workbook if they rely on the cm value being a number.

Hope this helps.

Pete