Questions tagged [regexextract]

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

Filter by
Sorted by
Tagged with
12 votes
3 answers
94k 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 ...
Senseful's user avatar
  • 30.7k
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 ...
Drewdavid's user avatar
  • 648
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 ...
ghoti's user avatar
  • 151
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 ...
Mr. B's user avatar
  • 2,857
4 votes
5 answers
27k 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 ...
TonyF's user avatar
  • 41
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 #...
Renan's user avatar
  • 51
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, "...
G Adams's user avatar
  • 33
3 votes
1 answer
247 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 ...
tipie's user avatar
  • 31
3 votes
2 answers
2k 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% ...
wg2moiLi8K425oUo's user avatar
2 votes
1 answer
665 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 ...
user1314051's user avatar
2 votes
1 answer
1k views

How to extract multiple paths from a single string of URLs in Google Sheets?

How can I get a subrange of second to last element in a formula under Google Sheets? I'm trying to extract all occurrences of https://example.com/* in a cell which may contain multiple URLs scattered ...
wass rubleff's user avatar
2 votes
1 answer
2k views

How to generate all combinations of letters in a word?

Using Google Sheets, how would I show all possible ways to type a word, for example: word Word WOrd WORd WORD wOrd woRd worD without any length word? The given word would go on Row A and the ...
Wiiliam Bonney's user avatar
2 votes
1 answer
476 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 ...
Jonathan Rolfsen's user avatar
2 votes
1 answer
6k 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 ...
Vinay Chandrashekhar's user avatar
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: ...
dzhi's user avatar
  • 129
2 votes
2 answers
1k views

Formula for getting the Left most cell value from a 'Range of cells'

Need your support in finding a solution for an issue which I am facing now. Formula for Column A... Data Range from B to D Have values in a range of spreadsheet cells (From column B to D) Value ...
Jackson Gilbert's user avatar
2 votes
2 answers
569 views

Google Sheets' Duration Input issue

We have been using Apple Numbers which seems to have this duration thing figured out a little better than Google, (either that or I'm missing something). In Apple Numbers, if I type into a cell with a ...
Jason Krueger's user avatar
1 vote
1 answer
690 views

Regexextract to get a text in the middle of string

SAMPLE SHEET I have data in a column in this format: 1 P01 - Product Name here - PAID IN FULL 2 P02 - Product2 Name here - PARTIALLY PAID 20 1 P03 - Product2 Name here I've been trying to ...
Genny's user avatar
  • 345
1 vote
2 answers
1k views

Extracting text from a capture group while using an arrayformula to consider multiple criteria

I have a page which contains a list of regular expressions in the format "some text (thing I want) but only if (thing is matched here)" that are fairly varied. There are 29 of them so far, ...
Stephen's user avatar
  • 111
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 ...
Oneezy's user avatar
  • 163
1 vote
1 answer
775 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("|"&...
Mr. B's user avatar
  • 2,857
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 (...
Mr. B's user avatar
  • 2,857
1 vote
1 answer
728 views

Applying a custom sort to Google Sheets string column

I am trying to sort a table of rows by their first column that is comprised of strings with this format (capital letters followed by a dash followed by numbers). Using the default string sort I get ...
Addie's user avatar
  • 121
1 vote
1 answer
349 views

Pretty print google sheet formula?

Finding answers on Web Apps SE or Reddit is wonderful, but if you don't understand them you can't modify them. I recently came across the following as part of an answer: ={"Corrected List 2";...
Sherwood Botsford's user avatar
1 vote
1 answer
223 views

ArrayFormula to extract part of the string

I have come across the below formula, and I am finding it quite useful, I want to turn it into an array shortly, but before I do, I was wondering if there was an easier way to do this? =if(len(LEFT(...
Ceebee86's user avatar
  • 135
1 vote
1 answer
22 views

Split cells not importing first 0

I am using split formula: =IFERROR(SPLIT(REGEXREPLACE(" , "&U4, "(\d)", "$1 ,"), ", ")) It works great except if I have 0250, it imports it across as 250, even with the cell it's importing ...
Peteh's user avatar
  • 49
1 vote
1 answer
54 views

Comparing two cells and adding text to matching values

I have two columns, one with all items in a set and another with the items I currently have. I would like to compare those and for the matching results to have a bit of text added if they match. So ...
littlecoder's user avatar
1 vote
1 answer
22 views

How to count cell with upper leter in array?

I need to count the upper letter if exist in a particular cell. So my results should be as on the image below. important is ignore in count empty cell. I noticed that task is simple if I can use ...
Mbded's user avatar
  • 113
1 vote
2 answers
5k 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 ...
Rafael's user avatar
  • 21
1 vote
1 answer
101 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 ...
Sinethemba Gayiza's user avatar
1 vote
2 answers
144 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 ...
Patrick's user avatar
  • 119
1 vote
1 answer
206 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 ...
Jamie B's user avatar
  • 21
1 vote
3 answers
410 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/...
Elad Ratson's user avatar
1 vote
1 answer
747 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 ...
JVC's user avatar
  • 1,202
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-...
AlvaroFG's user avatar
  • 214
1 vote
1 answer
558 views

How do I build a formula to select a Category based on different select keywords/phrases in a determined column?

I'm hoping to build a formula to automatially select a category based on select keywords/phrases from an imported bank statement. I attached an editable copy of the Google Sheet I'm looking for help ...
Ryan Voigt's user avatar
1 vote
0 answers
55 views

Why 2 columns of the same value give different total?

Sample Data in Result Sheet I have a formula in A2 that extracts data & splits it into unit & product (A & B). I used the SUMIF formula to give the the total of each separate set of ...
Genny's user avatar
  • 345
1 vote
1 answer
268 views

Using REGEXEXTRACT to retrieve specific content from two separate rows in the same column

I Included a Google Sheets link =REGEXEXTRACT(A2:A3, "19..") To me, it would seem as if this would check both cells for any date starting with 19 and extract that into the cell But, it ...
Marshall 's user avatar
1 vote
1 answer
150 views

Can I populate Google Sheets cells adjacent to a target cell?

I have a maintenance tracker in Google Sheets for lighting fixtures. One column is a type of fixture "B", the column adjacent is the problem "C". I'd like to find specific fixtures by name and copy ...
Kevin Arsenijevic's user avatar
1 vote
1 answer
24 views

How to create new cell from keyword of another cell?

I have a column that has job listings with extra info, so I only want to grab the job position and place it into a new cell. A1: Seeking Assistant to the marketing division... B1: Assistant (...
nr1's user avatar
  • 13
1 vote
2 answers
182 views

Copy the contents of a row to another sheet based on a specific value

I have a Google sheet with various rows of data, in one column (Column E), the values for each row follow the following pattern: A1 A2 B1 B2 C1 C2 etc. I want to copy the rows ...
2xtimes's user avatar
  • 13
1 vote
1 answer
883 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 ...
Nate.M-C's user avatar
1 vote
1 answer
27 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 ...
Ashkaan's user avatar
  • 13
1 vote
1 answer
87 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 ...
Jeremy's user avatar
  • 13
1 vote
1 answer
114 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 {} ...
Ryan Neves NevesMedia's user avatar
0 votes
1 answer
311 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'...
Bardo's user avatar
  • 317
0 votes
1 answer
169 views

How would you find and extract the 2nd ocurrence and extract only the number that is in between =value"123456789"?

Any expert on Google Sheets Regular expressions? I am trying to extract the numbers that exist in the second occurrence of this pattern value="400" from the HTML text that I am sharing in ...
Gilbert's user avatar
  • 39
0 votes
1 answer
405 views

Combine an array formula with query function

I want to know if there is a way that I can combine any of these 3 formulas. Sample Sheet I have in col. A3, this extracts the info in Data tab, and splits into rows: =arrayformula(substitute(...
Genny's user avatar
  • 345
0 votes
1 answer
133 views

Regexextract numbers from string

I have a regexextract formula which takes the year from Column A (column) - however this only works if the year is yyyy-yy format - however some of the years are only yyyy and it returns an error Is ...
magelincs's user avatar
0 votes
2 answers
320 views

How to write a Google Sheets formula to extra a substring from a URL?

I'm trying to set up a formula to extract data from a URL that is in one of the column B of my Google Sheet. I've been tryin use the LEFT formula to extract the data, but the problem is that the ...
jaywonger's user avatar