Data scraping using data from excel to retrieve more specific data on website

[choc415]

Hello all,

I am unfamiliar with coding and am looking for an automated method to save time. I am trying to work with gene data and switch the general gene id (ENSDARG00000036482) to the gene description (HEXIM P-TEFb complex subunit 1) that is actually useful to me. I am currently opening excel, copying the data (it is all in column L), and pasting into Ensembl genome browser 110 in the search bar at the top right. I am then clicking the first link to the data. I copy the gene description (HEXIM P-TEFb complex subunit 1) and paste it back into excel. Is this something that can be automated with VBA or a macro? Sorry if this is not the most descriptive, I could maybe upload a video if that would help.

Thanks for any and all help!

 

[Kyle123]

The text parsing formula bit is a bit grotty, but you can do this with a formula. Assuming gene id in L1:

=LET(json,WEBSERVICE("https://www.ensembl.org/Multi/Ajax/search?q="&L1),start,FIND("description"":""",json)+14,MID(json,start,FIND("""",MID(json,start,1000))-1))