UTM formula hyperlink is not working

Please provide: (1) the link text you’re seeing (2) what you would like to see instead (3) the formula (4) details about any fields the formula is using.

(1) this is what I am seeing(only half of the link is highlighted):
**https://www.understood.org/pages/en/families/?utm_medium=paid** &*utm_source=facebook&utm_campaign=this-is-a-testutm_content=post-w-image`

(2)I would like for the entire link to be highlighted.
(3) this is the formula I am using: LOWER(SUBSTITUTE(URL & ‘?utm_medium=’ & Medium & ’ &utm_source=’& Source &’&utm_campaign=’ &{Campaign Name} &‘utm_content=’ & Content))

Not sure what SUBSTITUTE is supposed to be doing. Try one of these:

URL & ‘?utm_medium=’ & Medium & ’ &utm_source=’& Source &’&utm_campaign=’ &{Campaign Name} &‘utm_content=’ & Content

LOWER(URL & ‘?utm_medium=’ & Medium & ’ &utm_source=’& Source &’&utm_campaign=’ &{Campaign Name} &‘utm_content=’ & Content)

If that doesn’t work, please try to re-communicate what you’re saying in (1). Your formatting is confusing.

Hey, Thank you for all your help! unfortunately the formal didn’t work. I was trying to say in for question 1:
the URL the formula is producing isn’t hyperlinking. half of the URL is a hyperlink while the other isn’t. At first I assumed it there was a space in the formula but after rewriting the formula several times there doesn’t seem to be any spaces. I am not sure what I am doing incorrectly.

This is how it looks:

https://www.understood.org/en/friends-feelings/empowering-your-child/building-on-strengths/5-steps-for-recognizing-strengths-in-kids?_?utm_medium=community-response &utm_source=fb-community&utm_campaign=this-is-a-test, covid-apr20-wp&utm_content=post-w-image

Welcome to the community, @Harmony_Gilliard! :grinning_face_with_big_eyes: Actually, there is a space in your formula, because there was one in the version that @Zollie created above. In yours, though, it’s “hidden” by the line break in your formula entry field. If you were to copy that full formula into a text editor, you’d see the space.

To nix it, place your cursor immediately after the quote at the end of that first line, and press the Delete key. That quote will move to the following line because the space has been removed.

thank you for noticing this, I went ahead and fixed it but unfortunately, it still didn’t fix the problem could there be anything else wrong with the Base or the formula? I could send you my Base if you would like to take a look at it.

Could you post some of your actual values?

In the screen shot in one of your posts it looks like there are two question marks in the URL.

?_?

?_?

this is a link to one of the records in the base https://airtable.com/tblxBdnafDwTpCxe0/viwf69BVDBvVJsdQd/recS6nijtkmxtYe71

@Harmony_Gilliard That link doesn’t work for users you’re not paying for. @kuovonne is just asking for values that the formula is spitting out. It also might help to share example values for the columns your formula depends on - so URL, Medium, Source, Campaign Name, Content. If typing is too hard, create a shared view and post that link.

@Zollie Typing is not too hard but rather time-consuming as I have been trying to fix this problem for about a week, with no solution. I am sure you could imagine how frustrating that is, especially on a tight deadline. Heres a read-only link : https://airtable.com/shr4GMQjgVpjd968A

let me know if it works. Thanks.

Yes that can be frustrating. Sorry if my remark came off as flippant. The shared view helped a great deal.

The additional space characters are being introduced from multiple places:

(1) The formula itself

Whoever wrote it left some spaces in some of the strings (a string is anything with quotes around it). I’m not sure if those were intentional or not, but here it is without those additional spaces:

LOWER( {URL} & "?utm_medium=" & {Medium} & "&utm_source=" & {Source} & "&utm_campaign=" & {Campaign Name} & "utm_content=" & {Content})

(2) Single select fields that the formula relies on

Both “Source” and “Medium” have space characters added to some of the values - for example, “twitter” is "twitter ". There could be other fields with the same issue.

You could either keep a closer eye on any fields that the formula relies on, or build your formula to correct for user error. To remove spaces, you’d use TRIM, which removes leading or trailing space characters, or you could use a function like SUBSTITUTE to automatically replace any space characters with a hyphen (as you have with the “Campaign Name” field).

Examples:

TRIM({Source})
"Twitter " => “Twitter”

SUBSTITUTE({Content}, " ", “-”)
“Post With Image” => “Post-With-Image”

Putting it all together, you’d end up with this:

SUBSTITUTE( LOWER( TRIM({URL}) & "?utm_medium=" & TRIM({Medium}) & "&utm_source=" & TRIM({Source}) & "&utm_campaign=" & TRIM({Campaign Name}) & "utm_content=" & TRIM({Content}) )," ","-")

Thank you @Zollie this is very helpful and informative I really appreciate it! I hate to be a bother but it’s still not working it’s telling me the formula is invalid which is strange. I am really not used to Airtable formulas. Heres what I am seeing :

It looks like there are a lot of curly quotes “ ” in your screen captures. Change all of them to straight quotes " .

This is a very common issue when copy/pasting formulas from the community.

Thank you its working now! @Zollie I really appreciate all of your help