0

I am wondering if there is a way to append a cell with a string value (for better/readable presentation), but ignore this appended value in formulas when referenced?

For example, lets say A4 has the value =SUM(A1:A3)&" USD" B4 has the value =SUM(B1:B3)&" USD"

Now, in A5 I want to have =(A4+B4)&" USD", and somehow have the A4+B4 formula ignore the appended " USD" in A4 and B4. Can I do this, or do I really have to keep a strict separation between calculations/formulas and presentation cells, and have "landing-cells" for calculations?

Improve this question

2 Answers 2

Reset to default
3

Use a custom format that adds the text to the end, like:

0 U\S\D

enter image description here

As you can see the USD does not truly exist in the value and as such it is still a number and can be used in mathematical equations.

Improve this answer
3
  • Oh, didn't think of this, thank you! I have one more question though. What if I have a cell to decide, in this case the currency. Can I refer to this cell? I.e., if I in A5 really have =(A4+B4)&" "&A6, where A6 can be set to "USD", "EUR" or whatever, can I make a reference to A6 instead of having a hardcoded value?
    – MahNas92
    Commented Dec 17, 2021 at 16:37
  • 1
    No, Custom format will not work with a reference. At that point just use two cells. One with the number and the other with the type. Hide the guidelines and add borders correctly so it appears as one cell.
    – Scott Craner
    Commented Dec 17, 2021 at 16:39
  • 2
    @MahNas92 Conditional formatting could do this, though.
    – Engineer Toast
    Commented Dec 17, 2021 at 16:46
0

With A1 similar to:

34.5 USD created by e.g. =B1&" "&C1

you should be able to extract 34.5 with:

IFERROR(VALUE(LEFT(A1,FIND(" ",A1)-1)),0)

... in any formula.

Assuming decimal-POINT in locale. Try 34,5 if not so.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .