Apple Intelligence is now available on iPhone, iPad, and Mac!

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

How to convert a cell reference value to text

I want to enter some text in a cell and show a reference to another cell e.g. "A1" with a formula.

The outcome of my formula should be something like "A1 converted in USD" where "A1" and "USD" are respectively "the actual cell reference (A1), location" and "the content of the cell" (USD).


How do I do that?

I can do the last part but I don't know how to convert the cell reference (column/row) into its text format.

The string (A1) has to remain text and my formula should be easily change the values through dragging the cell.


="Cell (A1) converted in "&B$3


The outcome should look like this:

Numbers 5.1 on MacBook Pro

MacBook Pro 13″, macOS 10.13

Posted on Jul 4, 2021 5:50 AM

Reply
7 replies

Jul 8, 2021 11:40 AM in response to BassPlaya

"I see where I made a mistake that caused the confusion. In my text { "Cell (A1) converted in "&(B3)" }, (A1) obviously should be (A5) "


Initially, you said:


The outcome should look like this:


As presented, and using the conversion rates in effect at about the this screen shot being posted, the the labels in all cells (except for the row number in each of the labels in row 1) correctly describe what appears to be displayed in row 5 of the table.


The screen shot should show the text you want to be displayed in each of the cells, so please enter that text directly into the cells that are currently showing error message or the wrong information.


I am assuming the 1,000 value in A5 is directly entered, and each of the values in cells B5, C5, and D5 are calculated by multiplying the value in A5 by the current exchange rate for the currency identified in row 4 of the same column.


If that is the case, there is no need for the (A1) reference (corrected to (A5) to be different. Each of the three values is a conversion of the amount in column A to the equivalent in the currency named in the label in row 3 of 'this column.


IF the value in each of columns B, C and D is calculated from the value in the colunn to its left, you will need to increment the letter part of A5 for each row, BUT you will also increase the number of calculations to determine the value, and get an incrementally less accurate calculation as you progress to the right.


If you are planning to include more than one row of values to be converted to the same set of currencies, you should drop the cell reference to A5 and place a column reference to A.


Making that single change ("A1" to either "A5" or "column A") in the formula offered in my reply above should pri=ovide the result you need in B1, C1 ans D1.


Regards,

Barry



Jul 4, 2021 6:10 AM in response to BassPlaya

I do not understand what you want.


In what cell are you entering text and what is the text you want to enter? Looking at your table I was thinking it was cells B1, C1, and D1 but those cells contain a formula.


Is the purpose to be able to type "Cell (A1) converted to USD" into cell B1 and have cell B3 become "USD" and B5 be the result of the conversion?

Jul 6, 2021 7:18 AM in response to Badunit

I see where I made a mistake that caused the confusion. In my text { "Cell (A1) converted in "&(B3)" }, (A1) obviously should be (A5) i.e. referencing the cell or perhaps only the column that is used in the calculations for Cells (B5),(C5),(D5), ... or columns B, C, D, etc.


FYI: The text in B1, C1, D1 is ONLY to show the user of the spreadsheet what happens in Cells (B5),(C5),(D5) etc. without the need of diving into the function itself i.e. they should see it "at a glance"


Therefore I'm entering this text in B1. Once entered, I should be able to drag that "header cell" horizontally to the right and the part in brackets in this case (A1) –which should actually be Cell (A5), my mistake – should be automatically updated.

How to convert a cell reference value to text

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.