Questions tagged [regexextract]
For questions about a spreadsheet function that allows selection from a string on the basis of a regular expression.
70
questions
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 #...
0
votes
1
answer
20
views
How do I use REGEXTRACT to return grouped numbers from a string where the number of groups varies?
In Google Sheets, I'm trying to return numbers from within a text string.
The numbers are values of the property named either item_id or item_ids.
I've tried the REGEXTRACT function but I'm struggling....
- 3,403
0
votes
1
answer
387
views
Extract Data from 2 columns if it matches condition
This is how my main data sheet looks like
And for any item that has customization, for e.g. - ORGANIC, I want to extract/copy just those data, the meal name & total units to another sheet.
And ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
1
vote
1
answer
267
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 ...
CommunityBot
- 1
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 ...
- 169
1
vote
1
answer
557
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 ...
- 3,403
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 ...
- 1,233
0
votes
0
answers
97
views
IMPORTRANGE contains specific columns with multiple answers
Example, one person can enter 5 events with several options for participating (both days or Sunday only or 1st go....) how do I get master1 sheet to only show contestants on Saturday and master2 only ...
- 9,915
1
vote
1
answer
689
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 ...
- 9,915
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(...
- 9,915
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 ...
- 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 ...
- 2,338
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 ...
- 5,530
0
votes
1
answer
57
views
how to use regextract to find occurences of a number when it ends with a 2 after decimel point
my number set looks like this example
-360.132,
125,
140.0152,
-400.13,
i would like to be able to have an if statement for the numbers that end with a "2" after the decimel point. So i want ...
- 13.3k
-1
votes
1
answer
990
views
How do I use non capturing groups in google sheets?
This is what I intend to do.
But a similar one doesn't work.
As with any problem I post, I still can't solve it after more than two hours of trying. :-(
https://github.com/google/re2/wiki/Syntax
CommunityBot
- 1
0
votes
2
answers
26
views
Split function doesn't get the result that I expect [duplicate]
A1 value is 485065/1, 2, 3 and I expect 485065 and 1, 2, 3 but it comes out 485065 and 1, 2, 2003.
=split(a1,"/",false)
Please help me why it does.
CommunityBot
- 1
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 ...
- 44.6k
0
votes
1
answer
49
views
How to partial extract from 3 columns with multiple levels of rules?
The formula someone on this forum provided worked well, but I have run into a problem, which I am struggling to get around.
Google sheets Link
The formula in H2 looks between 00 & 02 and ...
CommunityBot
- 1
0
votes
1
answer
270
views
REGEX in Google Sheets with Filter
I'm trying to mix regex with a filter in Google Sheets. What I have at the moment is
=filter(AD:AD,X:X=X2,right(Z:Z,1)={1,2,3,4,5,6,7,8,9})
The regular expression is that the final character in ...
- 9,915
-1
votes
1
answer
35
views
How can I extract specific information from the cells?
How can I extract just the: Brand, Showroom Only Product, etc.
CommunityBot
- 1
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 ...
CommunityBot
- 1
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 ...
- 121
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 ...
CommunityBot
- 1
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, ...
- 111
-1
votes
1
answer
69
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 ...
CommunityBot
- 1
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";...
- 44.6k
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("|"&...
- 44.6k
-1
votes
1
answer
54
views
Multiplying several numbers within a text string, is this possible?
I have cell A1 which is a text string:
100 blue cars, 100 red cars, 100 yellow cars
In cell B1 I have a number which is a multiplier say 2.
Is it possible to change the numbers within cell A1 ...
CommunityBot
- 1
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 ...
- 151
0
votes
2
answers
901
views
Extracting last match from a string with REGEXEXTRACT
I've created a simple text filter:
=IFERROR(REGEXEXTRACT(A1;"- May 2019| - Jun 2019|- Jul 2019| - Aug 2019|- Sep 2019"); FALSE)
It should extract the month and year for an event from the cell A1. A1 ...
- 222
0
votes
1
answer
229
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)...
CommunityBot
- 1
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 ...
- 1
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 ...
- 26.2k
0
votes
1
answer
229
views
I want to get values from multiple URL's in Google Spreadsheet. IMPORTDATA doesnt work
I have 9000 lines with URL's in my Google Sheet.
Example: https://steamcommunity.com/auction/ajaxgetgoovalueforitemtype/?appid=233860&item_type=10).
Every URL returns JSON like this:
{
"success": ...
- 26.2k
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(...
- 26.2k
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
(...
- 26.2k
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 ...
- 49
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 ...
- 26.2k
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 ...
- 26.2k
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,250
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 ...
CommunityBot
- 1
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 ...
- 26.2k
0
votes
2
answers
101
views
Google Sheets Conditional Formatting
I am trying to make a certain cell on my sheet light up based on what week it is.
Each cell contains the value Week X where the X is a week-number.
After a little bit of experimentation I have made ...
- 26.2k
0
votes
1
answer
3k
views
Vlookup using Regex Extract
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 ...
- 44.6k
0
votes
1
answer
298
views
Remove all text before (and excluding) match
Hi I have a cell with data such as:
Dropped pin
Near Hughanden Village Hall, Pome Ln, Hughanden, Low Slokey, HT11 3AT
https://goo.gl/maps/dVtKw423RBA9fkGs7
I would like to use REGEXREPLACE (or one ...
- 26.2k
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 ...
- 26.2k
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 ...
- 26.2k
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 ...
- 26.2k
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 ...
- 26.2k