How to Extract Text with an Airtable Formula

if you want to extract text using a formula then this video is for you i'm going to be showing you how to write formulas in air table that will extract certain parts of a longer text string so if that's of interest stick around and let's get into it hey my name is gareth pronovost i am the owner at gap consulting where we help you to organize and automate your business and life if that's of interest and you want to learn more about how we do that definitely check out our website i will include links below and don't miss our free airtable crash course it will get you up to speed quickly and easily within airtable but without further ado let's just talk about today's topic and it's all about text extraction so when you've got a long chunk of text or a long string of text sometimes you just want to get a little snip of information out of it and so for that you're gonna want to know some text formulas that will allow you to extract exactly what you're looking for in that long string so let's just go ahead and jump right into my screen and i am going to be using the images as an example here so sometimes you might want to know the url that airtable has created for a specific image this is a common use case where you might store images or attachments inside of airtable and when you do that actually an image url is created for that particular attachment and so anyone publicly with that uh url can then view that particular attachment in order to get this piece of information out of here though you're going to need to know a little formula magic so the first thing to note is that if we write a little formula called image url we can then use a formula here and let's just reference the image field now you might originally think that this is going to just duplicate the image field but in fact what it does is it shows us how airtable is thinking about this image on the back and side of things so as you see each of these different images has its own name and its own file type but then there is a url that follows it so in this example here i've got this particular file is my youtube branding file that we use and it's just an image of our logo and you see that in the image url that corresponds to this it still has the same title that starts off the entire thing so it still says youtubebranding.png and then after that there's an open parenthesis and here is where this particular url is then uh displayed so everything following that open parenthesis all the way to the second to last character in this string is that url that i want to extract and in fact if i were to click on this url over here you'll see that by clicking it it will open up that image in another another tab so that is exactly what i want to extract in this particular case so let's talk about step by step how we can write some formulas that are going to or a formula using multiple different formulas that are that is going to extract that right piece of information for us so the first thing to note is we are going to be using the mid formula so the mid formula if we just write it out you'll first get an idea of what the syntax of this formula is and it takes three different pieces of information and if you're new to formulas in air table do know that this little yellow box over here to the side where my cursor is this is helping us understand the syntax of the formula so it's saying look this takes three parameters parameter one is the string parameter two is where in that string do you wanna start and parameter three is how many spaces or characters do you want to go and so if we look at this example down here at the bottom we see that the string that it's referencing is quick brown fox and it starts in position 7 and it goes five characters and so if we were to you know kind of think through this q u i c k space b so b is the seventh character and then if we count out five characters from the b b-r-o-w-n is takes us to brown right so that is why this output here it says look i'm looking at this big string of text quick brown fox and i'm going to start in position seven and i'm extracting five positions from there so if we looked at our image as the text that we wanted to start with look at our image let's since we don't know exactly what position we want to start at let's say we start in position 25 and we want to go 500 spaces now of course this is not going to be the fully baked solution but you can see that we're starting to get somewhere here what it's doing is every in every case it's looking at this this output and it's starting in the 25th position and it's returning 500 characters now of course in our example we don't want to always start in position 25 and we don't always want to return 500 that those two numbers the starting point and the length are completely dependent on the specifics of that particular image so we're going to need to solve those pieces independently of one another but ultimately this mid formula is going to get us where we need to go now the second part is finding out instead of starting in position 25 every time how do we find out where to start and for this we can use a find formula so let's use a find formula and we'll call this starting point now the find formula is similar to mid in some ways but in here again looking at the syntax here we are going to use the parameters of a string to find and where to search and then we have an optional parameter of start from position we're not going to use that for this particular video so go ahead and ignore that for now we're just looking at the two parameters string to find where to start all right so in this case we are going to look down here at the find formula they put an example for and we see that it's looking for the word fox inside of quick brown fox and it outputs a number that is the character count where it starts to find this word fox and so if we were to you know count out quick brown fox the f of fox is appearing in position 13 of the quick brown fox string so we need to do that very thing we want to find out where does the url start and the easiest way in my opinion to do this is to look for http because every time we create an image it is by default also creating a public url inside of airtable so if we look for that http then we know that that's where the url starts so let's look for that the string we want to find is http and where do we want to look we want to look in that image field for that string and so once it finds http it's going to tell us ah yes i found that starting at this particular character count so let's go ahead and save this and see if that works as we expect and sure enough it does it's telling us hey position 23 is where this http is and this one is position 15 and this one is position 26. so now we can take this fine starting point that we calculated and let's go back to our mid formula and replace the 25 because we know we don't want a hard-coded 25 every time we want it to be flexible with us so we're going to go ahead and paste in the find formula that we just wrote and by saving here we are now getting even closer to our end goal so now what we have is the begin the right beginning spot for our mid formula and you can see that each of these is turning out correctly starting with http now the last part we need to do is make sure we don't include that closing parenthesis so we need to find the length of our mid formula here so right now we have this hard-coded 500 and we don't want that we need to replace this with a dynamic length that is telling us exactly how many characters we need to count out so how can we find that length again we can use the find formula and i'll call this one length and for our for this what we're saying what we're going to say is all right we've already solved for the first part that is where the http starts and then we need to count out till the end of this thing and so if we knew what the total length was and we subtracted the beginning part well then we could solve for this missing part right so if we know you know one plus two equals three well then we know that three minus 1 equals 2 right so that's what we're going to do here in order to get this thing to work for us so the first thing we do is use a length formula and the len formula is basically just saying i'm going to tell you how long something is so if i bring in lem for the image and save this is going to output the total length of characters that are present in each one of these different fields so 121 characters in here 106 characters 126 that's the total length now if i subtract the starting point so let me go in here again and solve for that starting point if i take that formula and subtract it from the length now i have the remainder and so basically what i've done is i've said i know where my starting point is i know my total length is and so i can say total length minus starting point equals the part i'm looking for so i'm going to take this formula now copy that whole thing and i'm going to replace my mid formula where i have that 500 and i want it to be a dynamic count every time so by saving this up now i have the perfect formula that is generating my image url and extracting it from here so again we've used a couple of different fancy formulas here specifically looking at the mid and the find formula there are some other text formulas that are very similar to these and they work in similar ways you have a left formula a right formula and i'm sure some others as well so do play with these and let me know what roadblocks you come up against and what questions you might have shoot those below in the comments as always i hope you found that to be very helpful if you did and you'd like to learn more swing on by our website and check out all the resources we've put together we have a free airtable crash course that will get you up to speed quickly and easily in air table and we also offer some paid services including hourly consultations with our experts we have some online group coaching programs and courses and for the very advanced needs we can build a bespoke project for you from scratch so swing on by and i look forward to connecting with you soon you

Channel: GAP Consulting

Views: 2,108

Keywords: airtable, gareth pronovost, airtable training, airtable consultant, airtable consulting, airtable demo, learn how to build automation, airtable formulas, airtable text extraction, airtable MID formula, airtable FIND formula, help with airtable formulas, learn formulas for airtable, airtable tutorial, GAP Consulting

Id: bKEqReb3RTk

Length: 11min 50sec (710 seconds)

Published: Fri Oct 30 2020