0

Within one cell, I would like the value of the cell to be treated differently from the text inside that cell. For example, I would like the cell to display "$148 (123 euros)", but the value to be treated as 148 when I am summing that cell and another.

0

Given your example — "$148 (123 euros)" — you won't simply be able to refer to the cell when trying to do math with it.

Let's say that your above string is in A2 and you want to divide the USD amount by 2 in cell B2. In cell B2, you'd use something like this:

=REGEXEXTRACT(A2,"\d+")/2

The REGEXEXTRACT will extract the first group of digits it finds, on which math can now operate.

Keep in mind that if you only want to extract the number without any further math, you'll need to wrap the REGEXEXTRACT in VALUE:

=VALUE(REGEXEXTRACT(A2,"\d+"))

This is because REGEXEXTRACT extracts strings. When you apply further math operations, Sheets automatically knows you want to treat the extracted portion as a number. But if no math is applied, the extraction remains a string. So VALUE converts it to a number. In lieu of this, you could also just apply the math of *1:

=REGEXEXTRACT(A2,"\d+")*1

2
  • Thank you Erik. I had a feeling this was not possible. Your workaround makes sense.
    – skp
    Mar 3 at 0:06
  • You're welcome. I don't know the purpose of your having the string information together in one cell. You may have one. But also consider that you could use two different cells, side by side. The first could just have the real number 148 formatted as currency with a dollar sign. And the second could have the number 123 formatted as currency with a euro sign. In fact, if the string is coming into your sheet as-is, you could set up two columns next to it that would pull either or both of those two numbers into separate cells, which you could then do direct math on.
    – Erik Tyler
    Mar 3 at 0:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.