Microsoft Excel Tutorial: Using a function in Python for Excel calculations.
The problem today: Count how many times a word occurs in a cell in Excel.
To download this workbook: Excel Python Custom Function - 2617 Sample Files - MrExcel Publishing
The first solution is a series of six formulas in Excel, including SUBSTITUTE, LEN, and more. While it is complicated in Excel, there is a much easier way in Python, using the .Count function. So, Python has a simpler version but how do you call the function from Excel?
After the Python solution, I used the Excel Labs add-in to convert the original six formulas to a LAMBDA.
Other topics here:
Saving Python function in a cell
Adding Text as line line of a Python script to appear in the cell.
Printing to the Python Console
Removing the Index column using two sets of square brackets
In the Out take, the Python Function can not be part of a table.
Table of Contents
(0:00) Python functions in Excel
(0:17) Problem Statement Count ThisWord in Phrase
(0:54) SUBSTITUTE function in Excel
(1:16) LEN function in Excel
(2:29) Python count function
(2:54) Storing Python function in A1
(3:29) Adding Text to Python to appear in cell
(4:15) Calling the Python function using Excel data
(4:45) Call per row or whole frame
(5:10) Printing to Python Console in Excel
(5:35) Does not work next to Ctrl+T tables
(5:56) Removing Index returned by Python in Excel
(6:52) Returning one column? Use double square brackets to prevent Index
(7:39) Writing a LAMBDA using Excel Labs add-in
(8:11) Add Function from Grid
(8:48) Testing the LAMBDA version
(9:33) Wrap-up
(9:59) Like, Subscribe, Ring the Bell
(10:06) Outtake Does not work with Tables
The problem today: Count how many times a word occurs in a cell in Excel.
To download this workbook: Excel Python Custom Function - 2617 Sample Files - MrExcel Publishing
The first solution is a series of six formulas in Excel, including SUBSTITUTE, LEN, and more. While it is complicated in Excel, there is a much easier way in Python, using the .Count function. So, Python has a simpler version but how do you call the function from Excel?
After the Python solution, I used the Excel Labs add-in to convert the original six formulas to a LAMBDA.
Other topics here:
Saving Python function in a cell
Adding Text as line line of a Python script to appear in the cell.
Printing to the Python Console
Removing the Index column using two sets of square brackets
In the Out take, the Python Function can not be part of a table.
Table of Contents
(0:00) Python functions in Excel
(0:17) Problem Statement Count ThisWord in Phrase
(0:54) SUBSTITUTE function in Excel
(1:16) LEN function in Excel
(2:29) Python count function
(2:54) Storing Python function in A1
(3:29) Adding Text to Python to appear in cell
(4:15) Calling the Python function using Excel data
(4:45) Call per row or whole frame
(5:10) Printing to Python Console in Excel
(5:35) Does not work next to Ctrl+T tables
(5:56) Removing Index returned by Python in Excel
(6:52) Returning one column? Use double square brackets to prevent Index
(7:39) Writing a LAMBDA using Excel Labs add-in
(8:11) Add Function from Grid
(8:48) Testing the LAMBDA version
(9:33) Wrap-up
(9:59) Like, Subscribe, Ring the Bell
(10:06) Outtake Does not work with Tables
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2617, Python Custom Function.
Actually a couple of titles.
How many times does this word occur in that long transcript? Or, hey not so fast there, Sparky.
Excel Labs can already do this better than Python.
All right, today's question. [ Fred: ] MrExcel, easy question for you today.
I have an entire season of Seinfeld scripts in Excel.
I want to count the number of times any word appears like Fred or soup or suit.
Can we do this?
[ Bill: ] Today's question, we have a phrase up here and I have a word and I want to know how many times this word occurs in this phrase. Now, in this case it's easy.
Boot, boots, boot. Three times.
But to solve this in Excel, well hey, yeah, it's super simple and super obvious.
It's just five steps. So step number one.
We take the first phrase, B3, and substitute every occurrence of the word B4, with nothing, quote, quote.
And so you see that gets rid of all occurrences of boot from the first phrase.
Yep, I know you're trying to count, just bear with me. All right.
Then second thing we do is we figure out the length of the phrase before we did anything to it.
It was 61 and then the length of the phrase after we removed the word boot three times and it was 49.
So how much did it change? 61 minus 49 is 12.
The word that we removed, how long is it?
So in this case, boot is four characters, that's the length of B four. And then to get the word count.
Finally our answer is we take that 12 and we divide it by the length of the word removed and that tells us how many occurrences there were.
Now of course you wouldn't go through all of those steps if it was just this phrase, you would just look at it.
But like here I have a phrase that's 12,000 characters.
That's a transcript for one of the podcasts.
I know how many times I said Excel and it comes up with 10 times. Or if I look for Python it comes up with 13 times.
So it is a great useful simple formula you just knock out.
[ Fred: ] Are you crazy MrExcel? How can any normal human figure that out?
I see everyone is talking about Python and Excel.
Is it easier?
[ Bill: ] Let's take a look at how Python would do this.
They have a function called count that operates on a data frame and you just say, I want to count the number of times this word occurred.
So here's our data frame, Excel D2 and we do the count of boot and the answer is three.
Wow, that's really simple.
Okay, so since it's so much simpler to do this in Python, then Excel, can we build sort of a custom python function?
All right. So here's what I've done back here on this sheet.
I have up and cell A1 the definition of a function and I've added a little bit of extra stuff in that I take the lower of the text coming in and then do a count of which word.
So we're basically taking the original data frame, adding a new column called word count, and then we need to know which column, so in this case it's the column called transcript.
And then apply this function to take the lower and then count.
The function itself actually ends here with return df, if I just had that in cell A1, I'm not sure that we would get anything. It would just be a zero or blank.
So there's this great trick that everyone is using to add a new last line to the formula.
And that last line explains what's happening, that there's something in the cell.
Don't just delete the cell or delete this column.
And in my case, I originally had function loaded, but I said, well wait a second, wouldn't it be nice if I actually documented the functions called this word count?
I have to pass it a data frame, the column name and which word I'm looking for. Cool.
So that's kind of my definition of the custom Python function.
And then down here is my input cell. That's the which word?
Here's the list of transcripts from several different podcast episodes.
And right here that's one line of code.
So we call the function name, this word count, we have to pass it a data frame.
So that's Excel A8 to B16, where headers equals true. I could even do this.
B8 to B16, I don't have to specify the whole thing.
And then which column?
It's the column called transcript and which word we want to produce, Excel. Now we're going to talk about this word count.
There's a lot of beating my head against the wall to get this to work, but let's just take a look.
I'll do control enter and there's our answer.
And now if we change it from Excel to Python, it's pretty fast. One, two, three, four, five.
And we actually did a little test here with the console.
I want to know, is it calling this function once for each of the rows or is it calling it just once for the whole thing? So I did something like this.
I added a line called print calling function.
Now print is used a lot in regular Python and it returns something to the console.
Here in Excel it's returning something to the diagnostics tab.
So if I change from Python to Excel, we'll see that there is a fresh call right there. So it's efficient.
It's just calling the thing once it operates on the whole data frame and returns those answers.
All right, a couple of things we discovered. Originally I had this as a table, Control+T.
And just like dynamic arrays don't work well with tables, these Python formulas don't work well with the tables. It actually wiped out my formula.
It just changed it to a zero.
It was like, nope, not going to allow you to do that.
And then the other thing that drove me insane is I knew that I had transcript and word count in the result.
And so I didn't want to get that. Check this out.
See I get transcript again and I'm like, ah, I don't need the transcript again.
And there were many, many times where I came here and said that I wanted just word count, like that, Control+Enter.
And it drove me freaking insane that every time it was adding an index, I went out and googled how to remove the index in Python.
And it had three different formulas and none of those formulas worked here.
Excel would turn the data sideways and was changing it to a string instead of a number and just drove me freaking insane.
Thanks to Suat who works here at MrExcel for suggesting, hey, by having just one set of brackets, you're creating a series. Two sets of brackets, will create a data frame.
And sure enough, it worked and there was much rejoicing.
All right, now here I have to look at this.
Every time that I'm calling the function, I'm printing something over here, so I want to get rid of that.
All right, good.
And then Control+Enter. All right, so there we go.
We have something that is working in Python, pretty cool little custom function up there.
And then calling the function sort of like we would with a UDF.
It'd be nice if we could name those parameters maybe and maybe you can, I don't know, but works pretty cool.
Now, hey, just a lot of times here in these past few videos, people are like, well wait, why are you doing this?
Couldn't you have just done this in straight Excel?
And a shout out here, of course we could do it in straight Excel with these formulas, but the Excel Labs folks over in Cambridge have just an amazing tool.
So this is an add-in called Excel Labs.
You go to insert, get add-ins and look for Excel Labs.
And then out here on the far right hand side of the home tab, you'll have this Excel Labs button.
We want to get into the advanced formula environment.
This is just some amazing functionality that's kind of buried in here.
You want to click the second tab across the top names.
And then this fourth icon is called add function from grid.
All right, so we've selected the system of formulas, click add function from grid.
They detect that the input cells are A17 and B17. The output cell is H17.
When we click preview, they use the headings from the row above in the let function.
They create a lambda called this word count.
We actually have to click create and then click save.
And now this workbook, if we look in the name manager has this word count to find and we can use it anywhere in the workbook.
So test lambda equal this word count. It even asked me for the phrase.
All right. So that's cell B6.
And the word, I'll press F4 on that one to lock it down.
Double click to copy it down, like that.
And we change from Excel to Python, works. We change to basic, we change to right.
Oh hey, check this out. Can we call this just once?
So B6 to B13 past the whole set of phrases. Yes, that continues to work.
I mean let's face it, this Lambda function is going to work in more places than the Python is going to work right now.
All right, well hey, I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
Hey, thanks to Fred Stoller, staff writer at Seinfeld.
Check out his book, My Seinfeld Year available on Amazon.
Fred Stoller: I Hello MrExel. I'm Fred Stoller.
Maybe you know me as Fred Yerkes from Seinfeld.
I also wrote the soup episode, not the Soup Nazi, the soup counts as a meal.
MrExcel: If you like these videos, please down below like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
Hey, here's just a little outtake that document why you can't do this next to a table.
So Control+Shift+P to get into Python.
This word underscore count and I'm going to point to the table and we want the field called transcript.
And the word is this one.
Close the function, two less square brackets, word count, Control+Enter.
We get no results. The formula that we just entered is just gone.
Just forget you. We're not even going to acknowledge your work.
It just disappears.
Said it a million times back in Excel 2007, they tried to put tables in, they realized it wasn't going to work.
They wanted to take it out.
It would be more work to take it out than to leave it in broken. So here we are, it's still broken.
People ask me why don't use tables? It's a feature that was never finished.
Actually a couple of titles.
How many times does this word occur in that long transcript? Or, hey not so fast there, Sparky.
Excel Labs can already do this better than Python.
All right, today's question. [ Fred: ] MrExcel, easy question for you today.
I have an entire season of Seinfeld scripts in Excel.
I want to count the number of times any word appears like Fred or soup or suit.
Can we do this?
[ Bill: ] Today's question, we have a phrase up here and I have a word and I want to know how many times this word occurs in this phrase. Now, in this case it's easy.
Boot, boots, boot. Three times.
But to solve this in Excel, well hey, yeah, it's super simple and super obvious.
It's just five steps. So step number one.
We take the first phrase, B3, and substitute every occurrence of the word B4, with nothing, quote, quote.
And so you see that gets rid of all occurrences of boot from the first phrase.
Yep, I know you're trying to count, just bear with me. All right.
Then second thing we do is we figure out the length of the phrase before we did anything to it.
It was 61 and then the length of the phrase after we removed the word boot three times and it was 49.
So how much did it change? 61 minus 49 is 12.
The word that we removed, how long is it?
So in this case, boot is four characters, that's the length of B four. And then to get the word count.
Finally our answer is we take that 12 and we divide it by the length of the word removed and that tells us how many occurrences there were.
Now of course you wouldn't go through all of those steps if it was just this phrase, you would just look at it.
But like here I have a phrase that's 12,000 characters.
That's a transcript for one of the podcasts.
I know how many times I said Excel and it comes up with 10 times. Or if I look for Python it comes up with 13 times.
So it is a great useful simple formula you just knock out.
[ Fred: ] Are you crazy MrExcel? How can any normal human figure that out?
I see everyone is talking about Python and Excel.
Is it easier?
[ Bill: ] Let's take a look at how Python would do this.
They have a function called count that operates on a data frame and you just say, I want to count the number of times this word occurred.
So here's our data frame, Excel D2 and we do the count of boot and the answer is three.
Wow, that's really simple.
Okay, so since it's so much simpler to do this in Python, then Excel, can we build sort of a custom python function?
All right. So here's what I've done back here on this sheet.
I have up and cell A1 the definition of a function and I've added a little bit of extra stuff in that I take the lower of the text coming in and then do a count of which word.
So we're basically taking the original data frame, adding a new column called word count, and then we need to know which column, so in this case it's the column called transcript.
And then apply this function to take the lower and then count.
The function itself actually ends here with return df, if I just had that in cell A1, I'm not sure that we would get anything. It would just be a zero or blank.
So there's this great trick that everyone is using to add a new last line to the formula.
And that last line explains what's happening, that there's something in the cell.
Don't just delete the cell or delete this column.
And in my case, I originally had function loaded, but I said, well wait a second, wouldn't it be nice if I actually documented the functions called this word count?
I have to pass it a data frame, the column name and which word I'm looking for. Cool.
So that's kind of my definition of the custom Python function.
And then down here is my input cell. That's the which word?
Here's the list of transcripts from several different podcast episodes.
And right here that's one line of code.
So we call the function name, this word count, we have to pass it a data frame.
So that's Excel A8 to B16, where headers equals true. I could even do this.
B8 to B16, I don't have to specify the whole thing.
And then which column?
It's the column called transcript and which word we want to produce, Excel. Now we're going to talk about this word count.
There's a lot of beating my head against the wall to get this to work, but let's just take a look.
I'll do control enter and there's our answer.
And now if we change it from Excel to Python, it's pretty fast. One, two, three, four, five.
And we actually did a little test here with the console.
I want to know, is it calling this function once for each of the rows or is it calling it just once for the whole thing? So I did something like this.
I added a line called print calling function.
Now print is used a lot in regular Python and it returns something to the console.
Here in Excel it's returning something to the diagnostics tab.
So if I change from Python to Excel, we'll see that there is a fresh call right there. So it's efficient.
It's just calling the thing once it operates on the whole data frame and returns those answers.
All right, a couple of things we discovered. Originally I had this as a table, Control+T.
And just like dynamic arrays don't work well with tables, these Python formulas don't work well with the tables. It actually wiped out my formula.
It just changed it to a zero.
It was like, nope, not going to allow you to do that.
And then the other thing that drove me insane is I knew that I had transcript and word count in the result.
And so I didn't want to get that. Check this out.
See I get transcript again and I'm like, ah, I don't need the transcript again.
And there were many, many times where I came here and said that I wanted just word count, like that, Control+Enter.
And it drove me freaking insane that every time it was adding an index, I went out and googled how to remove the index in Python.
And it had three different formulas and none of those formulas worked here.
Excel would turn the data sideways and was changing it to a string instead of a number and just drove me freaking insane.
Thanks to Suat who works here at MrExcel for suggesting, hey, by having just one set of brackets, you're creating a series. Two sets of brackets, will create a data frame.
And sure enough, it worked and there was much rejoicing.
All right, now here I have to look at this.
Every time that I'm calling the function, I'm printing something over here, so I want to get rid of that.
All right, good.
And then Control+Enter. All right, so there we go.
We have something that is working in Python, pretty cool little custom function up there.
And then calling the function sort of like we would with a UDF.
It'd be nice if we could name those parameters maybe and maybe you can, I don't know, but works pretty cool.
Now, hey, just a lot of times here in these past few videos, people are like, well wait, why are you doing this?
Couldn't you have just done this in straight Excel?
And a shout out here, of course we could do it in straight Excel with these formulas, but the Excel Labs folks over in Cambridge have just an amazing tool.
So this is an add-in called Excel Labs.
You go to insert, get add-ins and look for Excel Labs.
And then out here on the far right hand side of the home tab, you'll have this Excel Labs button.
We want to get into the advanced formula environment.
This is just some amazing functionality that's kind of buried in here.
You want to click the second tab across the top names.
And then this fourth icon is called add function from grid.
All right, so we've selected the system of formulas, click add function from grid.
They detect that the input cells are A17 and B17. The output cell is H17.
When we click preview, they use the headings from the row above in the let function.
They create a lambda called this word count.
We actually have to click create and then click save.
And now this workbook, if we look in the name manager has this word count to find and we can use it anywhere in the workbook.
So test lambda equal this word count. It even asked me for the phrase.
All right. So that's cell B6.
And the word, I'll press F4 on that one to lock it down.
Double click to copy it down, like that.
And we change from Excel to Python, works. We change to basic, we change to right.
Oh hey, check this out. Can we call this just once?
So B6 to B13 past the whole set of phrases. Yes, that continues to work.
I mean let's face it, this Lambda function is going to work in more places than the Python is going to work right now.
All right, well hey, I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
Hey, thanks to Fred Stoller, staff writer at Seinfeld.
Check out his book, My Seinfeld Year available on Amazon.
Fred Stoller: I Hello MrExel. I'm Fred Stoller.
Maybe you know me as Fred Yerkes from Seinfeld.
I also wrote the soup episode, not the Soup Nazi, the soup counts as a meal.
MrExcel: If you like these videos, please down below like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
Hey, here's just a little outtake that document why you can't do this next to a table.
So Control+Shift+P to get into Python.
This word underscore count and I'm going to point to the table and we want the field called transcript.
And the word is this one.
Close the function, two less square brackets, word count, Control+Enter.
We get no results. The formula that we just entered is just gone.
Just forget you. We're not even going to acknowledge your work.
It just disappears.
Said it a million times back in Excel 2007, they tried to put tables in, they realized it wasn't going to work.
They wanted to take it out.
It would be more work to take it out than to leave it in broken. So here we are, it's still broken.
People ask me why don't use tables? It's a feature that was never finished.
