Technology Software

Video: How to Separate Text From Numbers in Excel

Video Transcript


Hi. I'm Justin Conway. I'm an Excel expert, and I'm going to show you how to take numbers from a long string of information and extract them into other cells. So for an example that I set up here we have sort of a poll that was taken at a concert. And three acts played and different people recorded different information in a different way. So it would be easier if they said, you know the first person said a hundred people like this, 200 hundred people like this, 300 people like that because all the numbers would be in the same place in the beginning of their sentence, in the beginning of that phrase. But in the example that I've set up in this Excel sheet you can see that 600 people all like Justin Conway. News fans were fans were 6,012, these numbers are in different places so Excel needs to be kind of smart to figure out where the numbers are first and what to extract. So what we're going to do in this case is use the mid formula with match. There's several formulas used here. It's pretty complicated to get in to. What I'll do is I'll just leave this formula up here for you to take a look at and you can copy down. This is basically, this is the nested formula that I used to parse out the information and as you can see it took the number 690 from this string of information, left all of the alphanumeric behind and just is displaying in the B column the numbers that were in those phrases. So up here again, it's a nested formula, it's going to look for a certain string of information and it's going to look to a certain number amount. Another thing I'll draw attention to real quickly is just that this number 39 is the, is the maximum amount of characters that it'll count into. I have this also repeated over here, 39. So what you'd want to do is if you have like a long paragraph which would be a massive computation being computed in Excel. But if you did have that much information you would want that number to be more like a thousand or two thousand or something so it could through all of the words in the paragraph and extract information. You'd probably come in to some issues if you did this with a whole paragraph. But this is a way to do sort of a simple function on a small sentence. Again, here is the, here's the entire formula. You could play with that a little bit in Excel. Sometimes it's kind of fickle and you have to play with the numbers, make sure that it's linking back to the proper cells and it will extract the information for you. That is how to extract numbers from a string of information in Microsoft Excel. My name's Justin Conway. I hope you enjoyed.

Leave a reply