How to count acronyms is text (i.e., number of times between 2 and 5 capital letters appear as consecutive characters)

[ea2146]

Hello,

Thank you in advance for any suggestions! I have a corpus of written comments and would like to identify the number of acronyms that appear in each cell of text. Of course, there is no perfect way to do this, but one idea I had is to create an excel function that counts the number of times between 2 and 5 capital letters appear as consecutive characters. I chose 5 as the high end of the range because very few acronyms are more than 5 letters long and a function that indiscriminately identifies consecutive capital letters would incorrectly identify text that is written in all caps as an acronym.

Below is an illustration of what I need. I have the sentences in the "TEXT" column and would like a function that produces the values in the other column.

Any ideas would be greatly appreciated!! Thank you so much for taking the time to help!

TEXT	NUMBER OF ACRONYMS
This sentence has no acronyms	0
I launched a word-of-mouth marketing (WOMM) campaign.	1
I have experience in BTC and BTB	2
This sentence has no acronyms	0
Neither does this one.	0
He worked at a PR agency	1
THIS SENTENCE IS ALL CAPS	0

 

[Scott Huish]

Here's a UDF that should do that:

Function COUNTACRO(r As String) As Long
With CreateObject("vbscript.regexp")
    .Pattern = "[A-Z]{2,5}"
    .Global = True
    If .test(r) And r <> UCase(r) Then COUNTACRO = .Execute(r).Count
End With
End Function

Book1
	A	B
1	TEXT	NUMBER OF ACRONYMS
2	This sentence has no acronyms	0
3	I launched a word-of-mouth marketing (WOMM) campaign.	1
4	I have experience in BTC and BTB	2
5	This sentence has no acronyms	0
6	Neither does this one.	0
7	He worked at a PR agency	1
8	THIS SENTENCE IS ALL CAPS	0
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=countacro(A2)

 

[VBE313]

Here's a UDF that should do that:

Function COUNTACRO(r As String) As Long
With CreateObject("vbscript.regexp")
    .Pattern = "[A-Z]{2,5}"
    .Global = True
    If .test(r) And r <> UCase(r) Then COUNTACRO = .Execute(r).Count
End With
End Function

Book1
	A	B
1	TEXT	NUMBER OF ACRONYMS
2	This sentence has no acronyms	0
3	I launched a word-of-mouth marketing (WOMM) campaign.	1
4	I have experience in BTC and BTB	2
5	This sentence has no acronyms	0
6	Neither does this one.	0
7	He worked at a PR agency	1
8	THIS SENTENCE IS ALL CAPS	0
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=countacro(A2)

This is great, can you kindly explain the logic behind this code? I have never seen CreateObject("vbscript.regexp") before.

 

[FryGirl]

This will explain it nicely.

VBA RegEx | How to Use Excel VBA RegEx with examples?

Guide to VBA RegEx. Here we discussed on how to use Excel VBA Regular Expression with practical examples and downloadable excel template.

www.educba.com

 

[ea2146]

This works perfectly!! I can't thank you enough. Much appreciated! I also appreciate the discussion of the inner workings for the UDF.