Hot answers tagged

2

You have the basic concept right just piece it together step by step. This does the job: =REGEXREPLACE(JOIN(" ",ARRAYFORMULA(IFERROR( REGEXEXTRACT(SPLIT(A1," ",TRUE,TRUE), "https://example.com/(\S+)"),""))),"\s+"," ") You split the string at whitespaces, this is safe because URLs don't contain white space so you're definitely not breaking them up. This ...


2

If you, as Ryan Shillington suggests, want to know the number of cells within a range that has "sites" in it, you might try: =COUNTIF(A1:A100;"*sites*") The * acts as a wildcard notion, so it will look for a match anywhere within the cell, not just an exact match for the whole cell. I've also done this with a cell reference instead of "sites", but ...


2

You can also use this single robust formula in cell B2: =IFERROR(ArrayFormula(if(LEN(A2:A),TRIM(SPLIT( REGEXREPLACE(TRANSPOSE(SPLIT(TRANSPOSE(ArrayFormula(join("♣︎", A2:A))),"♣︎")),"(?:.?(@[A-Z][a-z]\b))|(.*)","$1"&" ")," ") ),"")),"") An even simpler formula can be used if you don't have any empty rows in ...


2

Edit In your Google thread you mention: Would really like to be able to convert this in one formula/step. ... I would love if it if could handle the whole column down. In this case, you can use the following formula =ArrayFormula(IFERROR( (REGEXREPLACE(REGEXEXTRACT(A2:A,"(^\d+\.\d+\.\d+)"),"\.","\/") &" " &REGEXREPLACE(...


2

EDIT (following OP's comment) To exclude empty cells (which are counted as zeros altering the result) we adjust the formula by adding the FILTER function. =AVERAGE(ArrayFormula((REGEXREPLACE(TO_TEXT(FILTER(A44:H44,A44:H44<>"")),"L",""))*1)) Initial answer You can use this simple formula: =AVERAGE(ArrayFormula((REGEXREPLACE(TO_TEXT(A44:H44),"L",""...


1

try this. It's cumbersome but it works =average(if(isnumber(A1),A1,value(left(A1,len(A1)-1))),if(isnumber(B1),B1,value(left(B1,len(B1)-1))),if(isnumber(C1),C1,value(left(C1,len(C1)-1))),if(isnumber(D1),D1,value(left(D1,len(D1)-1))),if(isnumber(E1),E1,value(left(E1,len(E1)-1))),if(isnumber(F1),F1,value(left(F1,len(F1)-1))),if(isnumber(G1),G1,value(left(G1,len(...


1

Please use the following arrayformula in conjunction with 2 nested IF functions. Adjust ranges to your needs: =ArrayFormula(IF(P2:P<>"", IF(P2:P=2,Q2&", WORLD",Q2),""))


1

To allow users to enter only text in short questions you should use the Response validation option and as the regular expression use Matches ^[A-Za-z]+ To take it a step further you can use an expression for 1st Capital letter(Upper case) and the rest ONLY lower case letters ^[A-Z]{1}[a-z]+


1

You could use the REGEXREPLACE function in your formula: =ArrayFormula(REGEXREPLACE(REPLACE(IMPORTRANGE("1I1MK51uYT_WJDh0fiiHxLjnYMoQOVoZi41XnHM", "SetstudentMatrik!C2:C"),1,6,"******"),"******","")) If you want to keep the leading ****** use the following formula: =ArrayFormula(REGEXREPLACE(REPLACE(IMPORTRANGE("1I1MK51uYT_WJDh0fiiHxLjnYMoQOVoZi41XnHM"...


1

The formula that would give you the desired results would be: =ArrayFormula(REGEXREPLACE(ARRAYFORMULA(TRANSPOSE(TRIM(SPLIT(CONCATENATE(IF(LEN(B3:H8),B3:H8&"★",)&REPT(" "&"♣︎",COLUMN(B3:H3)=COLUMN(H3))),"♣︎")))),"([^★]+).+","$1")) It is a bit convoluted but works fine.


1

The LEFT function is for you to find values from the left-to-right summation and the RIGHT function does just the opposite. The FIND function is to find at what position exactly text you want. It automatically starts counting characters for LEFT or RIGHT. And the LEN function is to count the total value of characters that exist in a cell, so you need to use ...


1

You can try =switch : =SWITCH(A1:A10,"A",0,"B",1,"C",2) Result : Same result with regexmatch : =ArrayFormula( if(REGEXMATCH(A1:A10,"A"),0, if(REGEXMATCH(A1:A10,"B"),1, if(REGEXMATCH(A1:A10,"C"),2,"no match"))) )


1

Yes, it can. The formula in the question is very convoluted and there isn't a description of what it does, so here is a a simple formula that returns Saturday Sunday Monday Formula =ArrayFormula(ARRAY_CONSTRAIN(TEXT(A2:A,"dddd"),3,1)) Explanation TEXT(A2:A,"dddd") This returns the day of the week for all cells on A2:A ARRAY_CONSTRAIN is used to limit ...


1

If you use a regular expression with improper syntax (according to Google's regex rules found here), it will crash the browser when it tries to autosave. For example, in PHP and Java you can use curly brackets as literals. However, Google uses the curly bracket as a quantifier. If you use the curly bracket as a literal (i.e. without escaping it) in a ...


1

Following the OP's Edit things are quite clearer. Using the FILTER function =FILTER('Form responses'!A:A, REGEXMATCH('Form responses'!I:I, "Solar")) Extra tip If you were to use the | (pipe symbol) in the FILTER function which stands for OR, then the formula would change to: =FILTER('Form responses'!A:A, REGEXMATCH('Form responses'!I:I, "Solar|Energy ...


1

There are three ways of matching Unicode characters according to Google Sheets' regular expression documentation: Using exactly two digit hex code: \xA0 Using up to three digits octal code: \240 Using any length of hex: \x{A0} or \x{0A0} or \x{0000000A0} etc. (any reasonable number of leading 0's is allowed apparently) Other ways of specifying characters ...


1

In C2 enter this formula : =filter( ArrayFormula(if(REGEXMATCH(A2:A,TEXTJOIN("|",true,B2:B)),,A2:A)), ArrayFormula(if(REGEXMATCH(A2:A,TEXTJOIN("|",true,B2:B)),,A2:A))<>"" ) Breakdown of the formula : =REGEXMATCH(A2,TEXTJOIN("|",true,B2:B)) Regexmatch returns TRUE or FALSE if A2 contains any of the words in column B. TEXJOIN ...


1

Yes you can do it. There are a lot of approaches for this problem. Example : =QUERY(A:B,"SELECT * WHERE "&C1&C2") Cell C1, C2 contains the rest of the query like C1 B = 'Apples' C2 OR B = 'Peaches' You can build C1 and C2 with conditions such as C1 =IF(D1<>"","B = '"&D1&"'","") Where D1 is your fruit's name Then build ...


Only top voted, non community-wiki answers of a minimum length are eligible