I need to update hundreds of cells, and that would be trivial automating, but I am not being able to make it work.

I have a list like the following:

And, in a different tab, a list I have to populate with values above (in B) based on the appearance of the twitter handle in other column.

The names are within a long text string (all of them begin with @), and it is not possible to re-order the list based on those names. Also, there are more names than values, so some cells will remain blank.

Is there a way I can write a formula that writes the values of the first list into the second one if the name in column A in that row is contained within the adjacent string?

Thanks!

CodePudding user response：

You can refer to this sample formula (Same sheet was used):

=arrayformula(if(C2:C<>"",iferror(vlookup(REGEXEXTRACT(C2:C,"\B\@\w "),A2:B,2,false),""),""))

What it does?

• Use array formula to loop column C values
• Extract the twitter name (string that starts with @) using

  CodePudding user response：

  Since we don't have access to the spreadsheet, I can't know for sure what the line-break character is within the Col-A cells of your second sheet. And using this line-break character is important, since Twitter handles may use some non-alphanumeric characters such as the underscore and others which are not included in such REGEX notation as \w. I'm assuming here that the line-break character is CHAR(10) from the ASCII chart.

  I also don't know the name of your first sheet; so here, I've just written it as Sheet1. You'll need to replace that with your actual sheet name, remembering to place it in single quotes if it contains anything but alphanumeric characters (e.g., 'Data Sheet').

  That said, delete everything from Col-B in your second sheet (including the header "Connections") and place the following formula in cell B1 of that second sheet):

  =ArrayFormula({"Connections"; IF(A2:A="",, IFERROR(VLOOKUP(REGEXEXTRACT(SUBSTITUTE(A2:A,CHAR(10),"~"),"@[^~] "),Sheet1!A:B,2,FALSE)))})