Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [regexextract]

For questions about a spreadsheet function that allows selection from a string on the basis of a regular expression.

2
votes
1answer
11 views

Format cell, If word from list in cell

I'm trying to color tag cells according to keywords they may contain. I've searched just about everywhere, and have tried various applications of the Search and Find functions to no avail. Imagine I ...
1
vote
2answers
24 views

How can I create a recursive formula in Google Sheets?

I'm making a task tracker in Google Sheets and am trying to implement subtasks. I am trying to create a formula that checks a row's "Parent Task" column, and if there is a parent task, indents the ...
1
vote
1answer
26 views

Total Wages Calculations

https://docs.google.com/spreadsheets/d/13HdNN_qa9rhLFyMe0N9I9EusmBudi_dcdBkc6UeLHMM/edit#gid=878850570 I am trying to total Normal Pay but the formula does not Multiply Regular Hours by Normal Rate ...
1
vote
1answer
40 views

Goggle Sheets IMPORTXML/REGEXEXTRACT out of date

Alright, so I found my basic answer from these posts, 2-3 years ago. Extract data from Steam Import total hours on Steam Steam has updated its website since then, so I've messed around with it and ...
1
vote
1answer
21 views

How to get the largest initial values of multiplied columns?

I'm tracking performance for some athletes and we need a way to see their "Best" workout numbers meaning the highest numbers of (rep × weight). The table looks like this: The reps and the weight are ...
1
vote
2answers
16 views

Google Sheets - Convert entire column of prices by 50%

I have a large column of prices, however, sometimes the word [FIXED] also appears in these columns (see screenshot below). I need to convert this price list to wholesale. All the products wholesale ...
0
votes
1answer
45 views

Convert Number and Text to Number

I have a spreadsheet in Google Sheets with data like this: In sheet 2, in column pricelist, the below data is there. I want the summary in sheet 1 to have the same. I have used index and match but it ...
0
votes
1answer
30 views

Inaccurate output when using importrange vs manual input

I have a little problem using Google Sheets. I'm using importrange to get raw data from another sheet that I don't own. This is the sample raw data, then import it my own sheet here. Importing data ...
1
vote
1answer
20 views

Conditional Formatting Text with more then one value

I am looking to have a row of 20 cells change font colour based on ABC to Z being white. Then 0 to 10 being blue. Then all the symbols being red. Is there a way to do this? I found I can do this but ...
1
vote
1answer
17 views

Extract Value from Poorly formed JSON result

I have a Google script that returns the following result set, but when I try to get the value for asking_price or asking_price_condition it comes back undefined. I assume it's because of some {} ...
-1
votes
3answers
38 views

Parsing a non-nested JSON string in Google Sheets using formulas only

I have a cell with the following JSON data: {"rev2_0": "2,058", "rev2_1": "2,099", "rev2_2": "2,141", "rev2_3": "2,184", "revchg3_0": "3%", "revchg3_1": "6%"} In another cell, I want to get the ...
1
vote
1answer
12 views

How to SUMIF depending on another column's value and REGEXEXTRACT

I'm trying to use SUMIF and REGEXEXTRACT, i want to get a total sum of the description only where paid out is not blank and the description value needs to be multiplied by value in paid out so on the ...
2
votes
2answers
240 views

Google Sheets - How to extract words from a cell using regexextract

I have a spreadsheet with many rows like this: Arkonor 16m³Crimson +5%Prime +10%Flawless +15% Bistot 16m³Triclinic +5%Monoclinic +10%Cubic +15% Crokite 16m³Sharp +5%Crystalline +10%Pellucid +15% ...
0
votes
1answer
181 views

Vlookup using Regex Extract - Google Sheets

I trying to do insert an image into a cell in google sheets. I'm using vlookup and regex extract to lookup columns to retrieve the image URL for the IMAGE function in google sheets. However one of ...
0
votes
1answer
38 views

Split rows dynamically based on row length (COUNTA) and transpose to 2D array

I have data in this format: And I need it to be reformatted into this: Basically, each row in the initial format is composed of 4 concatenated sections of equal lengths: Part(s), Product(s), Quant(s)...
1
vote
1answer
676 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 ...
1
vote
3answers
241 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/...
0
votes
1answer
417 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&"...
3
votes
1answer
3k 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 ...
3
votes
1answer
93 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 ...
1
vote
1answer
418 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("|"&...
1
vote
1answer
396 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
votes
2answers
75 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 ...
3
votes
1answer
657 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, "...
1
vote
1answer
997 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 (...
0
votes
1answer
142 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'...
1
vote
1answer
403 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 ...
3
votes
2answers
669 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 #...
2
votes
2answers
402 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: ...
1
vote
3answers
2k 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-...
5
votes
1answer
4k 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 ...
5
votes
2answers
27k 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 ...