SPLIT NAMES in Excel | Separate First, Middle and Last Name
HTML-код
- Опубликовано: 23 июл 2024
- In this video, learn how to use different methods to Split names in Excel (i.e., separate first, middle and last name in Excel).
There are different ways you can manipulate text data in Excel. And one of the common things people do in Excel is to manage the names datasets.
Here are the methods covered in this video to split names into first and last names:
-- Text to Columns
-- Find and Replace
-- Flash Fill
-- Formulas
All these methods work great and are quick and easy.
With the first three methods, the result you get is static. This means that if your names data changes, then the resulting data wouldn't change.
The last method in this video shows how to split names using formulas. With formulas, you get a result that is dynamic. This means that if you change the original dataset, the resulting data automatically updates.
Text to Columns method uses a delimiter to separate the first and last name. The delimited could be a space character or any other delimiter - such as a comma.
Find and Replace method uses wildcard characters to find and replace part of the name. For example, if you want to remove the last name and only keep the first name, you can do that with Find and Replace.
Flash Fill is one of the easiest methods to split names in Excel. All you have to do si type in a few cells and it automatically identifies a pattern and gives you the same result for all the cells. Note that Flash Fill is available only in Excel 2013 and later versions.
You can read about this tutorial here (it has all the formulas covered in this video): trumpexcel.com/separate-first...
Free Excel Course - trumpexcel.com/learn-excel/
Paid Online Training - trumpexcel.com/excel-training/
Best Excel Books: trumpexcel.com/best-excel-books/
⚙️ Gear I Recommend:
Camera - amzn.to/3bmHko7
Screen Recorder - techsmith.z6rjha.net/26D9Q
USB Mic - amzn.to/2uzhVHd
Wireless Mic: amzn.to/3blQ8uk
Lighting - amzn.to/2uxOxRv
Subscribe to get awesome Excel Tips every week: ruclips.net/user/trumpexc...
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial
This is soooo easy and simple. Thank you immensely. It was just what I was looking for!
Straight to the point and useful! Thanks
OMG!!! I had NO IDEA about the wild card use in Find and Replace! This is AWESOME! You ROCK sir!
Super thanks! You made it so simple, that I was able to get it in the first try itself.
finally someone from the expected quarter who actually could make me understand how this works ... awesome and thanksss...
Thank you so much, your trick helped me to sort data few seconds.
There is no star to give. You deserve a minimum of 5 stars. Thanks.
Thanks so much! Such a time saver
Your videos are awesome nice, simple easy to follow without the formulas. Thank you Thank you and Thank you
The Best Video on Split Names. Thanks you Sir.
Thank you so much! This video was so helpful!
That was amazing. Thank you!
was working and needed a quick solution. Very good explanation.
Fantastic. Loved the easy-ness. Keep it up! Great Job!
Thanks Gaurav... Glad you liked the video :)
Thank you so much Sumit Sir,really appreciate your effort and helping us learn Excel through your videos.
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
The best video so far about this topic. Amazing everything very clear. Thank you so much for this you are a star sir!
Informative video. Thank you.
dude you're doing a nice video of using excel
thank you, sir. Very helpful
Amazing video. Thank you so much Sumit!
Awesome Sumit! Thanks for sharing :)) Thumbs up!!
Thanks so much
That was really helpful thanks a lot
Great effort!!!
THANK YOU SO MUCH VERY USEFULL
thank you ...for ur help
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!!
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
Great video
Really the best video....
Superb Bro..
great my teacher
Dear Sumit,
You are awesome...
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.
Splendid 🤩
This is the best video! Super ez
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.
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
Nice sir
I'm unable to find the estricks in key board how it look like and where it will be keyboard please guide
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!
Hi Sumit, Can you please share the practice file for this video?
How do you split with multiple first names (e.g. John Michael P. Jones).
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
great
❤❤❤
nice
Can split long names (more than 8 names )
Sir can you please start VBA programming
Sir replace Wich simple writing plz ans and 2007 it is working plz ans
2007 excel
If you hear an Indian guy talking about Excel, you know you're in the right space.
noooo its doesnt work anymore wthhh