regexgoogle-sheetsgoogle-sheets-formulaarray-formulassumproduct
Convert text to numeral text
I have a google spreadsheet that I am using as a quiz. The quiz takers select an option from a data validation drop down.
The cell below strips all but the first character =left(A2,1) which is a numeral value. Further below in the sheet is a cell that sums certain cells, For instance,
=Sum(A3,D3)
For some reason, the sum function does not recognize the cell as a purely numeral value, even with changing the format to number. Any ideas?
Solution
you can use SUMPRODUCT instead of SUM which is able to recognize numeric values even if they are disguised as a text string:
=SUMPRODUCT(A3:D3)
another way would be use regex like:
=ARRAYFORMULA(REGEXEXTRAXT(A3:D3, "(\d+)-")*1)
- Get all pipe-delimited words inside of all curly braced expressions in a string
- Using regex matched groups in pandas dataframe replace function
- Basic PCRE expression to match word anywhere in a line + another word always at the end of line
- string split using regex PHP
- Split string representing a comparison condition into its three parts
- using grep inside make to check if a file contains an export to PATH
- Grabbing a specific url from a webpage with re and requests
- QT - QInputDialog How to Validate?
- How to fill gap between two characters with regex
- Golang Regex extract text between 2 delimiters - including delimiters
- Regular expression loop makes duplicate matches due to similar words. How to avoid?
- HTML5 pattern for validating IP with mask
- Why does running the same regex twice yield different results?
- Regex to find double whitespace not on the start of a line
- Extract BBEdit search history into a shell script
- How to add http:// if it doesn't exist in the URL
- Regular expression to return text between parenthesis
- Find procedure name in all other procedures
- How to use posix regexp operator for substring match
- regex for checking URL's wildcard for both subdomain and top level domain?
- How to split a code looking like '10abc' in numbers then letters?
- Replace (~) with Double Quote (") in C#
- Steps to draw a DFA (or NFA) from a simple statement?
- Elegant regular expression to match all punctuations but not "'" in emacs Lisp?
- Matlab split string multiple delimiters
- Split on regex (more than a character, maybe variable width) and keep the separator like GNU awk
- How to use regexp to perform a glob in C/C++ (Linux)
- Regexp matching a string - positive lookahead
- Extract values from string using regex
- Does the Python regular expression module use BRE or ERE?