Seperating first and last Name by space using function

Key Takeaways

hi guys welcome to excel Guru here I am going to show you the amazing interview question actually this is the clearly asked about now for our branding foreign okay here I will show you the amazing trick how is it possible to insert a state between two words without having any delimiter okay without having any delimited if you have if you want to insert a new space or you will do okay so first of all whatever what I will do here I'll do a little bit of a magic over here so observe carefully what I'm going to do first of all before going to start I will insert this whole call sorry I'll hide a whole column here I will show you how to do this one okay you have to insert a space between two words and the delimiter we have to think the delimiter will be the second capital letters this one I'm here K and here G and here B okay so here I will show you one amazing trick first of all what you'll do you have to extract each and every letter in a value separately you have to you have to extract each and every character amen well for myself see how well look middle of this row of indirect and there are so many people are trying in VBA some other ways but I will do only with Excel 2010 functions okay now number of characters one F nine three each and every character is separated each and every character is separated now we are interested the position of this how you find the position of this see how I'll do first of all what I'll do I will use the upper function to convert each and everything into an upper case uppercase so what it will happen F9 it will convert everything into a uppercase that is chapter letter so what I'll do here I will use the exact function to find Ctrl V the previous one which I copied just a paste over here the two texts I'll press enter and I will show you what it is actually a so if I use exact here exact so it will give you defaults text 1 and text you which will give you the force because here is the calculator and here is the small letter if I convert this into a capital letter then it will give you the true so this is the this formula this I will I'm using here so that now I highlight everything and I press okay what I'll do first of all I will use text 1 F9 and here also the second one is X2 is F9 see it is matching m m r r is not matching so it will give you the false e and e is matching so it will give you the two only two truths will be will be shown here one and two now we want this true not that true okay the second two so what I'll do here simple way that I will use a large okay not large function I'll just small small if okay if anything is true okay we are getting two true okay logical says so we are uh asking for a small function to extract second true okay value of 2 first of all value is true this is The Logical test okay two two false false and here we have to find the truth value of true if you find any two what will you do in previous videos foreign so here I'll copy from this the row function control zinc Ctrl C TRL D if value is true okay and the K the video smallest do you want to extract if I give one it will give you the first smallest the position that is one if I give you two it will extract the second position here F9 C okay I'm giving the 2 over here the K value in a small function in a small function I am giving 2 as a k value to express the second smallest all right till now I think you understood now what I'll do here just I'll do enter here Ctrl shift enter it's an array function now what I will do I will use the replace function see how you will do replace of the old text will be this and starting numbers this and number of characters see if I enter 1 and I insert a space what will happen see F9 12 from third position it will remove X and it will replace its uh e with this space here he is there so it will insert it will remove ease that is third position that is this position and it will replace with space so I don't I want you but I want to insert a space before the second capital letters okay so here is the logic so what you'll do is how many number of characters do you need to replace so what I'll do I will use zero zero in a replace function is that to insert a special characters in a value one means it will remove the uh how many numbers of characters you want to remove it will remove suppose if I give two it will remove two characters from the value if I give at 3 it will remove three characters and it will give you the space but what I'll do instead of three I will use 0. okay 0 is the inserting a character it will not remove any character but it will insert a new character what is the new character that is the space character it's not an array okay it's not an array because what will happen what happened okay I have not loved this one F4 enter why it's not array because I had not done anything sorry somewhere went wrong [Music] okay here is your third character so I'll drag this formula over here and I will not lock this one of course F4 let's see okay right and what I'll do I will copy this whole formula from here instead of using that formula that uh difference just a paste this whole formula and I will use control shift and enter [Music] right [Music] and that's it and if you have any doubts let me know in the comment box and uh let's try with if it is three characters like I have to but I want to space after by also so I want space after uh before the third character of B so you try guys this one okay and if you want any doubts please let me know in the comment box and please let me know each and everything whatever whatever doubt you have thank you guys thanks for your support