today i'm going to show you how we can remove numbers from text in excel
so i'll do a couple of versions of this i'll start with an easy version so i'm going to go ahead and
add a row on top of this not really necessary but i'm going to do it anyways so for this easy method
i'm just gonna go and just type this text with no numbers so i'll just go next to it and type dfsd
hopefully i got it right so basically just the first one with no numbers in it
i'm gonna click right below in the next cell and then i'll go under data
in my data section and i'm gonna find this thing called flash fill right here it may look
a little different on your screen but it's gonna be here sometimes it's a big button in the middle
sometimes it's a smaller icon but you'll see this arrow in there that's what you're looking for
right next to this text to columns i'm going to go ahead and click on that just once and that
should take care of it so as you can see if i look at this one it was able to remove all of
those numbers and for this one as well all set this should be fine if you don't need a formula
now i'm also going to show you how to do this with a formula so for that let's get rid of this
what i'm gonna do i'm gonna first use a function called sequence
so for sequence the way it works you give it some number let's say if i give it 20 and close
parenthesis hit enter it will give me a sequence of numbers from as you can see from 1 through 20.
so what i want to do i want to make a sequence for as many characters as i have in this text and the
way i can do that i can go back instead of this 20 i can just use this function len that calculates
the length and basically just link to this cell so this will calculate the length of that text based
on that we're going to create that sequence and see it starts from 1 now it goes until
apparently 27. so this text now has 27 characters so why do i need this
well the reason i need this is that now i can use the sequence of numbers that goes from 1
to whatever it is within this function mid and in this mid function i'm going to provide this text
right here a2 and then we do a comma and as a second argument in this function let's remove
this period that i did here we need to provide starting position that we want to extract text
from so actually let me just grab the sequence out of here i'm going to paste it back in a second
cut it so if i do for example position 3 comma and then do 1 and hit enter see it gives me d because
the third character within this text if we just grab one character would be d so this is what
this one refers to just get a single character and we want to start getting from the third character
now instead of just giving one position i'm going to remove that three and paste that whole sequence
of numbers that gives me one two three four five all those positions and if i do that and hit
enter what you'll see happens is that basically i just grab every character from this text
and we basically split it to different cells we have an array of characters so now because this
is an array of characters we should be able to work with this array of characters and since
i'm going to need to refer to that more than once i'm going to use let function to store
the results of that in a variable so i'm going to do let and i'm going to create a variable
i'm going to call it text array comma that text array will become the results of this
function which will be this array of results so that way i can just refer to that
after this comma in my calculations so what i'm going to do with that text array i want
to basically get rid of all these numbers and for that we can use different methods
for example one of them we can use filter function so i can use filter and in filter
we need to pass the array first so we're going to pass that text array that we have as a variable
which is basically this whole thing that we used to see comma and then we need to
provide basically which ones to include and which ones not to include so for example
if i do something like is number and then pass that same text array to this
which is that same variable so i'm checking if that's a number or not i'm going to close
parentheses this parenthesis is closing this i'm going to need another one to close the
filter function and finally i need one more to close my let function so let me just do this
and let's hit enter so you can see what happens so you can see i have no results and i have this calc
error now let's go back and just change this instead of ease number because right now i'm
checking if it's a number or not let's just wrap it in nut and close this parentheses here again
let's hit enter so you can see what happens if you look here what happened here is that i get
the same thing i used to have before so if i checked if it was a number i got no results
because it doesn't consider any of these a number because even though they look like a number to us
one and two the results of that mid function are actually text so it considers all of these
as text so as a result if i just check if it's a number we get no results so we get that error
but if i do check if it's not a number we get all of them that's pretty much where we are so i want
to make sure that this should be looked at as a number so what i can do here i can just take this
text array and i can just do some sort of basic calculation to force it to become a number so
for example if i take this text array right now and multiply it by one so if we have a number
multiplied by one we should get the same number like four multiplied by one should give us four
five by one should give us five if i take r and multiplied by one i should get an error
so that's what i'm going to use in my advantage so if i enter right now
you'll see that what happened i basically just filtered out all the numbers from this result
now if i go back and switch this instead of not to just get rid of that not out of here i'll keep
parentheses so it's easier for me to come back and add that back to this if i'd enter it's going to
give me the opposite it's just going to give me the numbers out of this but not text so i guess
this same formula could be used to remove text and just keep numbers too but we're just trying
to keep text in this particular example so i'm going to go back and undo this last step and now i
have my not function here so i was able to remove numbers from the array the last part is to make
sure that we put this all together in one piece of text and that i should be able to do by just going
around this and adding can cat function and i'm going to do concat go and close parentheses all
the way down here hit enter and there we go we got our text with no number in it with this
formula so at this point i can just take this and drag it down and it should just work for all of
them and that should do it thanks for watching please subscribe and i'll see in the next video