Hi this is Gary with MacMost.com. Today let's take a look at using
text manipulation functions in Mac Numbers.
MacMost is brought to you thanks to a great group of more than 1000 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 often in Numbers you need to use functions to manipulate text. There are a whole variety
of functions for this. Let's take a look at the basics. One of the simplest things you can do
with text in Numbers is to get the length of the text. So, for instance, here we've got some names
in column A and let's get the length of each name. So this formula here for that is just LEN, short
for length, and then the only parameter it takes is the text right here. You can see the result
there is 14 for this one. If I Copy and Paste it throughout you can see I get the length for each
name there. So, for instance, if you wanted to Sort by the length of each name I could now Sort
on this column here, I'll Sort Descending, and I get the person with the longest name first.
Now what if you wanted to count the occurrences of letters in text. For instance get the number
of times the letter A appears. So you could do that using COUNTMATCHES and you simply provide
the source and then what you're trying to match. In this case just the letter A. So it finds one A
here in this first row. Then you could see the number for each row after that. So you could
use this to count the number of words. You have to be a little sneaky about it. You can count
the number of spaces. So here we're looking for the number of spaces. Now since a single space
would denote that there are two words, one to the left and one to the right,
we're going to add one to this. So we get the number of words here by simply counting
the number of spaces and then adding one. In this case all of these just contain two words.
Now one thing you may need to do with text is to concatenate it. In other words to combine
or merge pieces of text together. You can do it in one of two ways. We've got a name here and
then a group letter. So if we wanted to combine it to create sentences like name is in group and
then the group letter with a period after it, we can do it using the concatenate function.
So this just takes as many parameters as you want to path into it. In this case the first
one here is the name and then in quotes some text here, space is in group space,
and then the group here and then also a period at the end. This concatenates all four pieces of text
together to give you this. But in most cases you don't need to use the function concatenate. You
can use the ampersand character. This formula here does the same thing. It's the same four
pieces of data but just putting an ampersand between them will concatenate them into text.
So what if you want to see if text contains a letter or
several letters. Well, you can do this in a few ways. One is to use COUNTMATCHES. COUNTMATCHES
will take the source and then whatever it is you want to search for and then it will return the
number of matches. So if I were to paste this in here I could see a lot of zeros and a few ones,
even a two down here where the AN characters are repeated twice. Now if you goal is actually to see
whether those characters are there or not then you want a True/False answer, not a zero or
one. You could do this a variety of ways. One is you could just do COUNTMATCHES, just like before,
and check to see if it's greater than zero. This is a test so it's going to return a result that's
either True or False. So it doesn't matter if there are one or two. It's just going to be True.
Now you can use two different functions as well. One is FIND. So FIND looks like this. It will
search for something in the source and then it will return the position. In this case it
finds A and N starting at the third character. Now if I were to select this and paste it you
are going to see a lot of Error messages here because it can't find that set of characters at
all here. So it gives you an error. So what you can do is surround this with ISERROR, like that,
and that will give you the True or False. Unfortunately it's kind of the opposite.
It's going to give you a True when it doesn't find it because it's telling you, true there is
an error here. So we can surround this with NOT. This is pretty common to use NOTISERROR like this
and now we get True and False, depending upon whether it finds the thing it's looking for.
Another thing you can use is SEARCH and it works in a similar way and gives you similar results.
We probably want to surround this with NOT and ISERROR as well. Then we can paste this in here.
If we look at Help for FIND you can see FIND is simply returning the starting position of
one string within another. SEARCH ignores case and allows wildcards. So it's a little more versatile.
Now what if you want to deal with substrings. You can use Left, Right, and MID to get
some simple substrings. For instance, LEFT with the source and then comma 3 gives you
the three characters to the left. RIGHT will give you the same thing but to the right. MID
allows you to have two parameters. The starting position and how many characters to return. So we
have 5, 3 here. It's going to grab from character 5, three characters. In this case space M and i.
So if I were to Copy and Paste these you could see how it handles in different situations here.
Now what if you wanted to get the first word. In other words everything up to the first space.
Well you can do that by using LEFT, getting the same source, and then using SEARCH
and searching for a space. It's going to give you the position of the first space.
So now we get the first name here. As a matter of fact the space is actually one character too
far over so we can subtract one and that way there's no extra space here at the end. Now
you can do the same thing for the last space using MID and searching. You probably want
to add 1 here to go one past the space. Then say give me 99 characters because
no last name is going to be that long. Then we get the last name looking for that space there.
There are also some other ways to do this. You can use Text Before, Text After,
and Text Between. So you can simply use TEXTBEFORE the space or TEXTAFTER the space or TEXTBETWEEN,
in this case I'm looking for everything between a space and the letter O to get a result.
Now Regular Expressions make text functions so much more powerful. The Regular Expressions
are really difficult to use. There are entire thick books on just using Regular Expressions.
The people that use Regular Expressions most are usually programmers. They are pretty complex. But
here's some simple examples. Let's say you want to count the number of words in here.
You can use COUNTMATCHES looking in the text and then do a Regular Expression and this will
look for groups of characters that are in words. In other words not white space, not
spaces, commas, periods, things like that. So when it finds these groups it will put
them together as a match and it's going to count the number of matches. So I'm going
to get two here. If I paste into everything I get two's for everything except a 3 here
because there's a hyphen which also is not a word character, it's not a letter.
A simpler example is counting only letters. So I can COUNTMATCHES and I'm going to look
for Regular Expression that is anyone of these, the letters A to Z capital, the letters a to z
lower case with brackets around them showing just one character that matches this pattern.
So I get 13 here because there are thirteen characters that match this pattern. There are
actually fourteen characters but one is a space so it doesn't count. I can use Regular Expressions to
get the first word as well by using REGEX.EXTRACT on that same word. I can look for things that are
word characters, group them together and get the first one of those from the first capture
group. So what I'm going to get there is the first word, in this case the first name. Then
I can do the same thing with the second word by simply looking for the second item in the group.
Now let's say I wanted to get everything between two letters but the letters were
sometimes different. Like maybe they were vowels. I could actually do a TEXTBETWEEN
and a Regular Expression that contains anything in this group, all the vowels,
and another group of vowels. Then I'm going to get from the first occurrence to the last occurrence
using a negative one to denote last. So I get everything between the first and last vowels.
Now what if you want to replace text. Like, for instance, I wanted to replace the
third and fourth characters. I could do that using REPLACE,
the original string, and starting with character 3 going two characters, replace it with two dashes.
That's what I get there. A more useful example would be to replace all the letters in the last
name except the first one. In other words give the first name and initial. So in this case I'm
going to SEARCH for the first occurrence of space and then add two to that. So go past the space,
past the first letter of the last name. That's where to start. Then to replace every letter
past there. Just put 99 so it replaces as many letters as it can. Replace them with a period.
So I get the first name, last initial for each one of these. You can also substitute letters.
So a very simple example SUBSTITUTE every letter A with a bullet character there. So you can see
what I get there. I can use REGEX with this as well. So SUBSTITUTE any vowel with a bullet.
Finally some miscellaneous things that could come in handy. If you want to clean up some text you
can use LOWER, UPPER, or PROPER. Lower will just convert everything to lower case. Upper converts
everything to upper case and Proper will convert everything so the first letter is capitalized and
everything else is lower case. REPEAT allows you to repeat a character a number of times.
That can be useful. I'm going to show you can example of that in a second.
TRIM is very useful for cleaning things up because it gets rid of white space to the left or right.
So, for instance, if the name here was from some data that wasn't very clean and had a bunch of
spaced in front of it you can see the spaces are here, here, and here. But TRIM actually got rid of
those spaces. Here's an example where you can just use REPEAT. Here it's just repeating seven times.
But what if instead of seven if you used the value that was in this column. So it's going to repeat
once for this one here but five for this one. So you could actually kind of get a little graph here
inside of the Numbers spreadsheet and see which values are large and which are small.
So those are all the building blocks. Those are all the basic things that you could do
with Text in Numbers. Hopefully you could take those, combine them, use them in different
ways to get the results you want in your spreadsheet project. Thanks for watching.