Thank you very much for this video!! I have a question please, how can I separate the names if they have a minus symbol (-) in between? Example: John-Smith Thank you so much!!
I found Flash Fill or Ctrl+E useful to extract the first and middle names from a list of names where there was surname followed by a comma then a first name and . I then used IFERROR(RIGHT([@First],LEN([@First])-SEARCH(" ",[@First])),"") to separate the middle names from the list, avoiding an error where there was no middle name (where [@First] is my list of first names with some middle names in my table). Probably not 100% fool proof but in my situation I can always copy and replace with values only to get rid of the formula. Similarly some surnames easily extracted with Flash Fill may be difficult with spaces and hyphens and require a manual fix. I found if Flash Fill has too many errors if you correct them Flash Fill will repair similar errors in the list. Temporarily inserting a column usually stops Flash Fill from making corrections you do not want.
I use google sheets now as it has a regular expression function built-in. I wrote a regex fn in vba for excel but it's nice for this to work online using google sheets. Perhaps a good video would be to compare the two. Excel vs. Google Sheets
If some of the names have a middle name and some don't have a middle name and those without a middle name have to be replaced with a ( space ). How to do this? Thank you for the clear explanation
This is easy when you have a pretty data set like that. Do the same with names entered in different formats mixed like (firstname lastname) or (lastname, firstname) or (lastname firstname) and THEN you can talk all you want.
What if the name doesn't have a middle initial and you have data that contains initials and those without your Last name would appear in your middle name.
Hello! How to extract a text string consisting of four spaces or more for that matter using a dynamic formula For example : File Another Dir Sample Now here, i want "Another Dir" in one column I have understood how to extract the first and the last part of the string using the left and right function. what i want to know is how to extract this result using the dynamics of the mid function please help me on this!
Boss awesome 🙏🙏 respect, it is always difficult to find the value for the 2 space, but you made it simpler
There is no star to give. You deserve a minimum of 5 stars. Thanks.
The Best Video on Split Names. Thanks you Sir.
finally someone from the expected quarter who actually could make me understand how this works ... awesome and thanksss...
thank you, it made my life easier
Fantastic. Loved the easy-ness. Keep it up! Great Job!
Thanks Gaurav... Glad you liked the video :)
OMG!!! I had NO IDEA about the wild card use in Find and Replace! This is AWESOME! You ROCK sir!
was working and needed a quick solution. Very good explanation.
Super thanks! You made it so simple, that I was able to get it in the first try itself.
Thank you so much Sumit Sir,really appreciate your effort and helping us learn Excel through your videos.
The best video so far about this topic. Amazing everything very clear. Thank you so much for this you are a star sir!
Amazing video. Thank you so much Sumit!
Your videos are awesome nice, simple easy to follow without the formulas. Thank you Thank you and Thank you
Awesome Sumit! Thanks for sharing :)) Thumbs up!!
dude you're doing a nice video of using excel
Thank you so much! This video was so helpful!
Thank you so much, your trick helped me to sort data few seconds.
That was really helpful thanks a lot
Thank you very much for this video!! I have a question please, how can I separate the names if they have a minus symbol (-) in between? Example: John-Smith
Thank you so much!!
Really the best video....
Thanks so much! Such a time saver
Sumit Sir,
I am a great fan of your channel, your tips and tricks are one of the most relevant and unique. ; )
Thanks Puneet! Glad you find the tutorials useful
This is soooo easy and simple. Thank you immensely. It was just what I was looking for!
Straight to the point and useful! Thanks
thank you, sir. Very helpful
Great effort!!!
Superb Bro..
That was amazing. Thank you!
Great video
THANK YOU SO MUCH VERY USEFULL
I found Flash Fill or Ctrl+E useful to extract the first and middle names from a list of names where there was surname followed by a comma then a first name and . I then used IFERROR(RIGHT([@First],LEN([@First])-SEARCH(" ",[@First])),"") to separate the middle names from the list, avoiding an error where there was no middle name (where [@First] is my list of first names with some middle names in my table). Probably not 100% fool proof but in my situation I can always copy and replace with values only to get rid of the formula. Similarly some surnames easily extracted with Flash Fill may be difficult with spaces and hyphens and require a manual fix. I found if Flash Fill has too many errors if you correct them Flash Fill will repair similar errors in the list. Temporarily inserting a column usually stops Flash Fill from making corrections you do not want.
thank you ...for ur help
Informative video. Thank you.
Thanks so much
This is the best video! Super ez
Splendid 🤩
Dear Sumit,
You are awesome...
Nice sir
I use google sheets now as it has a regular expression function built-in. I wrote a regex fn in vba for excel but it's nice for this to work online using google sheets.
Perhaps a good video would be to compare the two. Excel vs. Google Sheets
If some of the names have a middle name and some don't have a middle name and those without a middle name have to be replaced with a ( space ). How to do this?
Thank you for the clear explanation
How do you split with multiple first names (e.g. John Michael P. Jones).
This is easy when you have a pretty data set like that. Do the same with names entered in different formats mixed like (firstname lastname) or (lastname, firstname) or (lastname firstname) and THEN you can talk all you want.
What if the name doesn't have a middle initial and you have data that contains initials and those without your Last name would appear in your middle name.
same problem here
Hello!
How to extract a text string consisting of four spaces or more for that matter using a dynamic formula
For example : File Another Dir Sample
Now here, i want "Another Dir" in one column
I have understood how to extract the first and the last part of the string using the left and right function. what i want to know is how to extract this result using the dynamics of the mid function
please help me on this!
great my teacher
Can split long names (more than 8 names )
Hi Sumit, Can you please share the practice file for this video?
I'm unable to find the estricks in key board how it look like and where it will be keyboard please guide
nice
Sir can you please start VBA programming
great
Sir replace Wich simple writing plz ans and 2007 it is working plz ans
2007 excel
This is the second bloody video I have come across where the screen blurs and I can't see or view your video..
If you hear an Indian guy talking about Excel, you know you're in the right space.
❤❤❤
noooo its doesnt work anymore wthhh