How To Use Numbers Magic Fill

Hi, this is Gary with MacMost.com. Let's take a look at using MagicFill in Numbers. So Numbers version 15.1 with an Apple Creator Studio subscription has a new feature in it called MagicFill. And this example here gives you a basic idea of what MagicFill can do. You've got some names here. It's first name, last name. And this may be as data that's taken from somewhere else and it's put into your spreadsheet. Maybe you've got a hundred or a thousand of these names. Then for the first row, you've filled in the first name and last name. Now, this is all the information that MagicFill needs to complete the rest of the table. If you go to this cell here, select it and then click again to fill it in, you'll see MagicFill has two options for you. One is to fill in the single item here. So it gets the name Kevin from this cell based on how the cell above this gets Megan from this cell. And you could just select that and it will fill that one cell in. But you could also go to MagicFill column and it will fill in the entire column. We use that here. It's going to show us what it added. You could see the striped cells here showing you it added those nine cells and what it put there. Gives you the opportunity to accept it or reject it. It also gives you an option whether to add as text or as formulas. We'll look at formulas here in a minute, but for now we can just accept this and we were able to fill in this entire column very easily. The same thing here. If I click twice on this cell, you could see it predicts what the value of this should be based on A3 and based on C2. And I could say, fill in the whole column with that and accept. And now I've easily filled in first and last name columns here based on the info in the first column. Now, another way to do this would have been to select the cells, like all of them like this. And then you can go to table and then MagicFill cells or you can control click, right click or two finger click to bring up the context menu and find MagicFill cells there. And you can use this and then you get the same accept or reject. It's going to do both columns at the same time since we have them selected. I could also have selected the entire columns like this and then used MagicFill there as well. If you find these videos valuable, consider joining the more than 3000 others that support MacMost through Patreon. You get exclusive content, course discounts and more. You can read about it at macmost.com slash Patreon. Now, it's not just about getting say the first word or second word. It's a little smarter than that. So for instance, I've got now last name, comma, first name here. And if I wanted to grab that, I could just give it the example that it needs like this. And then it should figure it out, not including the comma at all. So if I MagicFill here, you could see how it does it. Another trick is that you could partially use MagicFill now and then use it more later. So I can select just these cells and then I could use MagicFill cells like this and accept. And now I could go and hover over the bottom of the selection. I could drag it down to continue filling in more rows. So this is handy if you have to add more like this, you can now select the rows like that and MagicFill down one more. Now it's not always perfect. For instance, here, I want to remove the middle initial, which is only in some of these rows. So I've given it two examples. And if I select this, I see that I can get MagicFill and we'll try to guess what I want, but it doesn't figure it out for all of them. Only three more of those names actually fit the pattern that was there before. This is actually good because I can actually accept this and then go to the rows where it can't match those and do those manually. So it still saves me some time, but then sometimes it's just impossible. Like you've got names like this where there's all sorts of different variations on the number of words and initials and things like that. And you'll find here, if you try to use MagicFill, it's not going to work. It's going to give you maybe just some here for the first column and it's going to get some of those wrong because it doesn't quite understand names. It's not really going any deeper than looking at the patterns in the text and trying to replicate those patterns for each row. But you can take data from multiple columns and combine them into one cell using the same thing. So for instance, I can create a full name column here and give it an example. And when I double click on it, I get nothing here. It's telling me that it can't figure out any kind of MagicFill function for it to do. But if I give it a second example, then if I try to use MagicFill here, it will figure out what I want it to do. So we needed two examples in this case to figure out it's going to combine these like that. If on the other hand, I try to do something like this and the same thing here and clear these out, I could get it to MagicFill in a different way like that. So you could see it's a little bit more complex than just taking data from one cell to another. It can actually take data from multiple cells and combine them. And it can also do conversions. So here I've got first last, and I want to do last comma first. So let me give it one example here. And then if I double click there, I don't get anything. So I'm going to give it a second example like that. And now if I select the whole column and MagicFill, you'll see it got the idea. So it's actually converting from one to the other in this case. This is taking a cell that has multiple lines in it and it's a full address. And then I want it to look like this where it's taking the first name, last name, city, state and zip out. If I try to do MagicFill here, it will kind of work, but not quite. So you could see here, it got the state wrong. What's probably happening as you guess is this is a two word city name, whereas this is a one word city name. So let's fix this and give it the exact right thing like that. Now that it's got two examples there, I can select all the rest of the cells and use MagicFill and it will get them all right. You can also do this in reverse. So here I have everything in its own column and I've given two examples here of how to combine the address. I've created the line breaks by using option and return, by the way. And now if I select all of these and I go to MagicFill cells, it figures it out and gives me these nice addresses. Here's another example of what it can do. Here I've got names and I want first name and last name, but I don't want them to be all capital letters like this. Unfortunately, the data came in like that. So I will give it an example that just has each name capitalized. And if I try to use MagicFill for this, it doesn't get it. A second example is very often needed for it to get things right. Now, if I do it, you can see it gets it right. Gets the first name, last name and does the case like I want. And you could do various other things. Like for instance, here are a bunch of email addresses and say I wanted the domain name for each of those. So I'll just use this first one here as an example like that. And now I can MagicFill and it figures out what I really want. Here are a bunch of file names. Let's see if it can get the idea with just one of those selected. And you can see, no, it didn't. It just took the number of characters from the end. So let's give it another example here and let's see if now it gets what I want and it's getting closer, but still doesn't quite seem to get it. So let's continue to give it some examples and now let's see if it gets it. Now, finally, it seems like we've got the right thing. Even here where there's no extension because that's a folder, it doesn't put anything. Sometimes you got to go more than just the two examples. It needs to have enough variation here and what's going on to figure out what you want. And here's one that surprised me that it worked. So I've got these little numbers here. Could be part numbers for something and there's letters and numbers. So if I give it some examples like this and I try to magic fill here, it figures out that I want all the letters here on the left and the numbers on the right and this one doesn't have any numbers. So it's not sure what to do with it. Probably if I gave it an example like this, it would figure it out. Now let's go back for a minute and take a look at the other options you've got when you do magic fill. If I select all of this and I do magic fill, I can accept it here and it just puts those names in there. But if I do it and I add another name, then you could see it doesn't fill this in automatically. I have to manually go ahead and drag down the auto fill there to do magic fill. But what happens if instead of that, I use the option here to magic fill as formulas. So I accept this and it looks the same, but if I look at the actual value at the bottom left, you could see now it's converted these to formulas. In this case, it's looking for the text before the first space. And then this one is the text after the first space and it's doing it for each one of these. It's got a formula in each one, not the actual hard-coded thing. That's important because if I were to change something here like that, you could see it updates and it wouldn't update if there was actually the name that was in there. Furthermore, if I were to add another row here like that and then press return, you could see it fills it in. That's because this cell and this cell have that formula in it that's automatically put in when you add another row by pressing a return there. So it's often more useful to have the formula, but also this gives you a peek behind the curtain. You could see exactly what it's doing. If we go over here, for instance, and we say, yeah, magic fill this, but give us the formulas like that. Now I can select one of these and I could see what it's doing. It's giving us the text before the comma. And in this case, the text after the space. Interesting. They could say, oh, I might mess it up if there's a missing space there, for instance. Let's look at this one here. If I use magic fill here and switch to as formulas, you can see here what the formula is going to be before you even insert it, since it's just one column. So it's going to concatenate the B column, put a comma and then a space, and then put the A column there. Here, if I go ahead and use magic fill with the formulas, it's using a regular expression to look for the characters before the first number. And here it's using a similar regular expression, just get the numbers on the end. So magic fill in numbers is an interesting feature. It'll work in a lot of situations. And when it does, it can save you a lot of time, but it won't work in all situations. And whenever there's a little variety in the data, then you might run into some issues. If you're good with formulas, you probably don't need it. You could do this all by creating your own formulas, but it doesn't mean it can't save you time in some situations. Try a few examples of your own in a sample numbers document, so you get the hang of it, so you know how to use it when you need it. Hope you found this useful. Thanks for watching. Want to know about every new MacMost video each week? Sign up for the absolutely free MacMost email newsletter at macmost.com slash newsletter.