Jump to content

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.


Thanks for any advice.


Link to comment

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.



  • Like 2
Link to comment

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.





  • Like 1
Link to comment

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 ";".)



Edited by drelARCH
  • Like 1
Link to comment
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. 😉


Glad you figured it out.



Link to comment

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.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

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

  • Create New...