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.