Obtain data from a cell with lots of paragraphs

[Sandeeep]

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.

g'day

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.

[#2]

@Sandeeep 

I'll give you 1. and 2.

[#3]

TYSM, the text.xlsx file has the exact similar kind of data I have, (mine is 500-100 everyday) and I got like 9 such per day.

I had the solution for 1. I'm so thankful for the Frequency thing. It's exactly what I needed.

ty ty ty ty

Any ideas for 3? I've been googling for 2weeks with random stuff, but none seem to be exactly what I need. Would love for getting a formula for that!

I can do 4. record macro myself, so that's fine. Hoping someone can solve 3. for me

[#4]

@Sandeeep 

See the attached version, now a macro-enabled workbook.

[#5]

@HansVogelaar 

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!

code

Option Explicit

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))
End If
Next varLine
ExtractParts = Join(strReturn, vbLf)
End Function

---

[#6 · Accepted solution]

@Sandeeep 

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?

[#7]

@HansVogelaar 

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.

[#8]

Please help.
Update, I somehow got it to work on the other workbook. but now all my old macros vanished and idky

[#9]

Update, I got my old macros to work.
But on your file now it shows Ambiguous name detected: ExtractParts

[#10]

Update, so I copied it to my PERSONAL.XLSB but whenever I open a new csv file or xlsx file.
The function does not work, and throws a #NAME? issue.

Anything I need to do so this function =ExtractParts(transcriptcell,absolute_cell_Value_for_keyword) works on my csv & xslx files?

[#11]

Update:
OMG, I figured out what you told me to do. finally!
from https://support.microsoft.com/en-us/office/create-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-...

Now I have a new issue.
So I like things organized, and renamed the modules from module1,2,3,4,5 to understandable ones.
click on module > view > properties windows > rename on the bottom left side.

This, kinda made all the shortcuts for my macros break.
Basically, An exact problem this guy had -> https://stackoverflow.com/questions/59105782/how-can-i-make-my-macro-shortcut-key-work-again

kay, so I renamed it all with an extra "s", so its FormatTranscriptss, CleanTranscriptss, ExtractPartss
It's all working now.

but like, I can't get its name back to without the additional "s", and my OCD hurts.
Any solutions?
If I try, I get "This will reset your project. Shall we Proceed?"
Saying "confirm" again breaks it back to PERSONAL.XLSB!FormatTranscripts.FormatTranscripts
Which is broken, as shown in the URL link above. Sigh.

Please advise

[#12]

@Sandeeep 

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.

[#13]

Oooh, so that's why I got the errors and it broke. okok.
I now use an underscore in module names now.

instead of FormatData for both module & macro names. I've changed to,
module-name: Format_Data
macro name: FormatData
shortcut: Cntrl + Shift + F

It's all fixed now. Thank you so much!

[#14]

Hi again,
I might need your help again.
https://techcommunity.microsoft.com/t5/excel/vba-text-based-help-pseudo-code-provided-hard-difficult...

Posting it here, if others ever see this post, they'll find another related question to a similar scenario.