Excel Python Custom Function (Count A Word Within a Cell) - Episode 2617

Подписаться 155 тыс.

Просмотров 8 тыс.

50% 1

Microsoft Excel Tutorial: Using a function in Python for Excel to count how many times a word occurs in a cell in Excel.
To download this workbook: www.mrexcel.com/youtube/6Ydb6...
Welcome to another episode of the MrExcel podcast, where we dive into all things Excel. In today's episode, we will be discussing a couple of different titles, including "How many times does this word occur in that long transcript?" and "Excel Labs can already do this better than Python." Our question for today comes from Fred, who wants to know how to count the number of times a word appears in a long transcript of Seinfeld scripts. Can we do this in Excel? Let's find out.
In this video, Bill Jelen, also known as MrExcel, walks us through the process of counting the occurrences of a specific word in a phrase using Excel. He breaks it down into five simple steps and shows us how to apply this formula to a long transcript of over 12,000 characters. But then, he poses the question, can we do this more efficiently using Python? He demonstrates how to use the Python function "count" to achieve the same result in just one line of code. This leads to the idea of creating a custom Python function for this task.
Bill then takes us through the process of creating a custom Python function that can be used in Excel. He explains the code and shows us how to call the function and specify the parameters. He also shares some tips and tricks he learned along the way, such as using two sets of brackets to create a data frame instead of a series. He also shows us how to use the Excel Labs add-in to create a lambda function that can be used in place of the custom Python function.
But why go through all this trouble when we can just use the formulas in Excel? Bill addresses this question and explains the benefits of using Python for certain tasks. He also shares some outtakes that demonstrate why using tables in Excel can cause issues with these formulas. So, if you're looking to improve your Excel skills and learn how to use Python in Excel, this video is a must-watch. Don't forget to like, subscribe, and ring the bell for more Excel tips and tricks. And as always, feel free to leave any questions or comments down below. Thanks for watching!
Buy Bill Jelen's latest Excel book: www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: www.mrexcel.com/like-mrexcel-...
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 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) Clicking Like really helps the algorithm
(10:06) Outtake Does not work with Tables
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #python
This video answers these common search terms:
using python in excel
python code to count how many times a word appears in a cell
printing to python console in excel
removing index column from python data frame in excel
python in excel will not work with a table
how to use advanced formula environment in excel to combine subformulas
Join the MrExcel Message Board discussion about this video at www.mrexcel.com/board/threads...

Опубликовано:

14 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 37

@Milhouse77BS 10 месяцев назад

Now i guess I need to learn Python.

@amoorinet.. 10 месяцев назад

I was started already after 22 AUg 24

@MrXL 10 месяцев назад

@@LegendScroller Just five more weekends to go.

@benrogers9092 10 месяцев назад

You could add a single char, rather than removing all chars then dividing by word length: =LEN(SUBSTITUTE(B3, B4, B4&"_")) - LEN(B3) Still requires the computer to "find + replace + len" rather than simply "find", though. Appreciate the python info!

@garydunken7934 10 месяцев назад

What? I like Tables and their references in formulas. When you are used to it, it works. You tried to enter PY function on the adjacent column. Normally, when data is entered on adjacent column or row below the last row, the table will expand to include them as table data. Have you tried entering PY formula by leaving a column gap? i.e. leave a blank column in between table and PY function. I can't try it on my PC, because the PY feature hasn't shown up for me yet, since signing up on Beta channel.

@MrXL 10 месяцев назад

99.9% of the world loves Tables. I am the only guy who hates them. Every time that I say on a video that I hate them, I am reminded that most of the world loves them. The only thing I hate more than Tables is blank columns. Some boardroom PTSD from 1991... the day I handed over a report with the left half sorted and the right half effed. I can still hear the CEO screaming at me.

@ricos1497 10 месяцев назад

Great video. I still haven't thought of a use for Python for me yet! The charts you showed in prior videos was a good use case I think. Good that you're still fighting the long term battle against tables though!

@MrXL 10 месяцев назад

I feel like I am the only one on Team-No-Table! Yesterday, I was trying to explain a bunch of events from 2018 to 2023. I put them on a Python Timeline and thought it did a nice job of replacing paragraphs of my prose. That might be the subject of Thursday's video.

@ricos1497 10 месяцев назад

@@MrXL tables are the devil's work, it's just most are too afraid of EXCile for speaking out. A bunch of events from 2018 to 2023? But nothing of note happened in that period, that I can think of anyway. Quite an uneventful time in history. Python timeline, yes, that's very good. A Gantt chart. I think I've maybe done something in python that did that before, but it was fairly rudimentary. Using matplotlib I think.

@marzenababij5148 10 месяцев назад

Thanks🦋💙🦋

@GeertDelmulle 10 месяцев назад

I’m still in denial: I prefer the LAMBDA function in this case anyway (that Excel labs add-in is great!).

@MrXL 10 месяцев назад

If there was a Nobel prize for best Excel function, the Import from Grid in the add-in would get my vote. In my head, I keep hoping to do a timed test of the Lambda versus Python over 2300 rows. I don't think it will be close.

@sahilsinghal9472 10 месяцев назад

Thanks for the video. Question - if I get the Insider Preview, will I be able to use Python in Excel's web version too?

@MrXL 10 месяцев назад

I have heard that for right now, it is just Windows. But I have to believe it will be coming to the web.

@David-vq4ho 10 месяцев назад

Hey Bill, in your outtake where it turns the PY function to a zero, what if you had done the same thing but left an empty column between the table and your PY formula? I’ve noticed if you put something in a column right next to a table it wants to make it part of the table, but if you don’t want that, a quick trick is to just leave a blank column between.

@MrXL 10 месяцев назад

Oh yes! I thought of the blank column. That would have been perfect. But then I would have been smited by the "no blank columns" gods.

@christiancoronado 10 месяцев назад

I follow, it's good, but really what would be huge it's an IDE similar to the VBE where you can write python code, or JS, or any other modern language. Also, VBA aint that bad, although having python as the embedded programming language in excel would be HUGE

@MrXL 10 месяцев назад

From your lips to Satya's ears. I agree this would be the best.

@ExcelInstructor 10 месяцев назад

Hi Bill I am office insider, however =PY() is not availible for me - any ideas why?

@garydunken7934 10 месяцев назад

Apparently, it takes few days to weeks, before the feature is pushed to Beta testers. I signed up on Beta channel 5 days ago, still waiting mate.

@MrXL 10 месяцев назад

It is coming. They broadened the audience today (I am guessing to 25% from 10%). They say, "The more concerned we are that something will break, the slower we roll it out". Apparently, the first week looked pretty good because they've already rolled it out more widely today.

@ExcelInstructor 10 месяцев назад

@@garydunken7934ok, but what happened to beta channels? I mean some time ago I had 3 channels, current, beta, and a3rd one which gave the newest feature the moment they got released. (it worked with the 14 functions froma year ago) and now i have only 2 channels, current and beta :(

@ARSEABOUTFACE 10 месяцев назад

Maybe as simple as =COLUMNS(TEXTSPLIT(A1,B1))-1 Assuming A1 is your string and B1 is your search word.

@GeertDelmulle 10 месяцев назад

Thanks for posting this! Your solution takes the cake in my book. :-)

@MrXL 10 месяцев назад

Yes! Winner! This is awesome. I am going to find someone to cross-stitch this to hang on my wall.

@AccessAnalytic 10 месяцев назад

how about =SUM( (TEXTSPLIT(B3," ")=B4 ) *1 ) This has the advantage of part words not being included, eg. Booth would NOT be counted as Boot

@MrXL 10 месяцев назад

@@AccessAnalytic I have a bigger problem with punctuation in these transcripts. Excel Excel, Excel. Excel! Excel? Excel:

@montmraaa 10 месяцев назад

The Excel formula/function method here is flawed. The text "We won the contest." contains the string "test" but not the word. This could be accomplished in either environment, but doing it comprehensively requires implementing more sophisticated methods.

@MrXL 10 месяцев назад

Agreed. I sort of like the partial match right now. There is a drinking game where you take a shot every time I say Basically, or Alright, or Right. Searching for Right gets you alright and right, so more matches. I also get tripped up when I am looking for Excel and the exact match misses: Excel. Excel, Excel! Excel?

@richardstorm1815 10 месяцев назад

I would like to see you use this python in excel on Mega millions lottery to predict next draw winning numbers

@MrXL 10 месяцев назад

You know that video can't be far behind. The lotto videos always get a lot of views.

@ricos1497 10 месяцев назад

7,12, 23, 47,48 and 16.

@richardstorm1815 10 месяцев назад

@@MrXL please make a video thank you

@Aahzmadius 10 месяцев назад

I used to be more anti-Table but since I use Power Query more and more I am forced to use them. Speaking of: Add Custom Column List.Count(Text.PositionOf([Column1],"boot",Occurrence.All))

@Aahzmadius 10 месяцев назад

Ack, just realized this wouldn't be ideal, since M is case sensitive. You would have to deal with "Boot" as well. More complicated.

@Aahzmadius 10 месяцев назад

Just in case anyone finds this in the future, this is not as complicated as I originally thought. There is an optional Comparer argument available: List.Count(Text.PositionOf([Text String],"boot",Occurrence.All,Comparer.OrdinalIgnoreCase))