regexgoogle-sheetsarray-formulasgoogle-sheets-querygoogle-query-language
Is there a way to extract a particular string from a cell?
I'm trying to extract the value between round brackets of some substring that is part of a string separated by ,, and then add all those values.
For example:
Cell A1 : Text4(5),Text1(7) Cell A2 : Text1(2) Cell A3 : Text1(1),Text4(3),Text6(5)
Extract all the values between the round brackets of the Text1. That would be 7, 2 and 1. And the final result 10.
I've tried this with a single text (not separated by ,).
For example:
Cell A1 : Text1(4) Cell B1 : Text1 Cell A2 : Text3(2) Cell A3 : Text1(6)
In this case, by searching Text1 it gets the values 4 and 6 and shows the result 10.
=SUM(ARRAYFORMULA(VALUE(
REGEXEXTRACT(
SPLIT(
QUERY(A1:A3,"select A where A contains '"&B1&"'")
,",")
,"[\[\(](.*)[\)\]]"))))
But I don't have any idea of how to get the value when there are more elements in the string. Tried with another query inside but had no luck.
Solution
=ARRAYFORMULA(SUM(REGEXEXTRACT(QUERY(TRANSPOSE(SPLIT(TEXTJOIN(",", 1, A1:A), ",")),
"where Col1 contains 'Text1'", 0), "\((\d+)\)")*1))
- Replace variables in a formula with their definitions
- Replace letters inside words that start with and end with a specific letter not occurring anywhere else with the same amount of a specific character
- regex for expression with one separator hyphen
- open vimeo url in colorbox using jQuery filter
- Split address and numbers
- Pyspark Regular Expression add double quotes after comma
- In the regex world what's a flavor and which flavor does Java use?
- Problems with table extraction using pdfplumber - empty fields in large tables
- How do I pass a variable into regex with Node js?
- How do I use regex in a SQLite query?
- Python RegEx: match words in a string only if they are not preceded by a specific character
- Regular expression that ignores HTML entities
- Allow access to page only from certain referrer
- Regex to match string containing two names in any order
- Matching boot times from log using sed -E
- Validate Bangladeshi phone number with optional +88 or 01 preceeding 11 digits
- Using setCustomValidity stops input from validating
- Use dynamic (variable) string as regex pattern in dart
- Regular expression matching Android package name
- Replace commas not inside single quotes with an @ symbol
- Visual Studio Code Search and Replace with Regular Expressions
- Split string on commas not inside double quotes
- Extracting specific data from a string with regex and Powershell
- Parse datetime string in Ydm.His format
- Extract all words double wrapped in curly braces
- Parse dot-delimited video filename to extract show title, series number and episode number
- Split a string into words, preserving contractions but removing leading apostrophes from words
- Split string on pipes which are not between square braces
- Simple regex pattern for email
- How to extract filename without extension in Makefile