Last Name Sort In Mac Numbers

The latest versions of Numbers make it much easier to sort by last name, even if the data includes only full names. The new functions TEXTBEFORE, TEXTAFTER and COUNTMATCHES allow you to put last name before the first name. You can also use REGEX to do it with an even shorter formula.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi, this is Gary with MacMost.com. Today let me show you how to take a list of names in Numbers and sort them using Last Name first.
MacMost is brought to you thanks to a great group of more than 800 supporters. Go to MacMost.com/patreon. There you can read more about the Patreon Campaign. Join us and get exclusive content and course discounts.
So back in the beginning of this year I showed you how to take a list of names, like this,  and then convert them to Last Name, First Name, like this, using some complex formulas and lots of extra columns. The technique I used involved first figuring out how many spaces were in the name which turns out to be very difficult. You have to count how many characters with spaces and subtract how many characters without spaces to get that number. Then you had to substitute a special character, like a semi-colon, for the last space in the name. So first name and middle name remain together and the last name is separate. Then you find the location of that space and from there you could use left and then right to get the first name and the last name separately. Then you could put those two together. Now that's pretty complex.
Fortunately, since I made that video Apple introduced some new functions that make this a lot easier. Now the basic idea remains the same. We need to figure out where that last space is. So we find the space between George and Washington. But we also find the space between Quincy and Adams in John Quincy Adams. Fortunately there's a new function that will take care of this for us. It's called CountMatches. So we'll use CountMatches and if we look it up here we could see it takes the source-string and the search-string. So the source-string is the name here. The search-string is simply a space. So a space between two quotes. We could see we get one here. If I copy and paste that throughout you could see I get 1 mostly but I get a 2 where there's a middle name or first name and an initial. Now to grab the actual last name all we need to do is grab everything after the last space.
So there's another new function called TEXTAFTER. If we look that up we could see that it takes source-string, search-string, and occurrence. So occurrence is where that number comes in handy. If there's just one space we need to look for what's after the first space. If there are two spaces because of a middle name we need to look for what's after the second space. So we could simply do TEXTAFTER and then this is the source, what we're looking for is the space so again quote space quote but then we leave the CountMatches there so it's going to look for the first occurrence if there's one space and the second occurrence if there's two spaces. Now this will return for us the last name. So we could see it works for that and if I paste it throughout you could see it even works for cases where there is a middle name. Now getting everything before this is essentially the same thing except instead of TextAfter we use TextBefore. So I'm going to Copy this entire thing and I'm going to Paste it before it with an ampersand and then quote comma space quote ampersand. So it's basically going to put a comma and a space between the first and last name. So it's going to find the last name and then comma and a space and then instead of TextAfter let's use the new TextBefore which is the same as TextAfter except it's going to return the opposite part of the string. Everything before that space. So TextAfter will give us the last name. Then we have a comma and a space. Then TextBefore will give us everything before the last name including a first name and middle name. So now we see here we get Washington, George. If we copy and paste that throughout you can see it works for John Quincy Adams, it works for William Henry Harrison.
So now we have one function. It's not short but it's much easier to understand and things like TextAfter, CountMatches, TextBefore are pretty straightforward. Now we can call this column Last, First and if we want to sort alphabetically we can sort using this column and get last name, first name sorting rather than first name, last name. You could even see here it works for George H.W. Bush because Bush is after the last space and everything before the last space is put after the comma. What about Martin Van Buren? How did it work for Martin Van Buren? Shouldn't it be Buren, Martin Van. Well, Van Buren is a last name despite the fact that it has a space in it. In a special case like this you need to indicate that the space isn't a word separating space. By going in and instead of having a regular space, like I'll type a space here, now you could see it actually does do Buren, Martin Van. But if instead of that I hold the Option key down and then hit the space it puts a non-breaking space, a different character than a regular space between Van and Buren. So now Van and Buren are kept together because that's not a real space between those two words. It's an Option Space or non-breaking space. So you just have to be aware in space situations like this to use that special character in order to get last  and first working correctly.
Now since I'm showing you the new functions TextBefore and TextAfter I did want to take time to show you another one called TextBetween. So TextBetween works by taking the source-string and then a first character to look for and then a second character to look for. You could also do a first-occurrence and a second-occurrence. So, for instance, if we wanted to use George Washington as an example here I could look for everything between the first o and the first i and it will give me the text between the o and the i. You could see it works like that. But I could also include Occurrences. So I could look for spaces. Look for the first occurrence and then I could look for the first occurrence after that. So you would think you would put a 2 here to find the middle name. In fact you put 1 to be the next occurrence after that. This will give me the middle name. Of course George Washington doesn't have one. But if I Copy and Paste you could see I can indeed get Quincy and Henry here. George H.W. Bush doesn't capture both the H and the W because it's looking for the space after George and then the next space. But if we use the same technique before and do CountMatches in this text and the number of spaces there then we subtract one because we're not counting that first space here. Then we actually do get everything between the first space and the last space. 
We can make this a little nicer by using IFERROR and then just having a blank so instead of an error we'll get blank there. If there is no error we'll get the actual value. So I Copy and Paste this throughout and now you can see I get just the middle names when a middle name is available.
Now it turns out there's also another way using another new function to be able to do this. It's using Regular Expressions. Now Regular Expressions are a lot more complex. So for most people it's going to be easiest to understand TextAfter and TextBefore and CountMatches than a Regular Expressions. But if you like Regular Expressions you basically use the substitute function to substitute one thing for another inside of text. The thing we're going to look at is the Name, then we're going to use REGEX inside of Substitute which will allow us to do a really complex search inside the original text. It's basically going to look for two things with a space in-between them. The dot and asterisk stand for basically any number of any character. So it's going to look for any number of any character, then a space, and any number of any character after that. The way REGEX works the space is the last space it finds. So in other words everything before the last space and everything after the last space. Then it's going to substitute the reverse of this. So it's going to take the second object, this thing which should be the last name, then a comma and a space, and then the first thing it finds. Everything before the last space so it would be the first and middle names or initials. We see that this rather short formula does exactly the same as this much longer one. It's just a little harder to understand. If I Copy and Paste that throughout you could see it works pretty well including recognizing our special non-breaking space, including recognizing middle names and even including recognizing multiple middle names or initials.
So that's how you can use new functions like TEXTBEFORE, TEXTAFTER, TEXTBETWEEN, COUNTMATCHES, SUBSTITUTE, and REGEX to make it a lot easier to create simple functions that convert a regular name to last name, comma, first name so you can sort them in Numbers.

Comments: 6 Responses to “Last Name Sort In Mac Numbers”

David Chadderton

4 years ago

I'm sure I saw in the video when you showed the function help that you can use -1 in TEXTBEFORE and TEXTAFTER to select the last match, which would simplify your formula a bit as COUNTMATCHES would be unnecessary.

Gary Rosenzweig

David: You're right!

Gene

Another fine video! How did you discover the "fake space" feature? That is option+space for Van Buren?

Gene: It has been around for years. I used it in the 1990s for desktop publishing when you want to keep two words together on the same line and for similar things.

Dan Bails

This is great Gary, You know almost everything, right? I have another wrench for my own list. I have a mixture of people's names and company names. I have tried putting the "fake space" in the company name but I get an error. Barn Yoga Group - "Argument 3 of TEXTAFTER is invalid." I guess your formula doesn't work if there are no spaces.

Dan: Right, it needs a space to work. You can use IFERROR to just use the text as-is if you like.

Comments Closed.

Last Name Sort In Mac Numbers

Comments: 6 Responses to “Last Name Sort In Mac Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information