Questions tagged [regexextract]
For questions about a spreadsheet function that allows selection from a string on the basis of a regular expression.
69
questions
1
vote
1
answer
4k
views
Google Sheets: REGEXEXTRACT Formula to remove characters not inside [brackets] but leave , commas?
I'm trying to create a formula that uses either =REGEXEXTRACT or =REGEXREPLACE to remove all characters not inside [brackets] but leaving the ,,, commas where they are.
For example...
➜ Text to ...
- 163
1
vote
3
answers
409
views
Google Sheets - REGEX Challenge
I have a long Google Sheet column of cells with content looking like this:
#text: Jacob Zuma
@domain: https://www.theguardian.com/world/zuma
#text: World news
@domain: https://www.theguardian.com/...
- 762
0
votes
1
answer
2k
views
Conditional color scale not working with functions
I want to apply conditional color scale but it doesn't work. This is the original cell (A12)in a sheet called ProcessedData:
= votes:[2074
Then, I use in B4:
= REGEXEXTRACT(ProcessedData!A12&"...
- 111
5
votes
1
answer
6k
views
How to apply a relative reference in conditional formatting formula?
I have a whole bunch of cells (28 so far) that need conditional formatting.
Here is what the data looks like:
I want the colours in the top "Last Period" metric cells to vary depending on the values ...
- 648
3
votes
1
answer
231
views
How to modify REGEXEXTRACT to import total hours played on Steam
My problem is similar as the one answered here: How to create an auto updating Google spreadsheet with information from Steam?
Only I need to modify the REGEXEXTRACTs to find total hours played ...
- 31
1
vote
1
answer
769
views
How to incorporate SUBSTITUTE function into complicated function in Google Sheets
I have the following function and it works perfectly, with one exception. Some of the values returned have characters in them that I want to remove.
=ARRAYFORMULA(IFERROR(REGEXEXTRACT("|"&...
- 2,817
1
vote
1
answer
735
views
How to regex extract alpha portion immediately after an underscore in Google Sheets?
Using Google sheets, here's what I'm trying to accomplish:
I have data that comes in like this:
xta_GAM-1a (61095)
xta_SM (61096)
xta_GAM2 (61156)
xta_GAM-1b (61161)
AO CPC ALL GAM
AO CPC ALL PA
AO ...
- 1,193
-1
votes
2
answers
115
views
How to move a Google Sheet from one file to another while retaining external references
Background
I usually add several sheets to a single Google sheets file. There is always a separate sheet that extracts information from the other sheets (via REGEXEXTRACT etc).
Problem
sometimes ...
- 1,149
4
votes
5
answers
19k
views
Drop part of a cell value after a specific character
I have one cell that contains a long string of several values separated by "|".
EXAMPLE in Cell B5
title | time | location | cost
I need a formula that will show the value of B5, but only the ...
- 2,817
3
votes
1
answer
2k
views
How to categorize data in Google Spreadsheet using regular expressions
I'm trying to categorize some data in a Google spreadsheet.
I have "phrases to screen" in column A, TAB "Phrases". For example "blueish house"
I have "words" in column A, TAB "Words". For example, "...
- 33
1
vote
1
answer
1k
views
VLOOKUP problem with value from =arrayformula(iferror(regexextract
I'm trying to use VLOOKUP to pull data from one tab in a Google Sheets workbook into another tab. The VLOOKUP formula works if the value being searched for is 100103, but not the cell reference G2 (...
- 2,817
0
votes
1
answer
308
views
Pull info from a larger text string into separate cells with regular expressions
I'm new to using regular expressions and I'm trying to use REGEXEXTRACT to pull info from a larger text string into separate cells:
Men's T-Shirt (Amount: 6.00 USD, Qty: 1, Size: M, Color: Black)
I'...
- 317
2
votes
1
answer
661
views
How can I extract the price using importxml from an AngularJS site?
I need to extract the price from this website using importXML on Google Spreadsheets.
Here is a product
https://en-sa.wadi.com/apple-iphone-6s-16-gb-4g-rose-gold-with-facetime-61616.html
The issue ...
- 51
3
votes
2
answers
2k
views
How can social hashtags be extracted from a cell?
Example:
A2 -> What a beautiful day! #sunday #summer
B3 -> #news Stay tuned for our next contest! #something
Desired output on B2 ->#sunday #summer
Desired output on B3 ->#news #...
- 51
2
votes
2
answers
1k
views
Can I call IMPORTRANGE function inside a REGEXEXTRACT function?
I want to obtain root domains from a list of URLs with REGEXEXTRACT but want the list of all possible TLDs to come from another sheet, to avoid specifying com|net|org|all other by hand. Basic idea:
...
- 129
4
votes
5
answers
25k
views
How automatically to add spaces inbetween each character in a text string?
Teachers I work with, enter four scores for a student’s assessment in a single cell, like this: 2113, with no breaks or spaces.
How can I separate those characters so as to be able to create a chart ...
- 41
1
vote
3
answers
3k
views
REGEXEXTRACT and French accents in Google Sheets
I have the following formula:
=REGEXEXTRACT(source!A2,"^[\w\s\À-ÿ]+")
where
source!A2="Ancien Marché Saint-Jacques (1125, rue Ontario Est)"
and I would like to extract
Ancien Marché Saint-...
- 214
5
votes
1
answer
5k
views
In a Google spreadsheet, how do I list unique items, modified by a regexp?
I have a Google spreadsheet with items and prices. Column D has the prices, column C has item names, some of which contain multiple words. When an item has multiple words, only the first one is ...
- 151
11
votes
3
answers
85k
views
Is it possible to use a loop in Google Spreadsheets?
I have the following document:
+---+-----------+
| | A |
+---+-----------+
| 1 | Foo (100) |
| 2 | Bar (30) |
| 3 | Baz (50) |
+---+-----------+
I'd like to use a REGEXEXTRACT to extract ...
- 30.4k