How to link to another cell?

Hi guys!

I’m quite new to airtable and have no experience in writing strings. I have read the syntax page, but could not find out what i should do to get the desired outcome.

I have a column with the name “Referral URL” In this column, urls will be entered trough zapier when a user submits his/her credentials. Something like this: “www.test123.nl/test-ebook-landingspage/?ref=xd3r4n”

I have another column next to the “Referral URL” column which is called “Referral Code”.

What i would like to do is, eliminate everything before “=” and place only the “xd3r4n” in the cells of the “Referral Code” > This should be automated en run everytime someones signs up

Is there someone who has experience with this?

Looking forward hearing from you guys!

Welcome to the community, @Bryan_Delmee! :smiley: There are a couple of ways to go about this. One is to use a combination of the FIND() and MID() functions to locate that equals sign in the URL, then grab all of the characters after it. The FIND() part would look like this:

FIND("=", {Referral URL})

That will give you a number representing the character position of the equals sign in the string.

The MID() function extracts a specific number of characters from a string starting at a specific position. In this case, you need to begin one position after the equals sign, and then grab six characters (assuming it’s always going to be six; if not, you can always grab more and the function is smart enough to know where the string ends).

To make this work, you’ll nest the FIND() function inside the MID() function where you want to insert the position of the equals sign, adding one to start one character past it. The final formula would be this:

MID({Referral URL}, FIND("=", {Referral URL}) + 1, 6)

Another way to do this would be to use one of the relatively-new regular expression functions, REGEX_EXTRACT(). This formula below effectively does the same thing as the one above: finding and ignoring the equals sign, then extracting everything after it.

REGEX_EXTRACT({Referral URL}, "(?:=)(.*)")

Regular expressions can do more complex matching and extraction than what’s possible with other functions, and often do it in a more compact way, as you can see here. It’s all about recognizing patterns and learning to represent those patterns with the regular expression tokens. They take some time to get used to, but I recommend investing that time. It’s well worth it!

1 Like

Hi Justin,

Thank you! It works:).

1 Like

Hi Justin,

I’ve got one extra question, if that’s okay.

I tried the look-up functions by linking to another table, but i got the idea that this only works with date’s and integers.

The output that is generated (from my 1st question) should be visible in another table. This specific table will be presented in an embedded form on webflow. So, i have on table called “Internal Leaderbord” which has the column “Referral CODE” (generated by your code). I whish to link this values to the column “Referral CODE” in the table “External Leaderbord.”

Do you perhaps know how to do this?

Looking forward hearing from you and have a nice weekend.

Lookup fields work with any data type. However, it’s important to know that most of the time what the lookup field returns (if you’re referencing it in another formula field) is an array, not just the raw data. I’ve built a table that shows what data types to expect from a lookup field (and others) in various configurations:

Are you able to share screenshots of your setup? I can’t quite picture what you’ve got and what you’re hoping to achieve when you say that you want to link that value to another column.

Hi Justin,

Thank you for your reply.

I have attached two images.

Image 1 (Yellow) contains your formula.

Image 2 The column with the red circle is the column where i would like to have the values from the column of the yellow column.

Does this make sense for you?

Thanks. That clarifies where you want the data to go. My next question is: how/when do you want it to appear in that other table? For example, when any new record appears in [Internal Leader Board], do you want the referral code to be copied immediately over to a new record in [External Leaderboard]? Or do you want some other workflow?

If all that you want to do is create a stripped-down view of the incoming data that doesn’t contain all of the original fields, one option is to make a new view in the [Internal Leader Board] table, hide the fields that you don’t want visible, then embed that view on your website. Will that work?

Hi Justin,

Thank you for your answer! I´m trying to achieve exactly what you are describing: if a suscribers enters the “internal leaderbord” base, the generated referral code should also be published in the “external leadbord.” This one is embedded on our website, so subscribers can check their position on the leaderbord.

Is there a way to realize this? I’d like maintain the internal leaderbord as it is right know , as it is for internal use and has more data which i dont like to be displayed on the website.

Best

With that in mind, I suggest looking into views:

Views are designed specifically when you want multiple ways of looking at—and possibly sharing—the same data. Rather than duplicating the data itself, you just make a new view that only shows you want you want to see in a given context. In this case, you could have an “Internal” view that shows all the fields you want for the internal leaderboard, and an “External” view with fewer fields visible that you can embed on your website.

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.