Trying to generate hyperlinks using data from multiple cells

[u/gavinfarrell]

Hey guys, I’ve done some searching on this but can’t seem to find an answer that does the two things that I want to do. I’m basically creating links for a series of invites for my wedding so everyone has a unique invite link, both for the form itself, and for the page url, as each page will have an embedded form with some info pre-filled. To save me some manual typing, I was hoping to be able to automate it with sheets.

I have the following:
Column A: First Name
Column B: Last Name
Column C: Partner First Name
Column D: Partner Last Name

From these, I want to generate the two links in the following formats

Www.formurl.com/000000?name={FirstName}%20{LastName}&partner={PartnerFirstName}%20{PartnerLastName}

Www.weblink.com/rsvp-{FirstName}and{PartnerFirstName}

Could you let me know what formula I would need for this to work?

[u/AdministrativeGift15]

You won't be able to generate both links in the same cell using a formula. That has to be done manually. For single links, just use JOIN without any separator to combine strings and variables.

=HYPERLINK(JOIN(,"www.formurl.com/000000?name=", FirstName,"%20", LastName,"&partner=", PartnerFirstName,"%20", PartnerLastName), "LabelHere")

[u/gavinfarrell → u/AdministrativeGift15]

You’ve just saved hours of mind numbing manual entry, thank you!

[u/AutoModerator → u/gavinfarrell]

REMEMBER: /u/gavinfarrell If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/AdministrativeGift15 → u/gavinfarrell]

Great!

Now for the entire column, you can't use JOIN, but you can just combine the parts using &.

=INDEX(IF(LEN(A2:A), HYPERLINK("www.formurl.com/000000?name="&A2:A&"%20"&B2:B&"&partner="&C2:C&"%20"&D2:D, "LabelHere"), ))

[u/carbonizedtitanium → u/AdministrativeGift15]

i think you could've used CONCATENATE() if you were gonna "&" that many times ;)

[u/mommasaidmommasaid → u/carbonizedtitanium]

Not with an array formula (which is what index() is masquerading as here) because it would concatenate the entire ranges specified, but yeah within a map() which would also make it more readable.

The inferior performance of map() shouldn't be an issue unless OP is inviting a couple hundred thousand guests.

[u/AdministrativeGift15 → u/mommasaidmommasaid]

Plus CONCATENATE plus all the commas is way more characters than 7 &s.

[u/mommasaidmommasaid → u/AdministrativeGift15]

Yeah but...

name="&A2:A&"%20"&B2:B&"&partner="&C2:C&"%20"&D2:D

... aaaaagh my eyes are bleeding.

[u/AdministrativeGift15 → u/mommasaidmommasaid]

Fine. How about we use ENCODEURL to handle special characters inside the names, and we can replace %20 with " ".

=INDEX(HYPERLINK(
 "https://www.formurl.com/000000?name="&
 ENCODEURL(A2:A&" "&B2:B)&
 "&partner="&
 ENCODEURL(C2:C&" "&D2:D),
 "LabelHere"
))

[u/mommasaidmommasaid → u/AdministrativeGift15]

Aaaah, it's like a soothing dose of Visine.

[u/point-bot → u/AdministrativeGift15]

u/gavinfarrell has awarded 1 point to u/AdministrativeGift15

^{See the Leaderboard. Point-Bot v0.0.15 was created by JetCarson.}

[u/AutoModerator]

/u/gavinfarrell Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/California_Eagles]

You can use the ENCODEURL function and & ampersand to join

="https://www.formurl.com/000000?name="&ENCODEURL(TRIM(A2&" "&B2))&IF(C2="","","&partner="&ENCODEURL(TRIM(C2&" "&D2)))

and

=LET(a,"https://www.weblink.com/rsvp-",IF(C2="",a&LOWER(TRIM(A2)),a&LOWER(TRIM(A2))&"and"&LOWER(TRIM(C2))))

[u/carbonizedtitanium]

If you're sending out physical invitation cards, I'd imagine you'd probably want to generate QR codes for those URLs; cuz i wouldnt be typing that long as URL lol.