Does Excel or some AI plug in have a "specificity index"? (no, not about medicine)

[Willowdale]

Greetings listers.

I have a huge (350K lines) database of dates, methods, positions, comments, results, and at last, a field of comments, some are short like " left package" "left message about package" etc, and some go into detail assigning the details surrounding the delivery of the package.

Is there a way to identify, assign or sort the records by relative specificity of these comments? In other words,
User A = I left the package.
User B = I left the package on the porch.
User C = I left the package on the southeast corner of the porch with the label facing the door.

You get the Idea. Earlier I used the len function to record raw number of characters in each comment. I guess what I'm looking for is a way to suss out the variability, the uniqueness of each comment to in some way put a metric to how specific individual user comments are.

Any help would be, well, super helpful. Oh, and yeah, my analyze tab is missing. My IT dept has it removed. Not sure if I could get it added, but Id sure ask if that would lead to a solution.

 

[Eric W]

You could just count the number of words in the description.

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

This counts the spaces in it. More words = more specific?

 

[Willowdale]

That is a possible solution, but the ideal is to be brief but detailed. So although I might have the highest word count, it might still be bad. To long=inefficient.

 

[Eric W]

If you're not looking for total length, or number of words, then you're going to have to look at the actual content. You could create a list of words that could be used to describe the delivery. The more found, the better the score. For example:

Book1 (version 1).xlsb
	A	B	C	D	E
1		Score1	Score2	Words	Prepositions
2	I left the package.	1	0	package	above
3	I left the package on the porch.	2	1	porch	across
4	I left the package on the southeast corner of the porch with the label facing the door.	4	3	north	against
5				south	along
6				east	among
7				west	around
8				southeast	at
9				southwest	before
10				northeast	behind
11				northwest	below
12				label	beneath
13					beside
14					between
15					by
16					down
17					from
18					in
19					into
20					near
21					of
22					off
23					on
24					to
25					toward
26					under
27					upon
28					with
29					within
30
Sheet14

Cell Formulas
Range	Formula
B2:C4	B2	=SUMPRODUCT(--ISNUMBER(SEARCH(" "&D$2:D$29&" "," "&SUBSTITUTE(TRIM($A2),".","")&" ")))

Here I used two different lists. The first has specific words that might help, but it is limited to just what you can think of. The second list contains prepositions. Prepositional phrases are used to describe things, so the more, the better, and you don't need to worry about specific nouns, like stairs, screendoor, gazebo, etc. Either way, you'll probably have to experiment a bit.

 

[Willowdale]

Gotcha...You may be on to something there......I'll be back after work.....