Group Query Adding total sales when it is having random delimiter like inches,kgs,Ton

Key Takeaways

hi friends this is excel guru rajinikanth and before starting this session i will explain you this first of all i'll give you some interaction about my channel that is excel guru so here in excel guru channel all interview questions with solutions all interview questions within solutions so really from repeated companies in in hyderabad for the post of mis so all of it totally nearly i collected 2 000 plus interview questions from reputed companies in hyderabad so here this is the query asked by one of our friend in group regarding see what actually the query is but the following explain about this query what it is actually see so here is the randomly it is there see rupees 456 420 kgs kg is 470 like this so you have to cut only the numbers but and remove all the characters and add only these numbers without helper column okay for clarification i did this one the sum of this is 292. see how i'll do i will explain you and another query is there is a plus delimiter between the numbers so you have to read off the those plus characters and add those numbers okay this is these are the two things so first of all i'll start this one here i will start what i will do first of all say what how i'll do substitute of from starting i'll explain you i'm adding space to all those things adding space comma and the old text what will be the old text so i'll add this one the space will be the older text and the new text i will add repeat of these many text comma 255 times into not into this one right so after i press f9 what okay repeat 255 characters wait a minute here somewhere went wrong correctly the text is this old text will be the first and new text will be just yes then why it is giving [Music] here i am adding see how why it is giving value it should not give evaluated actually somewhere something went wrong f9 and all of ours why it should not give us wait a minute made of number of substitute of the text this one and the starting number when i give 1 into 255 and the number of characters is 255 so it will pick up all the first and it will read off all the spaces and it it will give you this first word first word of inert range that is f9 the kg or what i'll do i'll trim extra spaces are there so i'm using trim function to read off extra spaces see rs 420 kg is five only first characters kg 580 mtr 486 so if i'll give to f9 it will accept only the second word after the space delimiter which is got my point so what i'll do instead of doing all those things i want one two three four five six seven like that here in place of two in case of one or a two so what i'll do i'll use this function row of this whole thing f4 minus row of this f4 plus one right here is the thing here you will get all the 1 2 3 but here you will see 255 256 because i multiplied with 255 so what i'll do here i will do this if i press f9 it will not give the proper answer right so what i'll do instead of doing all those things transpose i'm transposing into rows into columns still here f9 see rs4 rs456 see 420 kd kg470 580 ton mtr580 yeah 486 inches right so those are numbers in a text format so you have to convert into a numbers in text numbers into your numbers by using double negative f9 see value arrays are there and text numbers is converted into a numbers like 456 456 420 all those things now what i'll do here you have to read up all the errors by using if because if you do directly it will not it will give you the error it will give you the error not the answer f9 so you have to convert this into something which is familiar with everyone that is fairer if you find any error in this value so may be blank now what will happen it will give only the numbers but not the error f9 see you here i will do so that you can able to understand easily s9 okay what happened here again value okay here you have to give um now press c if what i will do from here is f9 again what happened okay i did not kept comma over here if error vitamin d [Music] f9 see 456 420 for all only the numbers are visible and the errors is converted into a blank okay for a while i'll keep like this only for at least 10 to 5 seconds so that you can see you can upload easily got it now if you press enter what will happen it will give you value error everything is correct then also it is giving you value address so what you have to do you have to use the special character that is control shift and enter if you'll do some of this here i'll guarantee some of this whole thing is 2092 2092 got it what i mean to say now coming to this part okay the same procedure as thing is nothing is changed substitute here the delimiter is plus sign here the limit is plus sign right and comma all text will be the old text will be just placed only because already delimiter is plus and the new text i'm replacing with space 255 times this thing and the starting number same like that i will give you so that you can able to understand that easily at night there is a lot of spaces so i read off spaces by using a trim function f9 see first character if i instead of one if i'll give 5 count how many are there 1 2 3 4 5 so 23 will come ok so here what i'll do i need same like row here i can do row like this but here how can i do that one row all those things i did go minus row all those things simply up the center and i will show you that function where i used here pse if the values in one and one cell all the values in one cell if it is a ranges you can use this function if it is in one cell like this how you will do don't worry i'll show you which is the five what i'll do row of indirect of [Applause] ampersand length of we don't know how many are there so length of this thing close all the parentheses for the row and check your starting number f9 okay here what i'll do uh okay i will highlight everything and i will show you f9 okay gone everything is gone i have to get five is that so i'll remove five from here [Music] okay i will use the same function comma starting number row of indirect ampersand length of [Applause] this length of b12 what happened again it has gone over some beds length of b21 into 255 is closed or not text will be this starting number okay you have to close one parentheses over here and remove one parenthesis from there f9 again what happened yes already went wrong again we'll check once again text starting number of c again one more parenthesis over here and remove one parenthesis from here f9 again what happened f9 somewhere went wrong [Music] with the root i have to close the door function here it is there right only properly now let me check starting number yes f9 number of characters yes i'll use mid function first of all f9 everything is right okay again i will do once again sorry guys again i'll do somewhere went wrong made of substitute of adding plus sign ampersand to this comma old text will be the plus sign only and the new text i am replacing with space 255 times new text and starting number is row of indirect of starting from one and we don't know the length of the value so i'm using length function over here to get all the characters from one to end of that i'm closing still row comma not here into 255 comma 255 okay let's check it out yes it is there now i'll use trim function okay two functions to follow the string function trim to move all those things okay converting text numbers into a number by using double negative f9 what happened here went wrong let me check whether where it went wrong everything is correct only i don't know where it went wrong 12 yes converting double negative first of all listening check double negative f9 why it is coming like this okay here you can see 12 45 36 39 all all the numbers so what you'll do if errors if you find any error in this function [Music] so give me d blank f9 3 twelve four fifty six thirty six four and remaining are all blank where it finds the error value and simply use some product for this some product and here also i'm using just enter it will give you the wrong answer so converting into this function into an array function by using ctrl shift and enter f2 ctrl shift enter if you want you can check it by adding those numbers and if you have any doubts ask me in the comment box thank you guys thanks for your support like share and subscribe my channel