Excel: Use Format Cells to replace numbers with text

=========
QUESTION
———
I have  a spreadsheet where I need to have a number in a cell for the calculations being done on that cell, but I want TEXT to actually display in that cell, not the number.

For example, I have a spreadsheet showing a per line item cost, and the per line item total calculates it’s value by multiplying the per line item cost times the per line item quantity field. I want the per line item cost to read “Included in total price”, but if I type that, my per line item total errors out and displays “#VALUE”.
So in this scenario, I need the value of the cell to be 0, but the cell to read “Included in total price”.

 

=========
ANSWER
———
Right click the cell
Select Format Cells
Under the Number tab, in the Category listing, select Custom
In the Type text field to the right, enter the following text:
0;-0;”no cost”
Click OK

Now when you type a 0 in this cell, it will retain the value of zero, but display the text “no cost”

 

=========
EXAMPLES
———

 

=========
APPLIES TO / KEY WORDS
———
Microsoft Excel 2007
Format
Cell
Text
Value
0
Zero

 

=========
REF
———

 


http://www.anysitesupport.com/excel-use-format-cells-to-replace-numbers-with-text/
http://anySiteHosting.com

By | 2017-12-01T23:47:41+00:00 June 10th, 2011|Computers, Documentation, Excel, Microsoft|0 Comments