copy what displays in cell

[papajohn]

This is probably an easy one, but I do not happen to know it.
In Cell A1 I have entered 50.2 This cell is formatted as currency so what I see is $ 50.20. I would like to convert this to text in cell B1 but I want the text to read "$ 50.20". How do I get what actuall appears in the cell into another cell as text?

Thanks in advance.

PS I have had the same problem with dates.

 

[plettieri]

I am sure you will get an answer shortly...but I was just wondering what is the need to convert them to text....Once they go to text you loose some flexibility to manipulate ....

pll
This message was edited by plettieri on 2002-10-16 18:58

 

[PaddyD]

=TEXT(D1,"$00.00")

 

[papajohn]

I actually want to concatenate the number (with the dollar sign) with some other text so that I can use the text string cell as a reference for a text box.

 

[plettieri]

PaddyD

I'm a little confused.....that is not unusual...if i add 1 to the contents of you formular in another cell , i will get a value that is 1 greater...I thought you could not add, sub, mult, etc from a text item.....

pll

 

[papajohn]

That seems to have solved the problem. Thank you very much.

 

[PaddyD]

On 2002-10-16 19:07, plettieri wrote:
PaddyD

I'm a little confused.....that is not unusual...if i add 1 to the contents of you formular in another cell , i will get a value that is 1 greater...I thought you could not add, sub, mult, etc from a text item.....

pll

You can perform some (many?) mathematical operations on text values - the effect is to coerce the text values into number values during the execution of the formula

Paddy

 

[plettieri]

Tks PaddyD

One more new thing to write down...my desk is getting full...i have too start memorizing this sfuff

pll

 

[Joe Was]

Plettieri,

It called "The hierarchy of operations."
Math is carried out before the text operation, so with a dollar value being referenced then operated upon by the text function. Excel evaluates the operations and sees that you want to add along with the text function and does so, before it runs the text operation.

Cell==> A1=$5.01 as a value
Cell==> B1=Text(A1,"$0.00")+1
Then B1=$6.01 as value not text.

And if you choose to do the same in steps with different cells, value in one cell, text conversion in another and addition in another: Excel will trace the dependents and see that the text function in this case is the value of a cell different than the cell containing the text function and do the addition before the text function again!

Cell A1=$5.01 as a value
Cell B1=Text(A1,"$0.00")=$5.01 as left justified value, not right justified value if you check the cell format.
Cell C1=B1+1=$6.01 as right justified value due to the math operation.

So text does not always mean text only, it may only affect the justification of a value.

If you hard format the cell as "Text" then you can over-ride this if needed, but it is best never to hard format a cell as "Text." As you will then have all the problems that you thought you would get using the Text-Function! JSW
This message was edited by Joe Was on 2002-10-16 19:53

 

[plettieri]

Joe Was

That explanation was great...

tks
pll