Apr 09 2022 05:28 AM
Apr 09 2022 05:28 AM
Cool, So I have multiple questions I need help with regarding formulas and macros.
ok, So I have a cell that contains paragraphs of text. Imagine I'm a teacher, and each student gave multiple paragraphs of text. Column A has the student's name, column B has the text.
(Row 1 is title/headers ofc)
Ok, now everything regarding column B.
1. Need the formula to find a specific word in the cell, provide it True/False if found. (in column C).
2. Then, how many times that specific word in the cell was found. (eg, how many times the word "the" was in the cell". (count of matches in Column D) (this can be dynamic, eg, Change a lookup cell with words, or a range of cells to lookup. like, finding the word "all" "therefore" "the" "hence" and give a total count or individual count of it all.
3. Super hard? So now imagine the paragraph is a theater script. with what the person says is followed by ":"
(eg, John: howdy partner!" Lisa: g'day Aaron: Hello y'all everyone John: Hello y'all, nice to meet everyone finally)
Here, I'll have a cell (or range of cells like in (2.)) to look up. Say this word is "Hello".
And I want the output to be the sentence/utterance that preceded it till the match on ":". With its multiple matches.
Thus the output of this formula would search the keyword "Hello y'all" (case insensitive) and provide the output.
Hello y'all everyone
(these are separated by a newline/enter key)(or separated by commas, cause I can use "find and replace" to change all commas to newline anyways)(in the same cell)
same cell output cause I have multiple students and I need it for each student individually, and there are 500-1000 students usually in the workbook, a new batch of students every day.
4. Finally, If possible, a macro to quickly do all this, but the output is on a neat and clean new sheet.
Apr 09 2022 06:10 AM
Apr 09 2022 07:48 AM - edited Apr 09 2022 07:50 AM
Thank you so much for the function! you're a god.
however, I need to tweak it a bit.
so the UC, was for the student Amy.
when Hamlet used the word "my" in his sentence, I needed to know what Horatio said.
not what were the words before "my" in the same sentence hamlet uttered.
For those where "my" is in the sentence the very first person said. Since there is no previous person, it can return a blank.
If possible, to make the output like ": Where, my lord?HAMLET:"
i.e, Searching from the nearest ":" to the next nearest preceding ":".
or ideally HORATIO: Where, my lord? (but given how dynamic the ideal output is, just Searching from the nearest ":" to the next nearest preceding ":". as stated above is more than enough.
What changes do I need to make in this code you've provided. (also, how to get this copy of this function permanently on all my workbooks?) TYSM! you're so awesome at this!
Function ExtractParts(Txt As String, Keyword As String) As String
Dim strLines() As String
Dim varLine As Variant
Dim i As Long
Dim p1 As Long
Dim p2 As Long
Dim n As Long
Dim strReturn() As String
strLines = Split(Txt, vbLf)
For Each varLine In strLines
p2 = InStr(1, varLine, Keyword, vbTextCompare)
If p2 > 0 Then
p1 = InStr(1, varLine, ":")
n = n + 1
ReDim Preserve strReturn(1 To n)
strReturn(n) = Trim(Mid(varLine, p1 + 1, p2 - p1 - 1))
ExtractParts = Join(strReturn, vbLf)
Apr 09 2022 08:59 AMSolution
If you want the function to be available in all workbooks, store it in a module in an add-in or in your personal macro workbook PERSONAL.XLSB.
Is the attached version better?
Apr 10 2022 10:19 AM
OMG OMG OMG OMG!!!!! This is exactly what I needed!!!!
However, I'm facing a problem on how to save what you've made to all my excel workbooks from now on.
I copied my columns to your text(dot)xslm files. And it works perfectly.
Now how do I add this to my other workbooks.
Tried googling the procedure, and I exported the module, and tried importing it. didn't work.
and tried random stuff on my other workbooks. didn't work either.
unsure what keywords to google to get the answer. I've googled "how to save a custom excel function to my other workbooks" and tried those stuff. perhaps I'm doing the procedure wrong.
THANK YOU SO MUCH!!!! this helps me so immensely.
Apr 10 2022 10:26 AM
Apr 10 2022 11:09 AM
Apr 10 2022 12:14 PM
Apr 10 2022 12:42 PM
Apr 10 2022 12:47 PM
The names of the modules aren't really important, you can use anything you like, as long as the names are not the same as those of macros or custom functions.
Apr 11 2022 09:45 AM
Aug 17 2022 04:32 AM