 Hello everyone and welcome back to another video. In this week's video I'm going to be looking at a couple of different ways But really quick ways that we can use to remove or extract text from an alphanumeric field So you can see on the left here We've got a few examples and these alphanumerics are in all in different formats So you can see we've got some letters at the beginning followed by numbers Letters and some more numbers and obviously single letter at the end there And you can see what different variations on this and we want to look or understand how two different ways of how we can extract These the text so that we will remove the numbers so we're left with just the text There's another way to put it So I've got two examples one is using flash fill and the other one is using power query Both of these are available from Excel 2013 or after so that should cover the majority of Excel users The versions that you have. So these are just a couple of ways These are by no means the only ways you can do this But I wanted to try and focus this video around moving away from complex or extensive formulas And just showing you the actual built-in function of an Excel to be able to do this particular solution So the first one we're going to be doing is using flash fill and it's probably the most easiest out of the two or any Any potential solution for doing this all we need to do with flash fill is look at our first example here And we can see that we've got just number and s at the end So we need to type in here what our desired outcome us our desired outcome is so for us that would be just number and The s at the end so just numbers So what we've done is yet to put in there what our desired outcome is from start this starting point All we don't need to do is select this particular cell We go to our data tab and then you can see we've got flash fill just off the center here If you click that button you can see it's going to flash fill and fill the formatting what we've applied all the way down and You can see it's done exactly as we required So you can see the one below is pulled at just the way apple is pulled Excel from the one below that a Bit of gobbledygook for this one here, but then everything else has been pulled as required So the only one thing to bear in mind with this is you need to pick a good example For your first one that you're going to copy down So for us we wanted to pick some we know that there's going to be texted various points within this String here, so we wanted to pick something that had text at the beginning the middle and the end as our starting point So that we could flash fill that formatting all the way down So with that in mind It is the real simple solution to achieve what we wanted to do here The only thing to remember with this is because there is no formulas or there's no connectivity here Obviously every time this data updates on the left you're going to need to do that flash fill again on the right Not really a big ask, but I guess if you had extensive data, then that is something that you probably need to bear in mind The second option that we're going to look at is using power query So you might have already been used to power query or if you're not this could be a good introduction for you So power query allows us to do a number of sort of extensive Transformations with our data and I say transformations because that's the one we'll be looking at today If you haven't seen power query before then I'd still definitely suggest looking into it more because it could save you a lot of time And we're going to move a lot of complexity from your work If you'd like to see us do a video focusing more around power query Please leave us a comment below and we'll make sure that we get to that and do that video So we can show you Well, I say do that videos could be a multitude of videos, but yet we can obviously address that for you as well So for power query we need to first convert our Source information into a table to do that. I'm just going to take a copy and go into another sheet and I'm just going to enter those values in here And I'm sure you'll be very aware of how you do a table. So it's going to insert table Just want to make sure that my table has got headers as we've got here So we can go okay Once we've done that the next thing we need to do is we just then go into our data tab and we can go Into from table and you can see I'm in the get and transform data section. So go from table range And you can see that it's loaded our table with our source information for us All we're going to do from this is we go into add column and we're going to go into the custom column So if we go into this this allows us to add a custom common a column That is basically built based on our entered criteria For this we're going to enter into here We're going to put no number data in the logical name to call it and then we can step into our formula So we're going to obviously sort of skim over this a bit But I'll try and go into enough detail so you understand what the form is doing But obviously not going in too much that it makes this more complicated than it needs to be Okay, so what we want this formula to do is we want it to search for and remove the numbers The part of the form is going to do that is we're going to do our curly brackets And we're going to do and normally what you could do in here is do a zero do two Full stops and then do the number nine do your curly brackets and then what that part of the formula tells To power query is we want you to search from zero all the way up to number nine So by putting this bit of code in here It basically saves you from having to put one all the way through to the number nine But because we're going to be obviously referencing an alpha numeric field We actually need it to look for the text version of those numbers So to do that, we're just going to enter each one of these into quotations so that it course He converts it to text and then once we've done that we can then enter the rest of our formula around it So if we're going to start here and the formula is going to be the function We're going to be using is text dot remove. So that's that top one. You can see here And then once we've done that we're going to open or do another brackets and Then we can just double click our alpha numeric here. So this is the field you want to reference Double click like that We then do a comma and you can sort of see how the form has now started to come together So we want to remove text from this field and obviously these are all the The text values or text number values that we want you to search for and remove and then just to close Or finish it off. We do a last bracket Select okay, and you can see how that has now converted those for us So this is the final result. We can see that it's removed all of the text and just Sorry, it's removed all of the numbers and this lift left us with the text values instead So once we're fine and happy with that we can just do close and load and You can see it's obviously refreshed our table for us and we've now got our desired numbers here So all I'm going to do now just a slightly cheap is go copy Go into here and I can now paste those into his value here Just change the title there, but there you go. You can now see we've got our text values there as well So in summary of that video Obviously the most quickest and easiest way to do it is by using the flash fill But hopefully that so there's a bit of an introductory video as well to show you how to start using power query and the benefits are using power query Power query is pretend particularly of benefit if you're pulling data in from an external source Or you're pulling in a lot of information and you just want to spit out that outcome So two options there for you to start with but hopefully that gives you a solution to that problem if you come across it If you did enjoy today's video, please do give the video a like It's not only very beneficial for the YouTube algorithm It also is very beneficial to me because it shows me the videos that you like and would like to see more of If it's your first time of finding the channel or if you have been on the channel before Please don't forget to hit the subscribe button and also hit that bell notification button So you're notified of our future videos. So thank you very much for watching and I should see you in the next video Before you go, don't forget to check out the other videos on our channel You'll see everything from other functions and formulas through to tips and tricks We've also created some playlists so you can see these categorized together So make sure you check those out and get all those useful information And obviously as always don't forget to subscribe and hit that bell notification button