With vanilla functions, detect if number is *formatted* as percent?

Question

• 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.

Answer 1

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.