Values and Text in the Same Cell

[Sprint54]

In Lotus 1-2-3 I am able to enter the following in a cell (w/o quotes);
"1234; forecast was 4567". 123 treated the 1234 as a value so it could be
added, subtracted, etc. and ignored the rest but "forecast was 4567" was
still in the cell for future reference and was easier and less time consuming
than creating a cell comment. How do I do the same in Excel? When I enter
1234; forecast was 4567 I receive an error. The cell comment feature is to
time consuming when I have to enter new values but want to keep the original
value as a note for up to 100 individual entries each month.

 

[Kevin B]

Click FORMAT, CELLS and if necessary click the NUMBER tab.

In the category list select CUSTOM. In the list of types, select the number
format you want to use and enter the text you want added to the number in
quotes.

The following example produces a number w/o decimals, dollar signs or
commas, followed by your text example:

0 " forecast was 4567"

 

[Sprint54]

Thanks Kevin, but that's not what I was looking for. I can see where I maybe
did not explain it very well. If I use your example in say cell A5 the
contents of cell A5 displays 0 " Forecast was 4567" and I can add, subtract,
etc... But I only want 0 to be displayed in the cell, not the text portion.
The text portion is only visable in the formula bar when the cell is active,
in this example cell A5. In 123 this was easily achievable. So using your
example agan but assuming Lotus 123, in cell A5 I enter 0;forecast was 4567,
in cell A5 only 0 is displayed, but if I go to, make active, cell A5,
0;forecast was 4567 is displayed in the formula bar. How can I do the same
in Excel?

 

[Pete_UK]

Obviously, there are differences between XL and 123 - I don't believe
you can do this in Excel. One way would be to enter the "comment" in
the next column - if you did not want this to be visible you could set
the foreground colour to the same as the background colour (you would
still be able to see it in the formula bar and when the cell is part of
a highlighted range.

Hope this helps.

Pete

 

[Norman Jones]

Hi Sprint,

See the N function in Excel Help.

Using the example discussed earlier in the thread. try:

=0+N(" forecast was 4567")

 

[Sprint54]

Perfect, thanks Norman. A few more characters to type than in 123 but it
does the trick. Thanks again.