Ok, so here’s the scenario, you want to enter a value like 20Kg (or 44 lb) into an Excel cell. All goes well until you try to multiply the value. Instant disaster. Your number isn’t a number it’s a piece of text and you can’t multiply or add text. You can’t divide or minus text or find the 4th power of it either – you get the idea.. it’s basically a useless entry – pretty but useless.
SO, how do you format a cell so it displays the Kg after the number but so it still treats the entry as a number? I’m so glad you asked!
The answer is to create a custom number format. Click the cell or a range of cells and choose Format > Cells > Number tab. Select the Custom entry and type something like 00.00 “kg” in the text box and click Ok. Now, enter the numbers again as 20 or 40 etc and the kg will appear automatically after the number courtesy of the format – and the number, well it’s still a number so you can add, multiply, divide and do all sorts of other wonderful things to it as you like.
You can also add the text before a number. So, for all those lovely Canada dollars I get paid by the folk at Hub Digital Living where I write a monthly Office column, I type a Custom format like “CAN” 00.00 in my sheet where I record my payments. It’s all so easly when you know how.. and now – you do too..
Thanks Helen for that info.
Its nearly right for me just need to do the formula now.
Thanks again,
Ian
Thank you, Helen.
Just to add, if you need spaces between the value and what’s been added – it can be done with underscores: ___ So, if you want 50 lb, the custom format should look like 50__”lb”.
Cheers!
Another thing to add:
you can specify Colour by putting it in square brackets before a format.
e.g. to have positive values in green, negative values in red, zeroes as a cyan dash, and text in yellow, you’d use:
[green]#;[red]#;[cyan]”–“;[yellow]
Thank you very much from Israel
I was searching all over the web for a solution finally you made my day. Thank you and God Bless
hi, im trying exactly the same, but my excel says that its not possible and i must choose from the predefined formats. Exactly it says: Microsoft excel cannot use the nuber format you have typed.Try to use one of the built-in number formats.
I have microsoft 2010 version
hi, im trying exactly the same, but my excel says that its not possible and i must choose from the predefined formats. Exactly it says: Microsoft excel cannot use the nuber format you have typed.Try to use one of the built-in number formats.
I have microsoft 2010 version
Many thanks for any help,
Zane