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.
Google Sheets – Value in Cell Different from Text
google sheets
Related Solutions
Please try:
=indirect(E26&"!"&$AV$6)
Indirect is able to read a string as a cell reference. Within the parentheses three elements are concatenated: the content of E26 (which could be as here a sheet named 17.02.2014), the required "!" and the content of AV6 (anchored with $s so it does not change if the formula is copied around).
You can achieve that by using the following formulas:
Formula
=IFERROR(OFFSET(A1, MATCH(C1, B1:B3, 0) - 1, 0), "nothing found")
=IFERROR(FILTER(A2:A4, B2:B4 = C2), "nothing found")
=IFERROR(QUERY(A2:B4, "SELECT A WHERE B = '" & C2 & "'"), "nothing found")
=IFERROR(ARRAYFORMULA(VLOOKUP(C2, {B2:B4, A2:A4}, 2, 0)), "nothing found")
Screenshot
Example
I've created an example file for you: Google Sheets: If Cell text is “Text” get adjacent cell text
Related Topic
- Google Sheets – How to Sum Cells Until a Specific Value is Matched
- Google-sheets – Help with a “=QUERY(IMPORTRANGE(…” formula in Google Sheets
- Google Sheets – Multiply Cell Value on Edit
- Google-sheets – Rich text formatting in a Google Sheets cell
- Google Sheets – How to Include Text from a Cell within a COUNTIF Function
- Google-sheets – Use Text in cell as formula in Google Sheets
- Google-sheets – How to extract specific value from a single cell on Google Sheets
Best Answer
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+")/2The
REGEXEXTRACTwill 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
REGEXEXTRACTinVALUE:=VALUE(REGEXEXTRACT(A2,"\d+"))This is because
REGEXEXTRACTextracts 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. SoVALUEconverts it to a number. In lieu of this, you could also just apply the math of*1:=REGEXEXTRACT(A2,"\d+")*1