Forum Discussion
Adding text to formula
Trying to create a formula to do this for example: first cell just a number, say 100, second cell a number plus text, say 2 kg would like to multiply and show "200 kg" as a result. Think need an array formula but fairly new to Excel, any thought appreciated
Numbers can be formatted with a custom number format to include text without affecting the data type.
Note in the following screenshot how the cell C1 hs only the formula
=A1*B1
and the cell shows the result as
6 kg
This is achieved with the custom format
0 k\g
Some characters have special meaning in number formats an need to be preceded by a \ sign in order to show in a number format.
The advantage of formatting a cell is that its underlying data type is still a number and can be used in further calculations. If you append text, as suggested with =A1*B1&" kg", the result is text and cannot easily be used in further calculations.
- You are right Ingeborg , the result will be text in my suggestion.
if the user need to do more calculation on the result he should use the way you mentioned.
- thanks for your quick reply, best regards, Alan
Hi Alan,
You can not do math on a cell that contains text, here is what you should do to get the same result:
1- Remove the kg from the second cell, and keep the numbers only.
2- Write the formula for multiply like this =A1*B1 &" kg"
if you have 5 on cell A1, and 4 on Cell B1, then the result of this formula will be 20 kg
