Fomula Help - How can I separate out multiple links from a field?

[Adrian_Waldron]

I have multiple affiliate links and descriptions that i would like to separate from their linked record. I’d like to have D1 (Description 1) and L1 (Affiliate Link 1) to be CONCATENATE(D1, L1)

However, with the lookup field pulling in the affiliate descriptions and links, they are all listed as D1, D2, D3, etc. & L1, L2, L3, etc.

How can i separate them from that list and pull them into another field for other use? While maintaining a correlation between D1 and L1 and D2 & L2, etc.

[Justin_Barrett · Accepted solution]

Welcome to the community, @Adrian_Waldron! :grinning_face_with_big_eyes: If I read you correctly, you’re trying to build the HTML tags for the affiliates tied to those linked records, correct? If so, I suggest changing up the process a bit. Instead of making all of the links in the table where you’re doing the lookup, add a formula field to your [Affiliates] table (guessing the name) that builds the HTML for each affiliate. That way you can look up those fully formed links, and you won’t have to reassemble the pieces when you have multiple affiliates linked in your main table.

[Adrian_Waldron]

This worked like a charm! I created a formula in the Affiliates Table that CONCATENATE() the HTML code I needed, then Looked up that field in the speaker list to then formulate it to be on separate lines and actually usable in context. Thank you! Here are some screenshots of the final product.

[Justin_Barrett]

Glad to know that you got the answer you were seeking! It looks like your initial formula is a bit more detailed than it needs to be, though. You’ve got several small segments with a single character in them that don’t need to be isolated. You could combine them like this to simplify the formula a bit:

CONCATENATE('a href="', {Affiliate Link}, '" target="blank">', Description, '</a>+')

Along that same line, I noticed that you’re doing a double-replace in the {HTML Copyable} field: you’re replacing the “+” with a newline, then in the same formula replacing the newline with two newlines around a line break tag. If the end result is going to have that tag, just put it into the original. You also have CONCATENATE() around a single field with nothing else, which isn’t necessary. In fact, that entire formula probably isn’t necessary if you add the line break tag in the original formula. I also suggest changing from the CONCATENATE() function to using the & operator, which achieves the same result.

In the end, all of these changes give you this for your {HTML Compile} formula, which doesn’t need any further modification after lookup:

'a href="' & {Affiliate Link} & '" target="blank">' & Description & '</a>\n<br>\n'

[Adrian_Waldron]

Thank You Justin, I originally used ‘"’ due to for some reason the formula recognized the included " in the text as starting another Command (I guess you could call it that lol) But that makes a lot more sense! The only further modification I had to do, just to make it copyable straight into HTML, was substitute the commas that the lookup field places automatically to separate entries. But otherwise, it works great! Thank you!