Format Instagram URL filed

[Gustavo_VD]

What would be the best way to format and clean a field with Instagram URLs?

I have a CRM built on Airtable and one of the fields is the contact’s instagram details. But some of them have been entered as ‘@myinstagram’, some simply as ‘myinstagram’ and some as ‘https://instagram.com/instagram’.

What would be the best way to format all of them as URLs?

Thank you!

[Chris_Guthrie]

You could use a formula field that will be the new output of the uniform output in your desired format.

The formula would look at the input field (the field with the three different formats) and format to your desired format.

[Gustavo_VD]

Nice and simple!

Then use a series of string functions split and format the output, correct?

I’ll give it a try!

[Gustavo_VD]

I spent a couple of hours playing around with the formula filed, but it turns out it’s not so easy as it sounded.

I have entries that can look like these on my record:

https://www.instagram.com/brand
https://www.instagram.com/brand/?hl=en
@brand
brand

And I want to be left only with the ‘brand’ part. Which is the instagram handle.

The formula I came up with is:

LEFT(
	SUBSTITUTE(
			SUBSTITUTE(
				{Instagram},
				"@",
				""),
		"https://www.instagram.com/",
		""),
	(SEARCH("/",
		SUBSTITUTE(
			SUBSTITUTE(
				{Instagram},
				"@",
				""),
		"https://www.instagram.com/",
		"")
	)) -1
 ) 

That is:
SUBSTITUTE I’m removing the @ in case it’s present.
SUBSTITUTE Removing the “https://www.instagram.com/” in an attempt to be left with only the handle.
LEFT discards anything that comes after the last “/”

The problem is, for the entries that don’t have “/” in them the formula returns nothing. Because the LEFT formula doesn’t have a starting point.

[Gustavo_VD]

Then I tried something more complex. Instead of using the LEFT function to start with, I tried another SUBSTITUTE to eliminate anything that comes after the last “/”.

Also no success.

SUBSTITUTE(
	SUBSTITUTE(
		SUBSTITUTE(
			{Instagram},
			"@",
			""),
	"https://www.instagram.com/",
	""),
	RIGHT(
		SUBSTITUTE(
			SUBSTITUTE(
				{Instagram},
				"@",
				""),
		"https://www.instagram.com/",
		""),
		(LEN(
		SUBSTITUTE(
			SUBSTITUTE(
				{Instagram},
				"@",
				""),
		"https://www.instagram.com/",
		"")) - 
		SEARCH("/",
			SUBSTITUTE(
				SUBSTITUTE(
					{Instagram},
					"@",
					""),
			"https://www.instagram.com/",
			"")	
		))
	),
"")

[Justin_Barrett]

Your first attempt was pretty close, but I think what was hijacking the process was the order you chose for removing the pieces.

I started by first removing the most difficult part—everything from “/?” to the end—using this formula:

LEFT(
    Instagram, IF(
        FIND("/?", Instagram),
        FIND("/?", Instagram) - 1,
        LEN(Instagram)
    )
)

If those characters are found, the LEFT() function is fed their starting position minus 1; otherwise it’s fed the entire string length. That gets us this far:

Wrap that in a couple of SUBSTITUTE() functions to remove the other known items:

SUBSTITUTE(
    SUBSTITUTE(
        LEFT(
            Instagram, IF(
                FIND("/?", Instagram),
                FIND("/?", Instagram) - 1,
                LEN(Instagram)
            )
        ), "https://www.instagram.com/", ""
    ), "@", ""
)

[Chris_Guthrie]

Very nice. Formulas has never been my strong suit. Thanks for jumping in @Justin_Barrett.