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.