Help with IF, OR statement

[Bill_Grinstead]

I have a formula which looks for the string “geo” in my URL column {Cloaked Link} then display all text it finds after it.

But sometimes I will have the text “visit” in the {Cloaked Link} URL column instead of “geo”, so if that’s the case I want to pull out all the text to the right of that string.

How can I do that?

Here’s what I have so far:

RIGHT(
    {Cloaked Link},
    LEN(
        {Cloaked Link}
        )-FIND(
            'geo',
            {Cloaked Link}
            )-3
        )

Basically I want to combine that with this:

RIGHT(
    {Cloaked Link},
    LEN(
        {Cloaked Link}
        )-FIND(
            'visit',
            {Cloaked Link}
            )-3
        )

[ScottWorld]

If you wanted to keep it all combined in one formula, you could do a nested IF statement like this:

If(
	FIND('geo',{Cloaked Link})>0,
		RIGHT(
		    {Cloaked Link},
		    LEN(
		        {Cloaked Link}
		        )-FIND(
		            'geo',
		            {Cloaked Link}
		            )-3
    ),
If(
	FIND('visit',{Cloaked Link})>0,
		RIGHT(
		    {Cloaked Link},
		    LEN(
		        {Cloaked Link}
		        )-FIND(
		            'visit',
		            {Cloaked Link}
		            )-3
		        )
	)
)

[Bill_Grinstead]

Excellent! Now, while I have you. What if I further wanted a new column where I take the value in my {Slug} column and add it into a specific string.

Let’s say the {Slug} value is “borgata-casino”. I want to first check the {Type} column. If that column is set to (GEO) then I want to incorporate the text “geo” and the {Slug} value into a specific string. So for example:

If the {Slug} = borgata-casino/ AND the {Type} = (GEO) I want to:

Print this text: [href*=“https://sportshandle.com/geo/borgata-casino/”]

But if the {Slug} = borgata-casino/ AND the {Type} = (VISIT) I want to print this text: [href*=“https://sportshandle.com/visit/borgata-casino/”]

So essentially those two values would fit insid this text like this: [href*=“https://sportshandle.com/(either geo or visit)/{Slug}”]

Not sure if this is making sense.

Screenshot of my columns

[Justin_Barrett · Accepted solution]

Here’s what I would use for the formula:

'[href*="https://sportshandle.com/' & SWITCH(Type, "(GEO)", "geo/", "(VISIT)", "visit/") & Slug & '"]'

[Bill_Grinstead]

This is great, but I need that string of text to display whatever URL is in the Cloaked Link column, not just https://sportshandle.com/, since the site could be different. How would I insert the {Cloaked Link} there instead of that specific string of text?

[Justin_Barrett]

I’m a little confused. Here’s what you asked for:

Bill_Grinstead:

So essentially those two values would fit insid this text like this: [href*=“https://sportshandle.com/(either geo or visit)/{Slug}”]

I understand (thanks to your clarification) that it’s not going to literally be “sportshandle.com”, so that’s not the confusing point. The confusing point is that the pattern you outlined for building this “new” URL creates (as far as I can see) the same URL that’s already in the {Cloaked Link} field. If it’s supposed to be the same domain from that field (whatever that domain may be), and the same geo/visit piece, and the same slug, why not just wrap the extra characters you want around the existing URL in {Cloaked Link}?

'[href*="' & {Cloaked Link} & '"]'

I don’t understand the need to read what’s in the {Type} and {Slug} fields if they’re already part of—and in the case of {Slug}, derived from—the original URL, and you just need that extra wrapper around it.

If that’s not what you need, then I would kindly ask if you could please try to explain it a different way, because what I read above feels like you’re asking how to rebuild something that already exists.

[Bill_Grinstead]

Oh, duh, yes, I couldn’t see the forest for the trees there. The simple formula worked. Thanks so much for your help!

[Bill_French]

Bill_Grinstead:

Oh, duh, yes, I couldn’t see the forest for the trees there.

Ha ha - I often miss the entire forest and I see only a cactus.

REMINDER; In the interest of creating better outcome data in these forum threads, make sure you mark this resolved by @Justin_Barrett.