I found an interesting hack to this by mistake. Just copy the column and paste it in a column in google sheet. I had opened an excel file from my drive using google sheet to work on it for this and it worked for both. SO i have been able to finally copy a whole column that that a formula which looked like =HYPERLINK(B2,TRIM(A2))
best kausik