I'm making a spreadsheet in Google Sheets where I analyze music genres based on specific predetermined data. Because of how I've set it up, my method of checking how many elements there are is counting the number of " " symbols in the formula, but I am currently unable to do that.

For instance, the exact text of cell B2 is currently "=26 26 26 26 13 24 12 24 24 24 22", which equates to and displays as 247. There are 10 " " symbols in this cell. When I call the code =LEN(B2)-LEN(SUBSTITUTE(B2," ","")) on this cell, the number returned is 0, as it is checking the text "247" for " " symbols. Is there a way to call a function on the exact inputted text of a formula, instead of the value the formula returns?

CodePudding user response：

FORMULATEXT is what you are looking for...

try:

=LEN(REGEXREPLACE(FORMULATEXT(A1), "[0-9=]", )) 1