Forum Discussion

rustamrana's avatar
rustamrana
Copper Contributor
Oct 18, 2023

Organize hyper link

I have a list of numbers and associated hyperlinks with descriptions, currenlty the numbers are not ajdac I'm trying to get the links and numbers next to each other. There are about 1200 so I was hoping there is a way to automate this.

excel
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 18, 2023

    rustamrana 

    =MATCH(LEFT(D1,FIND("-",D1,FIND("-",D1)+1)-1),$C$1:$C$20,0)

    This formula is in cell E1 and it extracts the number from the link in cell D1. Then it returns the row number of the matching number in column C. For example 17-7 is in cell C17 which is row 17.

    =IFERROR(INDEX($D$1:$D$20,MATCH(ROW(B1),$E$1:$E$20,0)),"")

    This formula is in cell F1 and returns the link for number 16-1. 

     

    Both formulas are filled down.

     

     

Resources