How to add login credentails to a bunch of links in excel

JMak

JMak

Yeah.. I am at it ;)
Adept
Hi
I have a excel sheet that has like 75 different links to a few files on a file host, stored in a column with
An example is

The string after .com/ is different in each link.
To download them (rather leech them to a different hosting ) I have been given login I'd and password of the host .
I wish you add the login I'd and password to each link so that they become direct and could be downloaded at any location ..
Something like

How can I do that with one click in excel .
I saw a string butvas I am not that savy so couldn't do it

I have the links stored in A1 column in excel sheet

Thanks
 
One trick you could use if you have username and password stored in separate columns is extract the text after https;// part, append the username and password to the url and reattach the https;// part.
Assuming A is link,
RIGHT(A1,LEN(A1)-FIND("/",A1)-1)
Click to expand...
will give you everything after the https:// . This basically looks for the position of first '/' then increments that index by 1 and gets everything to the right of it.
I am assuming all first parts are https://, If not then you will need to extract the first part as well using
LEFT(A1,FIND("/",A1)+1)
Click to expand...
Assuming column B is username and C is the password
"https://"&B1&":"&C1&RIGHT(A1,LEN(A1)-FIND("/",A1)-1)
Click to expand...
should embed username and password into the link or if the links have different first parts
LEFT(A1,FIND("/",A1)+1)&B1&":"&C1&"@"&RIGHT(A1,LEN(A1)-FIND("/",A1)-1)
Click to expand...
If the username and passwords are stored elsewhere you will need to replace B1 and C1 with the appropriate lookup based on where they are stored.

Hope this helps
 
Last edited:
User I'd and password aren't on the Excel sheet, but the credentials are same, its just a matter of adding the same credentials after http:// to every link and also have @ at th3 and if the password

Any simpler way? coz I am 0 when it comes to such things
 
Last edited:
JMak said:
User I'd and oasswird aren't on the except sheet, but the credentials are same its just a matter of adding the same credentials after http:// to every link and also have @ at th3 and if the password

Any simpler way could I am 0 when it comes to such things
Click to expand...
Just copy paste this as a formula, replace "username" and "password" with what you got
LEFT(A1,FIND("/",A1)+1)&"USERNAME"&":"&"PASSWORD"&"@"&RIGHT(A1,LEN(A1)-FIND("/",A1)-1)
Click to expand...
 
Back
Top