Data Cleaning using LAMBDA Function in Excel

[Music] Hello friends welcome back my name is PK in this video you will learn how we can clean our data in Microsoft Excel using the Lambda function so as you are seeing my screen I have created this remove character function using the Lambda function and here we have given the special character including the numbers and here we have some data with the special characters so we have to clean this data and we have put this remove character formula and here we have the clean data I will show you this formula and I will explain you step by step how we can create this in Microsoft Excel let me show you the base formula first so to do this we are using the substitute function and one by one we are removing the characters friends one thing I would like to tell you I haven't used any kind of VBA to create this function okay so this is pure Excel based function let's say for the first character what we have done here we have used substitute function and we have taken this text comma old text we have taken first character of the special character input data this one so we have used here left and this text comma 1 and we have replaced with the blank just close the bracket and hit the enter Because left is one if I will input here one then you can see the one is removed now we have this text we are changing the special character also now this time we are using here right of this text and we are using here Len of this minus one so basically it is taking the all character except the first one because we have used in the formula if I will use the same formula again so I can copy and paste here but I will take this text okay so now this time it has removed two you can see okay and we can fill this right again so we have removed the two from here also similarly we can fill this right till the all characters you can see here fill more right until we get the our result so here you can see finally we are getting this result okay so once we have run the loop for all the characters and we are getting blank then we will exit from the loop we will run this Loop using the recursive Lambda function let me show you how it will work I have displayed the basic function what we are doing now let's delete this and we will create the functions from from the start go to the formula bar and name manager and delete this function Lambda and the parameter we will provide first main text A5 comma then another parameter we will take the special characters okay special character which is these B2 all characters okay okay comma Now we will put our formula so we will use here if first we are putting the condition to exit from the loop if special character is equal to blank okay if special character is blank then we will take the main text else we will use the main formula so we will keep the name of the formula as remove character so let's use here remove character and inside this remove character use the substitute function and text we will take as main text comma the old text we will replace the first character of the input of special characters so we will use here left special character comma 1 and just close the bracket and we will replace it with the blank okay so that is how we will remove the character 1 by one so this remove character formula is not exist as of now we will create the formula name with this one okay now we will change the value of special characters also we will not take the first one as we have run this next time we will not take the second one then third one so we will use here right and let's take the special character comma use here Len of special character minus one and just close the bracket and close all the brackets and just hit the enter it is showing this error but don't worry about that just copy this formula and delete this go to the formula bar name manager make sure keep the name as same we have taken inside the formula that is remove character paste that formula here which we have copied Okay click on okay and close this now we can remove these all things from here and let's use here remove character formula if I will take only this cell and comma special character I will take this one and just hit the enter I'm getting the result and we can freeze this V2 press F4 and then we can fill this down so it is working okay or you can use the array also like remove character and take main text this whole range comma special character let's take this one and just close the braet hit the enter so we are getting the result for all okay you can add some other special character also in this sequence that will apply automatically inside the formula so friends that is how you can use the recursive Lambda function to clean the data this was just an example you can use in various ways in your office work friends I hope you have enjoyed this video If you like this video please hit the like button and please subscribe my channel to get the regular update of my videos thank you so much for watching

Channel: PK: An Excel Expert

Views: 9,035

Keywords: excel lambda, lambda function in excel, excel data cleaning, excel lambda examples, lambda function tutorial, excel tutorial, data management in excel, excel tips, excel advanced functions, excel custom functions, excel for beginners, excel tutorial for data cleaning, LAMBDA in Excel, Excel formulas, excel programming, non-VBA excel, Excel function library, reusable functions in excel, excel tricks, excel functions, cleaning data in excel, advanced excel, excel skills

Id: gj94dU8eUMU

Length: 6min 33sec (393 seconds)

Published: Mon Nov 06 2023