Convert cell formula to plain text?

Question

Level 1

0 points

Convert cell formula to plain text?

Say I have a cell with the input [A5/D3] which fetches some values and gives me the value 10. But instead of having a cell that says [A5/D3] everywhere I go, I want it converted into plain text, saying 10.

Imagine these formulas are not as simple as that, and there are many interconnections and many MANY decimals, and simply pasting cells as "Paste value" ruins my individual scientific decimal formating of the cells. If I use the Inspector to change cell type to "text", it will also reset my formating and give me 1000 decimals.

Basically, I want a "plain text snapshot" of exactly how I'm seeing my cells, e.g. if I printed them, so I can remove all the formating contained.

Is this possible in Numbers.app?

Posted on Nov 28, 2011 1:23 PM

Reply

Answer 1

Nov 28, 2011 2:19 PM in response to Joh T

Joh,

I can't imagine why Paste Values wouldn't do exactly what you need. Here's an example:

Regards,

Jerry

Reply

Answer 2

Joh T Author

Level 1

0 points

Nov 28, 2011 2:51 PM in response to Jerrold Green1

Here's how my values look like, and what the formula looks like. Below is the value I get if I "copy" and "paste value". It appears that "paste value" doesn't work in every case of formating I guess.
User uploaded file

Reply

Answer 3

Nov 28, 2011 3:24 PM in response to Joh T

Joh T,

Try "Paste Value" to the canvas so that a new table is created when you paste. You can also select the whole table (not just cells) and copy and paste value.

I think what is happening is applying "Paste Value" to existing cells sometimes doesn't carry the format of the copied cells, which I think might make sense, but I haven't used it enough to be sure.

If you need to paste into an existing table, you can also try in the Format menu, Copy and Paste Style after the paste value. I am not that familar with that feature either so I don't know if it works with all cases.

Reply

Answer 4

Badunit

Level 7

31,162 points

Nov 28, 2011 3:38 PM in response to Joh T

I think you need a script that will copy then convert the clipboard to text, then you can paste it as it appears.

Here is such a script. I usually create a service using Automator. It will appear in the Services menu and you can create a keyboard shortcut to use it.

Reply

Answer 5

Badunit

Level 7

31,162 points

Nov 28, 2011 3:58 PM in response to Badunit

You also will need this

Reply

Answer 6

Badunit

Level 7

31,162 points

Nov 28, 2011 6:27 PM in response to Badunit

Actually, now that I think about it, it might be more useful to have a "Paste as Text" script. You can probably figure out how to modify the script to do that instead (Hint: switch the order of the two lines and replace the "C" with a "v").

Reply

Answer 7

iGodric

Level 1

8 points

Nov 9, 2016 3:45 AM in response to Joh T

You can use the "Edit" menu command "Paste formula results" or simply press Command + Shift + V.

Reply

Answer 8

Barry

Level 9

60,124 points

Nov 9, 2016 10:51 PM in response to iGodric

Hi 'Godric,

"Paste formula results" is the new name of "Paste Values", mentioned by Jerry in the first reply to the original question, and rejected immediately (with examples showing how it failed) in the OP's response.

Regards,

Barry

Reply

Answer 9

iGodric

Level 1

8 points

Nov 10, 2016 4:38 AM in response to Barry

I tested it and did not have the problem. So my answer is correct for the question.

Maybe the problem has been solved by Apple.

Example:

Cell1 value is 1

Cell2 value is 3

Cell3 is formula of SQRT(Cell1,Cell2) and is formatted so that there is only shown 1 digit after the .

Cell4 is not formatted in any way, format is set default to "Automatic"

Now if I copy Cell3 and then Paste formula results to Cell4, it does keep the formatting from Cell3, it does only show 1 digit after the . .

Reply

Answer 10

Barry

Level 9

60,124 points

Nov 10, 2016 12:27 PM in response to iGodric

"Maybe the problem has been solved by Apple."

Apparently it has. The original question, and responses in this 2011 discussion applied to version 2.x of Numbers (Numbers '09).

Regarding your test: As described, the formula in 'cell 3' would return an error message, not a number. SQRT requires a single argument.

Removing 'cell 1' from the formula, leaving SQRT(A2) and formatting cell A3, containing the formula as Number, 1 decimal place displays the result as 1.7.

Copying the cell, selecting cell A4 and going Edit > Paste Formula Results pastes the actual result (1.73205080756888 shown in the box below the table) and the format setting of the source cell (A3), and displays the result as 1.7.

(Checked in Numbers 3.6.2)

Regards,

Barry

Reply