copy what displays in cell

papajohn

New Member
Joined
May 29, 2002
Messages
20
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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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
 

papajohn

New Member
Joined
May 29, 2002
Messages
20
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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS

ADVERTISEMENT

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
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

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

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Tks PaddyD

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

pll
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

Forum statistics

Threads
1,144,439
Messages
5,724,369
Members
422,547
Latest member
Vision1291

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top