Functions For Working With Text In Mac Numbers

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.