The following arrayformula needs to be applied to the entire column B, so that it populates Twitter account IDs in the cells of Column B according to hyperlinks in Column A. It firstly checks whether the cell in Column A is empty. If empty, leave the cell in Column B empty; if not empty, populates the cell in Column B with a Twitter account ID according to the hyperlink in the cell of Column A.

=arrayformula(IF(A1="",,IF(REGEXMATCH(A1, "twitter\.com/[^/]+"), REGEXEXTRACT(A1, "twitter\.com/([^/]+)"),)))

But the problem is that, every time I and my coworkers "Insert 1 row above", the arrayformula is not automatically applied to the newly inserted row.

I found a 10-year old solution, but not sure whether it's still an optimal one. I did not see any option for adding script in the Tools menu as the solution described. But I saw a similar option called "Apps Script" in the Extension menu. And I tried creating a script with that option but didn't know what to do next. After I press the Debug button, it says something like authentication is required. The owner of the spreadsheet is one of my coworker, so I myself cannot do the authentication directly.

Incidentally, the purpose of populating Twitter account IDs in Column B is for finding duplicates in Column B. And that's because one Twitter account can only have one hyperlink in the spreadsheet.

1 Answer 1


Reserve row 1 for headers by choosing View > Freeze > 1 row and change your workflow so that you always insert new rows below row 1.

Then clear column B and put this formula in cell B1:

  row(A1:A) = 1, 
  iferror(regexextract(A2:A, "twitter\.com/([^/]+)")) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.