How to Split Data in Excel & Google Sheets

Key Takeaways

Please kindly share and subscribe to everyone. It may useful to others also. Friends, this is Excel guru Rajin Khan. This query asked by one of of my friends. He went for an interview. So they asked this sort of query. So he's unable to solve it. So he asked me to solve this. Okay. So here is the both things are there. First of all try to understand that here both things are there. One thing is the text and numbers. You have to extract only the numbers. Okay. See before going to show the solution. First of all I'll copy this one and I'll paste over here because I have to show you the solution. Now see if I change only one here it will extract one. If I change 1 2 3 comma a b c comma wxy. So that it has to extract only 1 2 3. Okay, understood. Now I will show you how to do that. No need to confuse. It's very easy. Just you have to understand the concept then you'll able to understand. See first of all I want to extract each and every value from the data is equal to mid of substitute of so this is the delter so I used comma here comma is the delimter and I'm locking only the column but not the row comma old text comma and New text you have to repeat 255 times. Why 255 times? It's a maximum number in a Excel. Okay. Close parenthesis. Here it's completed. Now new text. What will be the new text? See here is the old text. Here is the value. Here is the old text. Here is the replacing with the new text that is page. Now starting number. What I'll do? I use 1 into 255, 255. Okay, then it will extract only the 1 2 3 F9. See with a lot of spaces. Okay. If I type two here, it will extract second that is XY Z F as 9. Okay. Now I want only the numbers. Okay. So what I'll do here? I use one functions row of indirect so many times I explain this one I don't want to explain once again why I'm using this type of this okay locking the column but not the row close parenthesis close parenthesis and close parenthesis okay now I do F9 9. See all numbers are there including empty strings. Okay. Now I want only the numbers. I'll copy this one because I have to use so many times. Double negative F9. See text are removed and converted into a value. Now it is showing only the numbers. Okay. text number into a number is equal to number. Okay, this is the thing. Now what I'll do here I just do one thing small if is number if is number if you find any number close parenthesis you find if you find any number in this row of indirect again I'm using this one% length of this the same formula. See here I use the same formula. Okay. Blocking the column close parenthesis. Close parenthesis. Close parenthesis. Okay. I don't want false. Close parenthesis comma the K value one. F9. So it will restrict one. If I type for k value for the sum two it will reflect two the smallest value. See what it will show you enter. I will explain you here logical test. F9 see all truths are there here. F9 all 1 2 3 wherever it find the true it will pick up the corresponding from here from here to here okay the K value two so it will pick up the second smallest so I don't want like that I don't want like that I want like this columns of columns of the form line B7 I lock 7 colon B7 7 close paras okay this is the formula enter now I'll drag this I'll show you all magics control D so somewhere I forgot to lock where I went lock somewhere I wait a minute I have to somewhere I forgot Everything is right only has to come. Yes. Yes. Okay. Three. Now one and three are numbers. One and three are numbers. Only one is numbered. One. Three are numbers. 1 2 3. Now I'll do one magic over here index of just I copy this one before starting the uh solution I copy this one. Okay. So I place in in place of array comma close parenthesis. Enter. Control R control D. F2. If you find any error in this function. If error. If error show me the blank extracting only numbers when it is having numbers and a text in a value. Okay guys, thank you guys. I'm expecting more views and uh subscriptions.