Having units in Google Spreadsheets?

Question

I want to be able to input plain numbers like 200, or 3000 and have the cell display 200g, 3kg, etc... But still be able to add the values in the cells. So if I add the two above I'd get 3.2kg on the result cell.

I suspect this is not possible, but is it?

Answer 1

Does not manage the automatic division by 1000 for kilogrammes but perhaps of some use (better than nothing?)

In New Google Sheets, Format > Number > More Formats > Custom Number Format and Apply:

0,0 g

Answer 2

I found it.

Format > Number > More Formats > Custom Number Format
then enter: . "lbs"

If you want two decimal points then it would be: .00 "lbs"

Putting whatever you want in quotes allows you to create custom units of measurement.

Answer 3

There is an add-on that does this called LegendSheet. However, you don't have the number and the unit in the same cell rather you have to put them side by side. But it handles all the unit conversions such that you could do something like this:

  | A   | B    | C     | D | E                     |
--+-----+------+-------+---+-----------------------+
1 | 3.0 | [k]g | 200.0 | g | =LS_QADD(A1:B1,C1:D1) |

And the result in E1 will be 3.2 [k]g. Note that you have to use the brackets for unit prefixes. There is a help video on on YouTube that shows you can also do subtraction, multiplication and division and raise to a power numbers with units. It is a paid add-on, but you get a good amount of free uses per day that I find to be sufficient for my needs.

Answer 4

you can add to any cell: & "unit"

Answer 5

Sorry to resurect this very old question but I was looking to do the same thing and could not find the answer anywhere. My missus is useless when it comes to multiplying a recipe from her books to cover the number of people she is cooking for so i created a spreadsheet for her to enter the number of people the recipe is aimed at and the number of people se is cooking for and calculate the weights. of course the recipies all use different measurements and I wanted to account for them all.

Here is what i ended up doing...

first format the column you want to enter the values into as plain text

format > number > plain text

This will allow a number without units to work with the following regex functions

add a few columns beside the column you want to enter your value into... in these columns add the formulas -

column C. =VALUE(REGEXREPLACE(B7,"[^[:digit:].]", ""))

column D. =REGEXREPLACE(B7,"[^[:alpha:]]", "")

this will split the entered measurement into its value and unit components.

now create a conversion table on a separate sheet that contains the following -

unit	conversion rate	base unit	base increment	multiplier
g	1	g	kg	1000
kg	1000	g	kg	1000
lb	454	g	kg	1000
lbs	454	g	kg	1000
oz	28	g	kg	1000
ml	1	ml	L	1000
L	1000	ml	L	1000
pt	568	ml	L	1000
qt	1137	ml	L	1000
floz	29	ml	L	1000
tsp	1	tsp	tbsp	3
tbsp	1	tbsp	tbsp	1
...

add whatever conversions you want on here

back on the main sheet continue adding columns to do the lookups

column E. =INDEX(Sheet2!$A$2:$C$99,MATCH(D7,Sheet2!$A$2:$A$99,0),3) 

this gets the base unit

column F. =if(E7="",1,INDEX(Sheet2!$A$2:$C$99,MATCH(D7,Sheet2!$A$2:$A$99,0),2))

gets the conversion rate (defaults to 1 if the unit entered is unknown)

column G. =F7*C7

Gets the total base qty for the recipe

column H. =round((G7/$K$3)*$K$4)

adjust for the number of people being served (k3 holds the number servings on the recipe, k4 holds the number of servings required)

column I. =if(E7="","",INDEX(Sheet2!$A$2:$E$99,MATCH(D8,Sheet2!$A$2:$A$99,0),4))

column J. =if(E7="",1,INDEX(Sheet2!$A$2:$E$99,MATCH(D7,Sheet2!$A$2:$A$99,0),5))

Gets the values needed to convert to the highest denomination of the measurement

column K. =if(H12/J12 >= 1,H12/J12 & " " & if(I12="",D12,I12), H12 & " " & E12)

this is the actual value we have been looking for. It checks if the conversion to the higher denomination is greater or equal to 1. If so it uses the higher denomination - otherwise it uses the base denomination.

Hide columns C - J and this is what you get -

Recipe Multiplier
recipe servings		4
req servings		6
ingredient	recipe qty	total qty
eggs	2	3
sugar	6 oz	252 g
milk	10 fl oz	435 ml
cinnamon	1 tsp	2 tsp
flour	750 g	1.125 kg