0

  • A have a numerical field with a custom number format which should accept a number (decimal between negative and positive infinity).

  • When a user wishes to, they may write something like "45%". This will automatically format the field to use the percentage formatting. A percentage acts sort of like (if not exactly like) a number in the range 0<=num<=1.

Let's call that column A. I wish to write a formula (e.g. column B) that treats the value of the cell differently depending on its formatting, without resorting to Apps Script. For example, if the cell in column A is 1234, the result should be 1234. If the cell in column B is 45%, it should equal 0.45*column C.

Is there any simple way to do this with a function?

Things I thought of trying:

  • coercing to a string, and checking for a "%" substring (doesn't work, oddly)
  • some function that somehow reads the format (don't know of one)
  • IF(or(0<=x,x<=1), ...) -- complicated by 0.45 being a valid entry as well as 45%.

Otherwise, it seems clear for correctness ("code smell"), if I want to allow number likes 0.45 to be equally valid as 45% and mean different things, I should create (yet another) column for this modality... if there's no way to distinguish them with a function, and I'm unwilling to use Apps Script for the latency it adds.

Improve this question

1 Answer 1

Reset to default
2

Use regexmatch() and to_text(), like this:

=if( regexmatch(to_text(A2), "%"), A2 * C2, A2 )

See regexmatch() and to_text().

To learn the exact regular expression syntax used by Google Sheets, see RE2.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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