Here's the deal - I have a column of names. Some cells have one name only (ie, John Doe), whereas other cells have two names separated using Alt+Enter. There are a couple thousand rows in this column, and I was wondering what the best way would be to sort this column. I tried Googling this, but not luck. TIA.
Concatenate the separated names? I don't understand what you mean by they're separated with alt+enter.
There are two names in one cell, such as: John Doe Robert Jones Both of those names are in the same cell.
So there are some cells that have one name, ex. John Doe, but there some cells that have two names, ex. Jack Doe Jane Doe. Is that correct?
Yep. But instead of being typed out in a cell separated by a space (ie, Jack Doe Jane Doe), I used Alt+Enter so one name is directly under the other. ie, Jack Doe Jane Doe
Probably should elaborate... Select that column and then go to Data-->etc... Also when you checkmark space, make sure you uncheck everything else
Do you have the same sets of names coming up in these cells? Like, do you have John Doe/Robert Doe 27 times, or is each and every cell different? If you have the same sets coming up, you could auto filter for those instances, and manually separate them on one row, copy, paste, and replace for the rest of the instances, and then move on to the next filtering set. It might take a while longer than you are looking for, but it's better than going one by one...
That worked? Weird. I would think what would happen is what happened to Jayou and you'd lose data. Or at least that's what happened to me.
Whoops, you're right..it seemed like it worked at first because of the massive amounts of data..but it did cut most of the data off. Any other suggestions?
Hey what version of Excel are you working with, Balla? I've got 2007 and Tree's trick with the text to columns made the second name disappear completely. I'm assuming you want to separate the 2nd name into a new cell.
Use the SUBSTITUTE function to replace all instances of CHAR(10) in the column you want to split with some other character like a comma or a pipe character. Then do what TreeRollins did using the character you used to perform the split. *EDIT* : Had a typo in there... should be CHAR(10), not CHR(10).
Highlight your data and click Format->Cells. Select the Alignment tab and uncheck Wrap Text. You technically don't need to do this, but it makes the data look cleaner. Now you need to figure out if you have a line break in your data or not. In an adjacent column paste in the following formula: =IF(ISERROR(FIND(CHAR(10),A1)),A1,TRIM(MID(A1,1,FIND(CHAR(10),A1)-1) & " "&MID(A1,FIND(CHAR(10),A1)+1,500 ))) That takes a name in the cell A1 and spits it out in clean form, regardless of whether there is a line break or not.
