Hello, I work for American Airlines as an Executive Assistant and I use Excel quite frequently however I'm a novice. Your video was so easy to understand and I've watched a million videos to help me through certain processes! Yours was the best I've seen thus far. Great Job and I really appreciate you - I'll be sharing your details with all my Admins as well!
Thank you - this is by far the easiest tutorial I have found. I rarely have to do it and have to search to figure out the answer - thanks for making my job easier today!
You're welcome! Be sure to check out Solution #2, which uses Flash Fill. It's really a better solution. Flash Fill is like magic! Here's the link: ua-cam.com/video/rrggzpaUb78/v-deo.html
Yay! This was easy to follow and helped me with a huge list of names! But some people have 2, 3 or 4 middle names between first and last (all in the same cell). What do I do about that?! Thanks a million.
Hi Sarah. I have a video that describes using flash fill. That might help you in this situation. Watch the entire video, but the part that might help you most starts around 4:45. Here's the video: ua-cam.com/video/rrggzpaUb78/v-deo.html
OH MY GOSH!!! This is something that has frustrated me for YEARS!!! So many different Divisions inside our City Government list their employees in Excel as First Name, Last Name, others just first name or just last name, when all I really want is LAST NAME, FIRST NAME. Where have you been all my life? I "used to" consider myself very experienced in Excel. Well, today you proved me wrong. Thanks again!!!
You're welcome. Consider checking out a second (and better) solution that I posted for converting first-last to last-first. You'll find it here. ua-cam.com/video/rrggzpaUb78/v-deo.html
Hello Frank. Your video was very helpful in finding a solution to name sorting. It did not work on the final step for me and I wonder if it is because I exported a csv file, not excel file. I need a csv file format because it needs to be uploaded to an accounting software program. What would be your suggestions in this case? I was able to split the first name and the last name into 2 columns, but then to use the contat function it didn't seem to work. Thank you.
Hi Debbie. First, be sure you're using Excel (as opposed to google sheets, etc.) and be sure you're spelling the function name correctly (concat instead of contat). We know the Excel concat function works (I prove that in the video), so check for simple errors first. If you're able to get it to work when within an Excel file, but you find the CSV file does not contain the correct values, then before saving to CSV, copy the final column of names into a new column, but use "paste special" to paste the values only. Then you're guaranteed that the values will be in the CSV file (because they will no longer be based on an Excel formula). Finally, consider checking out my other video, where I offer a solution to the "last name first name" problem using flash fill, which is awesome, ua-cam.com/video/rrggzpaUb78/v-deo.html
Great video! How would I use the =CONCAT to automatically add once a new name is added on a new row. I tried to use the drag box down extra blank rows but it fills in a comma and space. Also I want to use that information to populate a drop down box in a form. Do you know how I'd be able to do that without those commas appearing.
Good questions, although unfortunately I don't have time right now to look into the answers because my classes are starting soon and I need to focus on them. That said, I strongly recommend joining a few facebook groups that focus on Excel. Many of those members are Excel gurus, and they are eager to find solutions to people's problems. Good luck!
can you make this a running formula? I regularly add names to a list. It would be beneficial to have this automatically happen without having to go back to the data/ column text to run this every time... I just want it to automatically take the name John, Doe and make is Doe, John everytime I add a new name. It seems that i can currently do that... The =concate() formula pulls from the (firstname), (lastname) columns, which derive their values from manually running the DATA>text to column process you just walked us through.. This is the process im trying to Automate...
Yeah, that's a good idea! You can use the autofill handlebar to copy that same concat function down as far as you'd like. One problem you'll run into is seeing the comma will appear in the cells corresponding to the rows that have no names entered yet. You can use conditional formatting on those cells to get rid of the commas until the names are entered, but that's a whole other topic altogether. Good luck!
@@FrankMLoSchiavo Actually I have one more problem I have a list of names next to it I have a date of birth and next to that I have the location of a facility they need to go to for medical... My initial problem whoz that the names needed to be last name 1st with a, in between like you addressed.. The problem is I had started by hand with about 2:00 of the 6:00 rose so some of them already were done... And they are intermingled... And they are intermingled Is there a way to do it still so it only picks the ones that are not finished yet?
I have multiple names in a single cell (First Last, First Last, First Last). I need to change the cell to (Last, First; Last, First; Last, First). Do you have video on this or a suggestion?
I have a video that shows how to use flash fill as a second solution to this type of problem. Take a look at the video and see if flash fill might work in your situation. Here's the link: ua-cam.com/video/rrggzpaUb78/v-deo.html
Below is the function/formula I used in the video. It can be written slightly more efficiently with a few tweaks, but in the video I wanted to take things one step at a time for clarity. =CONCAT(C2,","," ",B2)
Hi. If you're trying to remove extra spaces, consider using the TRIM function. If you're trying to change the case of the text, try using the UPPER, LOWER, or PROPER functions. You also might want to ask Google or ChatGPT and see if they can provide other suggestions. Good luck :)
I'm not sure of an easy way to reuse the text-to-columns procedure without going through those steps again. HOWEVER, I recommend that you check out my video that shows a second solution to this issue using FLASH FILL, ua-cam.com/video/rrggzpaUb78/v-deo.html
I'm sure there are many complicated ways to handle it with one Excel formula, but off the top of my head, I'd probably create one formula based on 1 first name, another based on 2 first names, etc., and then sort and apply the appropriate formula to each name. Once you have formulas written for 2 first names, etc., you could easily nest the formulas into one formula using the IF command. So, IF only 1 first name, apply the first formula, IF 2 first names, apply the second formula, etc. I hope this helps. If you develop a better solution, let me know.
You're welcome! By the way, I have a video that presents a second (easier) solution to that same problem. If interested, take a look, ua-cam.com/video/rrggzpaUb78/v-deo.html
I'd recommend using Flash Fill. Check out my follow-up video that shows how to convert last/first names using that strategy. FLASH FILL is like magic! You'll find that video here: ua-cam.com/video/rrggzpaUb78/v-deo.html
I posted a similar video with a second solution that uses flash fill. I recommend trying that. Here's the video: ua-cam.com/video/rrggzpaUb78/v-deo.html
By the way, if you liked that video, check out this one...ua-cam.com/video/rrggzpaUb78/v-deo.html It discusses FLASH FILL, which can accomplish the same name conversion much more easily.
Hello, I work for American Airlines as an Executive Assistant and I use Excel quite frequently however I'm a novice. Your video was so easy to understand and I've watched a million videos to help me through certain processes! Yours was the best I've seen thus far. Great Job and I really appreciate you - I'll be sharing your details with all my Admins as well!
I'm so glad the video helped you. Thanks for posting such supportive comments. I appreciate it!
Thank you - this is by far the easiest tutorial I have found. I rarely have to do it and have to search to figure out the answer - thanks for making my job easier today!
I'm so glad it helped! Thanks for letting me know :)
Thank you! This was very helpful. I don't normally need to use Excel for work so you just saved me about an hour of time!!!
Glad to help :)
Thank you so much. I read and tried everyone elses suggestions and they didn't work. Your video worked. Thank you so much. Im saving your video.
I'm glad the video helped you :)
This video has been so helpful to me. Thank you!
You're welcome! Be sure to check out Solution #2, which uses Flash Fill. It's really a better solution. Flash Fill is like magic! Here's the link: ua-cam.com/video/rrggzpaUb78/v-deo.html
This was so helpful - thank you! Nice and easy to follow along to.
Happy to help!
Thank you for such a clear explanation! Much appreciated!
My pleasure. Thx for commenting!
I got it, yay. You are a good teacher. God bless you, 🙏🏼.
I'm glad to help! Thx :)
Great tip! And very well presented. Exactly what I needed to know. Thank you so much!
You're welcome! Glad it helped :)
Just saved from having to redo an entire spread sheet! Thank you!!!
I'm so glad the video helped you! Thx for letting me know :)
This was great and very helpful, clear and easy to understand.
I'm glad the video was helpful! Thanks for taking the time to let me know :)
That was exactly what I needed. Thanks
My pleasure.
Thank you Frank. That's what I needed to know.
Happy to help!
You made so easy to understand 👍
Thanks for letting me know that. I appreciate it! If you can, share the video with others :)
Extremely helpful, thank you!
Glad the video helped! Thx for posting :)
Thank you thank you ! I have no idea what I’m doing at my new job but I’m learning, from you ! Much appreciated
I'm glad the video helped you. Good luck with your new job!
Yay! This was easy to follow and helped me with a huge list of names! But some people have 2, 3 or 4 middle names between first and last (all in the same cell). What do I do about that?! Thanks a million.
Hi Sarah. I have a video that describes using flash fill. That might help you in this situation. Watch the entire video, but the part that might help you most starts around 4:45. Here's the video: ua-cam.com/video/rrggzpaUb78/v-deo.html
OH MY GOSH!!! This is something that has frustrated me for YEARS!!! So many different Divisions inside our City Government list their employees in Excel as First Name, Last Name, others just first name or just last name, when all I really want is LAST NAME, FIRST NAME. Where have you been all my life? I "used to" consider myself very experienced in Excel. Well, today you proved me wrong. Thanks again!!!
I'm so glad the video helped you! Please spread the love by sharing the video with everyone you know, lol!
Very easy to understand thanks
You're welcome :)
soooo helpful Thank you!
You are soooo welcome, lol!
Many thanks for this information bro
My pleasure. Thx for posting :)
Brilliant! Just what I needed. Thanks.
You're welcome!
Thank you so much!!!
You're welcome. Consider checking out a second (and better) solution that I posted for converting first-last to last-first. You'll find it here. ua-cam.com/video/rrggzpaUb78/v-deo.html
Super helpful! Thank you so much for this. 👍
My pleasure. Glad it helped you.
OMG thx a lot!
My pleasure!
Just a simple thanks
You're welcome!
Many thanks!
You're welcome!
Hello Frank. Your video was very helpful in finding a solution to name sorting. It did not work on the final step for me and I wonder if it is because I exported a csv file, not excel file. I need a csv file format because it needs to be uploaded to an accounting software program. What would be your suggestions in this case? I was able to split the first name and the last name into 2 columns, but then to use the contat function it didn't seem to work. Thank you.
Hi Debbie. First, be sure you're using Excel (as opposed to google sheets, etc.) and be sure you're spelling the function name correctly (concat instead of contat). We know the Excel concat function works (I prove that in the video), so check for simple errors first.
If you're able to get it to work when within an Excel file, but you find the CSV file does not contain the correct values, then before saving to CSV, copy the final column of names into a new column, but use "paste special" to paste the values only. Then you're guaranteed that the values will be in the CSV file (because they will no longer be based on an Excel formula).
Finally, consider checking out my other video, where I offer a solution to the "last name first name" problem using flash fill, which is awesome, ua-cam.com/video/rrggzpaUb78/v-deo.html
Thank you so much 😊
My pleasure, my friend. Thx for leaving a comment :)
Thank you ❤️❤️❤️
You're welcome.
Wow, thanks so much!!!
Thanks! By the way, I have a video that shows a simpler solution, using Flash Fill. Check it out: ua-cam.com/video/rrggzpaUb78/v-deo.html
Great video! How would I use the =CONCAT to automatically add once a new name is added on a new row. I tried to use the drag box down extra blank rows but it fills in a comma and space.
Also I want to use that information to populate a drop down box in a form. Do you know how I'd be able to do that without those commas appearing.
Good questions, although unfortunately I don't have time right now to look into the answers because my classes are starting soon and I need to focus on them. That said, I strongly recommend joining a few facebook groups that focus on Excel. Many of those members are Excel gurus, and they are eager to find solutions to people's problems. Good luck!
can you make this a running formula? I regularly add names to a list. It would be beneficial to have this automatically happen without having to go back to the data/ column text to run this every time... I just want it to automatically take the name John, Doe and make is Doe, John everytime I add a new name.
It seems that i can currently do that...
The =concate() formula pulls from the (firstname), (lastname) columns, which derive their values from manually running the DATA>text to column process you just walked us through..
This is the process im trying to Automate...
Yeah, that's a good idea! You can use the autofill handlebar to copy that same concat function down as far as you'd like. One problem you'll run into is seeing the comma will appear in the cells corresponding to the rows that have no names entered yet. You can use conditional formatting on those cells to get rid of the commas until the names are entered, but that's a whole other topic altogether. Good luck!
Omg you just saved my life!😁
My new video addresses that same problem but fixes it ever faster! Check it out: ua-cam.com/video/rrggzpaUb78/v-deo.html
@@FrankMLoSchiavo holy crap, i love it! It works 😃 I feel like I just won the lottery lol
Thank you!!
@@FrankMLoSchiavo Actually I have one more problem I have a list of names next to it I have a date of birth and next to that I have the location of a facility they need to go to for medical... My initial problem whoz that the names needed to be last name 1st with a, in between like you addressed.. The problem is I had started by hand with about 2:00 of the 6:00 rose so some of them already were done... And they are intermingled... And they are intermingled Is there a way to do it still so it only picks the ones that are not finished yet?
Sorry, typos, talk texting. *200 and *600
Thanks ☺️
You're welcome.
I have multiple names in a single cell (First Last, First Last, First Last). I need to change the cell to (Last, First; Last, First; Last, First). Do you have video on this or a suggestion?
I have a video that shows how to use flash fill as a second solution to this type of problem. Take a look at the video and see if flash fill might work in your situation. Here's the link: ua-cam.com/video/rrggzpaUb78/v-deo.html
Saving my job lol
Can you help with MI between First and Last name?
Try using flash fill instead. I have a video about it: ua-cam.com/video/rrggzpaUb78/v-deo.html
@@FrankMLoSchiavo Thanks!
@@MyLife3636 You're welcome :)
I’m just curious how many “,” do we need to put before we put the fist name? Does that depend on the letters of the first name
Below is the function/formula I used in the video. It can be written slightly more efficiently with a few tweaks, but in the video I wanted to take things one step at a time for clarity.
=CONCAT(C2,","," ",B2)
It can also work for windows10? Tia,
Sir I have some data in one cell like Kumar ,vijay,Singh how we write data in correct form in one cell
Hi. If you're trying to remove extra spaces, consider using the TRIM function. If you're trying to change the case of the text, try using the UPPER, LOWER, or PROPER functions. You also might want to ask Google or ChatGPT and see if they can provide other suggestions. Good luck :)
Thank you.
My pleasure. Please share the video with others :)
Thank you 😊
My pleasure. Be sure to watch my video with another solution to that same problem. It uses flash fill, which is really easy.
If I need to repeat this every month, with a new list of names, is there a way to do this without going through all the steps again?
I'm not sure of an easy way to reuse the text-to-columns procedure without going through those steps again. HOWEVER, I recommend that you check out my video that shows a second solution to this issue using FLASH FILL, ua-cam.com/video/rrggzpaUb78/v-deo.html
Yes, look up MACROs on excel
@@robpgh159 That's a good idea. In fact, I have a video that shows how to record, save, and run a macro, ua-cam.com/video/sIi0wn9LFB4/v-deo.html
hi, how could it work if there are 2 or more first names? please help...
I'm sure there are many complicated ways to handle it with one Excel formula, but off the top of my head, I'd probably create one formula based on 1 first name, another based on 2 first names, etc., and then sort and apply the appropriate formula to each name. Once you have formulas written for 2 first names, etc., you could easily nest the formulas into one formula using the IF command. So, IF only 1 first name, apply the first formula, IF 2 first names, apply the second formula, etc. I hope this helps. If you develop a better solution, let me know.
thank you!!!!!!!!!!
You're welcome! By the way, I have a video that presents a second (easier) solution to that same problem. If interested, take a look, ua-cam.com/video/rrggzpaUb78/v-deo.html
Thank you
You're welcome :)
How about with middle name?
I'd recommend using Flash Fill. Check out my follow-up video that shows how to convert last/first names using that strategy. FLASH FILL is like magic! You'll find that video here: ua-cam.com/video/rrggzpaUb78/v-deo.html
It's a cool video... Brilliant
Thanks!
How to not seperate first name who have two names when seperating first name, middle name, and last name?
I posted a similar video with a second solution that uses flash fill. I recommend trying that. Here's the video: ua-cam.com/video/rrggzpaUb78/v-deo.html
sangat membantu, terima kasih
You're welcome!
Frank, you just saved me a lot of aggravation, a lot.
And it worked. Thank you!!!!
@@John_GGG You're welcome! Thx for letting me know :)
Great walk through. Thank you!
You're welcome!
By the way, if you liked that video, check out this one...ua-cam.com/video/rrggzpaUb78/v-deo.html It discusses FLASH FILL, which can accomplish the same name conversion much more easily.