Hot answers tagged

4

Change your * to a +, so you end up with ">([^<]+)<. I don't think you want the double quotes at the end either, since Account is followed by </div>, not by something like <"div which wouldn't be valid HTML anyway. * means 0 or more matches, + means 1 or more. See e.g. Wikipedia for some information about these 'quantifiers'. Here ...


3

You mentioned How can I use a filter or conditional formatting to find all numbers that are NOT in this format (#### ### ###). Please use the following conditional-formatting rule =IF(A1<>"",(IF(ISNUMBER(A1),TRUE,NOT(REGEXMATCH(A1,"^\d{4} \d{3} \d{3}$")))))


2

You can try the following =ArrayFormula(REGEXREPLACE($Q26:Q,"\w+","1")) (Please adjust ranges to your needs)


2

You can use the following formula =CONCATENATE(SORTN(ArrayFormula(IFERROR({REGEXEXTRACT($A$2:A,".*NB"), REGEXEXTRACT($A$2:A,"NB(.*)")*1})), 1,2,2,0)) Functions used: CONCATENATE SORTN ArrayFormula IFERROR REGEXEXTRACT


2

Edit. You can use Index & Match to get the reference to the max item in the array and return the matching item from C. I don't like this approach as your declaring an array twice so the computation is more expensive, I would suggest the answer by @marikamitsos =INDEX(C2:C9,MATCH(MAX( ARRAYFORMULA(INT(IFERROR(REGEXEXTRACT(C2:C9,"NB(\d+)"),...


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+)"),"\.","\/") &" &...


2

First substitute the white space. Paste this to Cell B2: =SUBSTITUTE(A2, " ", "") After that make your number to exactly 10 digit. Paste this to Cell C2: =IF(LEN(B2)=9,"0"&B2, IF(LEN(B2)=8,"00"&B2, IF(LEN(B2)=10,B2))) Lastly add the space back to the right place.Paste this to Cell D2: =LEFT(C2,4)&" &...


2

Addressing strictly what you've requested in your post, I added a sheet ("Erik Help") with a custom conditional formatting rule applied to Column A: =AND(LEN(A1),ISERROR(REGEXEXTRACT(A1,"^\d{4}\s\d{3}\s\d{3}$"))) This will highlight any entries in Column A that are not in the format "#### ### ###". You can view this rule by ...


1

You can address your issue by using 2 similar formulas as shown in the tab I have created in your spreadsheet. The most challenging of the two is in cell P4 where we calculate the times =INDEX(IFERROR(REGEXREPLACE(FLATTEN( SPLIT(QUERY({C4:C&"@"},"where Col1 is not null",0) ,CHAR(10))),"@"," ...


1

You can try the following formula instead =ARRAYFORMULA(QUERY({Projects!A1:C44,REGEXEXTRACT(Projects!C1:C44,"\w+ Payment"), Projects!D1:H44}, "SELECT Col1, Col4, Col7 WHERE LOWER(Col3) CONTAINS 'receive' AND LOWER(Col3) CONTAINS 'payment' AND Col9<>'Complete' and lower(Col5) contains '"&LOWER(REGEXEXTRACT('Report ...


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

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

How to Add Text to Beginning or End of Cells without a formula Please follow these steps Step 1 Go to the top menu --> Edit --> Find and replace Step 2 From Search choose Specific range and clicking on the little icon pick your range. Step 3 Check the Search using regular expressions and un-check Match case Step 4 To add text in the end, on the ...


1

There are different ways to handle the issue. I find the following the easiest ones. For a single cell you can use =REGEXREPLACE(A1,"(\(.*\))|(\D)","")*1 For the whole column try =ArrayFormula(IF(A1:A7="",,REGEXREPLACE(A1:A7,"(\(.*\))|(\D)","")*1)) If you also want to get rid of the #N/A cells, please use =...


1

This formula did the trick: =sort(A2:A,REGEXEXTRACT(A2:A,"^\w+"),true, VALUE(REGEXEXTRACT(A2:A, "\d+$")), true) My first attempt was missing the string to value conversion.


1

You would need to alter your "regular expression" in the sample regex from: ((?:[[:alpha:]]+\s?)+) to: (?:[[:alpha:]]+\s?)+ Following that, your formula will work just fine. =textjoin("",true,arrayformula(if(iserror(REGEXEXTRACT(B2,Criteria!$A$2:$A)),"",choose(1,REGEXEXTRACT(B2,Criteria!$A$2:$A))))) You could also use [A-Za-z] ...


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