Tag Info

Hot answers tagged

4

This formula will do the job: =regexextract(A1;"@(.*)") If no @ is found, the original value is shown. The =regexextract function (documentation) executes the regular expression parameter (@(.*)) against the value of cell A1, and returns the matching group.


3

I would use the following formula to obtain an URL. Formula =IF(ISEMAIL(A2), REGEXEXTRACT(A2,"@(.+)"), IF(ISURL(A2), A2, IF(ISURL(REGEXEXTRACT(A2,"@(.+)")), REGEXEXTRACT(A2,"@(.+)"), "No valid entry" ) ) ) Copy / Paste ...


2

In Dashboard D36 please enter: =sumif(log!C:C,A36,log!B:B) (should result in the 19 you mentioned) and then copy up to D4.


2

I think you are getting the correct answer but .2857 is being rounded down to less than £1! (ie it is just the formatting).


1

I used this to solve my problem. =IF(ISTEXT(VLOOKUP($A6,responses!$D:$M,3, FALSE)),"Yes","No")


1

As Google Sheets does not natively support iteration with circular referencing, you will need to resort to Google Apps Script. Eg: function onEdit(e) { var editRange = e.range; var sheet = editRange.getSheet(); if (sheet.getName() == "Sheet1" && editRange.getA1Notation() == "B1") { var updateRange = sheet.getRange("A1"); var newValue ...


1

Assuming the data starts in row 1, try in B1: =ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),{{0;FILTER(ROW(A:A),LEN(C:C)=0)},{FILTER(A:A,LEN(C:C)=0);""}},2),)) Out of interest, the formula simplifies a bit if you are retrieving from cells below: =ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),FILTER({ROW(A:A),A:A},LEN(C:C)=0),2),))


1

It looks like there is a hidden built-in function ISFORMULA() from Google. I realized when I deleted my own function and was still working. I tried it in a new spreadsheet and still works. No cache issue.



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