Sorting By Last Name in Mac Numbers

Hi this is Gary with MacMost.com. Today let me show you how to sort a list of names by last name even if the names aren't formatted that way.

MacMost is brought to you thanks to a great group of more than 500 supporters. Go to MacMost.com/patreon. There you could read more about it, join us, and get exclusive content.

So it turns out if you have a list of names and then names aren't formatted last name comma first name like these names aren't then trying to sort them by last names can be really tough. But you can do it. You just have to use a lot of different functions and formulas in Numbers to get the job done.

So here's what we're dealing with. We have a list here with some names and some data. The names, unfortunately, are first name last name format and that's not good for sorting. We want to sort by last name. As a matter of fact we want to sort properly by last name comma first name and then middle name or middle initial after that. So we need to convert these names into that format. To do so takes a lot of work so we're not going to try to do it in one big formula. Instead we're going to break it up into steps using multiple extra columns. Columns that we will hide later on. Right now it will make it easier for us to see each step and make modifications along the way.

So let's start by selecting this second column here and going to Table and then Add Column Before to add an extra column there. In fact I'm going to add a whole bunch of extra ones. I can always delete them if I'm not using them. I happen to know I'm going to need a bunch of them. Now the first step is to figure out where that space is. So we can figure out the location of the space using either the Find or Search command. Find will work just fine. 

So I'm going to start here by hitting the equals key, so I'm entering in a formula, and now I'm going to use Find and the find function if we look here on the right under Text we'll see FIND and we can see that it's looking for the search-string and then the source-string. So the search-string is space. So quote space followed by quote comma and then we'll look at what's in this column here and I'll put parentheses and we see we get the number 7. That's where the space is, in the seventh position. If I were to copy that and put it here we can see it's in the fifth position for John Adams. So we'll call this one Location of Space.

Then we'll go and get the first name based on where that space is located. So we'll do this First Name column here and we can use another string function called LEFT to get the characters to the left. So LEFT and then the first parameter is source-string, this, and then up until which character. So we'll do location of the space and we see it gets us the first name. Actually it's getting us an extra character because it's including that space. George only has six characters in it but we're getting the first 7 characters. We can actually go into here and put -1 just to make sure we get only the first name and no extra space. If we copy that here and paste it there we can see we get John for John Adams.

Now to get the last name, which we'll do here, we want to use something similar but we can use RIGHT to get the RIGHT most characters. So we'll get from the Name and we'll use the location of the space. What's going to happen is we're not going to get the right thing. So it's not starting from the seventh character and then everything to the right of that. It's starting from the end and getting a number of characters from the right side. So we can do that by taking the length, which is LEN, of the entire name and then subtracting the position of the space. So in this case George Washington has seventeen characters. We're going to subtract 7 on the left giving us 10 characters. So the right most ten characters is the last name, Washington. If we were to copy that formula and paste it here we get Adams. So perfect so far.

Let's add another column here. This is going to be the one that's going to be last comma first. All we need to do with this one is do a formula and then use CONCATENATE which will allow us to bring together different strings. So we'll do Concatenate and we'll start with the last name and then comma, and then quote then the string comma space. So a comma and a space between the last name and the first name and then comma and then next thing we'll put is the first name. We'll see now we get Washington, George. Perfect. Adams, John. Perfect.

Now if we were to take all four of these formulas here and paste them for, say, Thomas Jefferson. We get Thomas the space is in the 7th character, Thomas is the first name, Jefferson is the last name, Jefferson, Thomas. It works out perfectly. It will work for anybody that has a first and the last name and nothing else. But what about John Quincy Adams. We paste it here and it becomes a mess. The 5th character is the first space, that gives us the first name John. Now the last name it thinks is Quincy Adams, everything after that first space. But Quincy is the middle name. So we end us with last, first being Quincy Adams, John. 

So we need to do something a little different when middle names are concerned. What we want is not the location of the first space but the location of the last space. It turns out this is really tricky to get. There is no way to go and say give me the location of the last space. There's no function for that. So what we need to do is somehow pick out that last space. Okay, so the first thing we need to do is figure out how many spaces are there in these names. There's no way to do that. There's no function that says give me the number of space or the number of a's or w's or anything like that. You can get the length of the string and that's it.

But you do have a function called Substitute. Substitute allows you to do some interesting things like this. You do equals and then SUBSTITUTE and you get the source-string, take the existing-string that you're looking for so a space, quote space quote, and then a replacement string and I'll do quotes with no space in it. Replace every space with nothing. Then we don't need Occurrence here. If we do this we end up with GeorgeWashington with no spaces.  If we can get GeorgeWashington with no spaces and we get George Washington with spaces, we can get the lengths of those and subtract them to get the number of spaces. 

So if I were to go and put LEN around this I would get the length of George Washington with no spaces. If I were to go and take the length of GeorgeWashington with spaces and subtract the no spaces version I get one. 1 space. Copy that and put it here for John Adams, Thomas Jefferson, one space for each of those. But for John Quincy Adams I get 2. There are two spaces. Now we can use that same substitute function to actually replace that occurrence of that space with something else. Because you notice that if I do SUBSTITUTE I have source-string, so let's put George Washington, existing-string, so space, replace that with something else. Something that would never appear in a name. I've never seen a semi-colon in a name so let's use that. Then it says occurrence. Which one? Well, let's use the results of this column here. So in this case the first space. So now we have George;Washington. If I were to copy and paste that here John;Adams, Thomas;Jefferson and if I paste it here I get John Quincy;Adams because it's replacing the second space with a semi-colon.

We can call these columns Number of Spaces and we can call this Divided Name. Now instead of Location of Space we can look for the Location of the Division and instead of looking for space we can look for semi-colon instead of in that cell we can look for this cell here. Let's go and set it so it preserves the column there just to keep it neat. So now we see the semi-colon is in position 7, it's in 5 here, 7 there. Same as the space since there is only one space. But if I paste that formula here the semi-colon is in position 12 which means the first name is now correctly put as John Quincy, technically first name plus so we'll put First Name Plus here. The last name is correctly put as Adams. So we end up with Adams, John Quincy.

Now we can take all of these. There are six total special columns here. We can copy them and we could paste them in the entire list all the way through. Now we get last comma first all the way down. It works and is correct for every single name except one because we have President Martin Van Buren. Van Buren is a rare instance of a last name that has a space in it. As you would if you had, say, a list that had Charles De Gaulle or Vincent Van Gogh or Helen Bonham Carter. These people all have last names with spaces in them. Now I could do a hyphen here. Right. I could put a hyphen between Van and Buren and now it alphabetizes correctly. But that's not Martin Van Buren's name. It has a space. Not a hyphen. 

Fortunately there is a second space character. We have the regular space character that you get when hitting the spacebar. There's a special one called Non Breaking Space. You get that by holding the Option key and typing space. This you would use if you wanted to type something, say, a word processing document and keep two words together on the same line. So it would never break them up. A non breaking space means that. Those two words are tied together. In the case of Van Buren this is a good instance where you would want a non breaking space. If you were writing a book on Martin Van Buren chances are you would never want to actually have the words Martin Van at the end of one line and Buren on the next. You would probably want to have Van Buren together all the time. So I can select the space here and hit Option Space and you can see it looks just like a normal space. A non breaking space has the same appearance.

But we're searching for instances of spaces. So now only one is found. It is found between Martion and Van. The semi-colon is placed there and the last first is now properly shown as Van Buren, Martin. So now our list is perfect. We can now sort. Now we could sort by clicking on the top of the list and sorting that way. But that's a one time thing. A better way to do it is to go to Organize, Sort, and then Add a Column. Add Last, First. This sorts by this column here. Anytime we add a name to the list we can hit the Sort Now button to re-sort it. 

We can actually get rid of all of these columns or Hide them rather. So I'm going to select this one and this one here . Selected them All. I can now go to Table and Hide six columns. It will hide those six columns there. They're still there. You can see this is column A and this is column H. The table looks like it did before but now it's sorted in alphabetical order even taking into account middle names, multiple middle initials, and things like Van Buren.

Now I'm going to include this example file for you to check out if you want. Just look for the link at this post at MacMost.com.