# Converting units in worksheet

## Recommended Posts

We have window schedule (worksheet database type) reporting dimensions in feet and inches but would like to have it also in metric units as cm, m...Is there formula to do the convert? We have looked into worksheet functions and found 'ToUnit' but not luck so far to make it work.

Primary file units are feet and inches.

Pavol

If you only need the metric units for display and don't need to do calculations on them then the =txt() function should be able to do what you want.

Assuming your database header is in Row 3 and the value you want to convert is in column C, then

=txt(C3, 'Centimeters', '0.00', 'Units', 'ThousandsSeparator', 'TrailingZero', 'LeadingZeroes')

should return the value as a text string in centimeter, with two decimal places of accuracy, using the default thousands separator and using trailing and leading zeros'

The full reference for the txt function is on the Developer Site.

Notice how column C is Right Aligned indicating that it is a number, while column D is Left Aligned indicating it is text.  So you won't be able to do math on the values in column D.

If you need to do math on the metric values, you should be able to wrap a version of the txt() function that does not include the units or leading/trailing zeros and no thousands separators in a Value function to get a number.

=Value(txt(C3,'0.0000','centimeters'))

HTH

Thanks a lot @Pat Stanford for both options. They work really great!

It took me some time to realise that I probably have to adjust some notation in order to make it work for me ...  but this second sentence from Worksheet Functions was there to help :-)
(In European notation, the decimal "." should be "," and the separator "," should become ";".)

Thanks!

Edited by drelARCH

Maybe I should add Location to my request for people to include OS and VW Version to their signatures.  Then I might remember more often to make the Comma/Semicolon speech for worksheet functions. 😉

4 minutes ago, Pat Stanford said:

Maybe I should add Location to my request for people to include OS and VW Version to their signatures.  Then I might remember more often to make the Comma/Semicolon speech for worksheet functions. 😉

😀

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×

• KBASE
• #### MARIONETTE

×
• Create New...