Excel Magic Trick 562: Extract Names - Some Names In List Have Middle Name Some Do Not
HTML-код
- Опубликовано: 6 сен 2024
- Download Files:
people.highlin...
See how to extract first, middle and last names when the list contains names where some have a middle name and some do not. See the functions LEFT, SEARCH, LEN, SUBSTITUTE, LEN, MID, and IF functions.
Text Functions
Thanks for letting me know about the Split function! I am no good with VBA and so that is why I did not know about it.
Cool - I am glad that you like it!
LEN is RAD!!!
Great trick! Love the creativity behind this one. It really solidifies the power of LEN.
Very nice!!!
@ExcelIsFun Split is the VBA function that lets you split out a string into an array based on a delimeter, in this case the " ".
@ExcelIsFun i'm a VB programmer by heart, which is why a lot of my solutions involve VBA.
@ExcelIsFun Basically it's a VBA subroutine that creates an array out of a string based on a delimeter:
arName = Split(Cell.text," ")
Now, you can get the first name by arName(lbound(arName)), the last name by arName(ubound(arName)) and the middle by looping through the array and taking any index that isn't equal to the lbound or the ubound and taking the contents as the middle name.
You are welcome!
What do you mean multiple? Did you try?
Hi, what if you have a two strings middlename?
thank you very much, finnaly I got right video for middle name.
What is split? If you know a cool trick - I'd love to know!
Great sir
Glad it is great for you, Khemraj!!!
Thank you so much. This video is very helpful as always. Regards.
Thanks Sir
All your videos are really very helpful. thank you very much for posting them. I was wondering if you have uploaded a video that shows how to extract characters from left, middle and right when there is no space or symbols in between? If yes, please let me know the magic tick (creative name though) number. Thanks a ton!
@pueblonative I have an example of it on my web site, but youtube doesn't seem to like links.
Brilliant method! Thanks for sharing!!
This may seem self explanatory or tedious but how do we modify the middle name formula to account for if someone has multiple middle names because this happens occasionally? For example if someone has the name Brendan Michael Sean Patterson, their first name is Brendan, the middle name column would say Michael Sean, and their last name is Patterson. Also, how do we modify the last name column to determine if a person has a suffix. For example if someone's name is Patrick Jones III the first name is Patrick, the middle name field is blank and their last name is Jones III.
The formula for the middle name that you have in the video works for what I am looking for. I still need to determine how to account for suffixes last name though. How do I account for when someone has a name like Charlie St. Cloud. The first name is Charlie, he has no middle name, and his last name is St. Cloud. The way the last name formula will work now, it will say his last name is Cloud and so by virtue of the way that the middle name formula works, his middle name is St. which is not correct.
How do i separate last & first names separated by comma w/o using text to columns? Say: Smith, John Robert? into Smith and John Robert only into 2 columns? thank you
Thanks Mike, as always....Do you have an alternative to Fuzzy Vlookup?
How do I extract FirstName, OtherName and LastName when its like this:
LastName, FirstName, OtherName?
Wouldn't split be a little easier?
@ExcelIsFun
Thanks for providing the link I tried opening it by changing the . but an error message appeared. Could you please provide another link? or just tell me the magic trick number? Thanks!
I have been stuck for weeks, I've been importing data from a website but that website changed it's text. How do I get just the first and last name from this data of basketball players
J. Harden James Harden SG
GS @ Hou Tues 8:00pm
For whatever reason they've added the first initial and last name in front of the first and last name. If you could help that would be amazing. I've been messing with it everyday for a few weeks now but still can't figure it out. I have very little experience in spreadsheets
Last name and first name working fine but unable to extract in 2003. Could you send me the formula. Regards
How to extract First Last from 0 - First Last ? Thanks in advance
What if first names and last names are mixed up?
What does the patter of your names look like. Give me a few examples of what you have and what you want.
I have a list from the bank. Over 45,000 people. I need to arrange columns with first names, second names and last names, sometimes there are two last names. It looks like this: John Cash, Phillips Ed, Mike Ted Anders, Sara Anna Dumont-Spacey, Johanson Laura, Marin L. Moon, etc. Big differences in the record. Doing this by hand is a nightmare. Thanks for help, Christina.
Need help
confusing, this is why databases and SQl is used :)
Try this playlist of videos:
youtube[dot]com/user/ExcelIsFun#grid/user/A8E169D01380A4F7
Maybe for last:
=LEFT(TRIM(A6),SEARCH(" ",TRIM(A6))-1)
first:
=TRIM(LEFT(SUBSTITUTE(A6,B6,""),SEARCH(",",SUBSTITUTE(A6,B6,""))-1))
I would post an example and your question to:
mrexcel[dot]com/forum
It will be easier to get you a solution if you post an example and explain if there are any extra spaces etc. In essense this site is good for back and forth dialog to get you a solution.
I mean middle name extraction in 2003. Regards
I am not good with VBA. The best VAB videos around are from Mr Excel. His video set costs about $49:
mrexcel[dot]com
Two variations with SUBSTITUTE():
TRIM(SUBSTITUTE(SUBSTITUTE(A6,B6&" ",""),D6,""))
TRIM(SUBSTITUTE(SUBSTITUTE(A6,B6,"",1)," "&D6,""))
what