Questions tagged [regexextract]
For questions about a spreadsheet function that allows selection from a string on the basis of a regular expression.
71 questions
0
votes
1
answer
70
views
Extract URL from a hyperlink using REGEXEXTRACT not working in arrayformula
I have a sheet that gets a hyperlink added to it everytime a form is submitted. I want to convert that hyperlink into a URL.
https://docs.google.com/spreadsheets/d/1MjPKOF-...
- 49
1
vote
1
answer
2k
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 ...
- 13
1
vote
1
answer
2k
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 ...
- 383
0
votes
1
answer
886
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(...
- 383
0
votes
1
answer
210
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 ...
- 87
0
votes
1
answer
94
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 ...
- 107
0
votes
1
answer
556
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 ...
1
vote
1
answer
835
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
1
vote
2
answers
2k
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
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 ...
- 171
-1
votes
1
answer
57
views
How can I extract specific information from the cells?
How can I extract just the: Brand, Showroom Only Product, etc.
- 1
0
votes
1
answer
285
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": ...
- 1
1
vote
1
answer
270
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(...
- 143
1
vote
1
answer
47
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
(...
- 13
0
votes
2
answers
83
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.
1
vote
1
answer
92
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 ...
- 172
2
votes
2
answers
510
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 containing ...
- 23
0
votes
2
answers
152
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 ...
- 103
2
votes
2
answers
661
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 ...
1
vote
2
answers
6k
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 ...
- 21
1
vote
1
answer
963
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 ...
- 37
1
vote
2
answers
176
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 ...
- 119
-1
votes
1
answer
101
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 ...
- 7
1
vote
1
answer
141
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
vote
1
answer
274
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 ...
- 21