I have a column of names, each with first and last. Instead of editing each cell to manually add a last name column (hundreds of names) beside the first, is there a way to sort using the second word of the column?
Name John Thompson
Is there any way to sort for the first character following a space, or something like that? Just hoping there's some way I can do this without grunting through each cell for almost a thousandThanks for any help!!
Do you not want the two names separated or do you believe it would be too much work to separate them?
If you are able to have another column try this. If your names are in column A, place this code in B1 and drag down to all of the cells you need it in. This will give you the last name in each cell. You can hide this column so it is not showing and when you go to sort, simply choose "sort by column B".
EDIT: Corrected my formula.
=IFERROR(LEFT(A1,FIND(" ",A1)-1),"")
Last edited by Teblol; 02-05-2016 at 03:38 PM.
I would love to separate the two names, if some automated mechanism makes that possible. Really don't want to spend hours doing it manually.
Thank you for that formula, but it gives the first nameI tried changing the "LEFT" to "RIGHT" and tinkering with the "-1" but each cell is clipping the name differently
Wow, I even went back to correct it and I still messed it up. If you want to place the first name into its own column and last name into its own column, you can use the original formula I gave you in column B and the column below in column C.
EDIT: Or you can just use DATA > Text to columns > Make sure delimited is selected and click "Next" > Make sure Space is selected and choose "Finish".=IFERROR(MID(A1,FIND(" ",A1),LEN(A1))," ")
This will move the last names to the next column.
Last edited by Teblol; 02-05-2016 at 04:02 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks