Capturing letters and numbers in phrase density macro

**Glayva** · 11-11-2011, 08:30 AM

Hi all

I am running the code below to draw out the commonly used words and 2-word, 3-word (etc) phrases in one column of a large spreadsheet.

However, I have just realised that this ignores numbers so, for example '1st place' or '300 people' wouldn't appear in the resulting lists.

Can someone advise me what change I need to make to the code to ensure it captures numbers and alpha-numeric combinations.

Thanks

G

Sub Test()
PhraseDensity 1, "h"
PhraseDensity 2, "k"
PhraseDensity 3, "n"
PhraseDensity 4, "q"
PhraseDensity 5, "t"
End Sub

Sub PhraseDensity(nWds As Long, Col As Variant)
Dim astr() As String
Dim i As Long
Dim j As Long
Dim cell As Range
Dim sPair As String
Dim rOut As Range

With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each cell In Range("e1", Cells(Rows.Count, "e").End(xlUp))
astr = Split(Letters(cell.Value), " ")

For i = 0 To UBound(astr) - nWds + 1
sPair = vbNullString
For j = i To i + nWds - 1
sPair = sPair & astr(j) & " "
Next j
sPair = Left(sPair, Len(sPair) - 1)

If Not .exists(sPair) Then
.Add sPair, 1
Else
.Item(sPair) = .Item(sPair) + 1
End If
Next i
Next cell

Set rOut = Columns(Col).Resize(.Count, 2).Offset(1).Cells
rOut.EntireColumn.ClearContents

rOut.Columns(1).Value = Application.Transpose(.Keys)
rOut.Columns(2).Value = Application.Transpose(.Items)

rOut.Sort Key1:=rOut(1, 2), Order1:=xlDescending, _
Key2:=rOut(1, 1), Order1:=xlAscending, _
MatchCase:=False, Orientation:=xlTopToBottom, Header:=xlNo
rOut.EntireColumn.AutoFit
End With
End Sub

Function Letters(s As String) As String
Dim i As Long

For i = 1 To Len(s)
Select Case Mid(s, i, 1)
Case "A" To "Z", "a" To "z", "'"
Letters = Letters & Mid(s, i, 1)
Case Else
Letters = Letters & " "
End Select
Next i
Letters = WorksheetFunction.Trim(Letters)
End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

**snb** · 11-11-2011, 09:45 AM

Can you explain what the result should be ?
What do you mean by 2-word phrases ?

Please Login or Register  to view this content.

**Glayva** · 11-11-2011, 10:31 AM

Originally Posted by snb

Can you explain what the result should be ?
What do you mean by 2-word phrases ?

Sure. In my spreadsheet column E contains text that users have typed into a free text field on a website.

I use this macro to create 2-column tables (starting in columns h, k, n, q, t) showing individual words (column h) or 2,3,4 and 5-word phrases (k onwards) ranked by the number of times the word / phrase has been used.

i.e. 'people' was entered 1623 times or 'older people' was entered 1292 times

At the moment though it is ignoring the numbers in this column so, for example, even though '300 people' features heavily the word 'people' comes up in resulting lists but '300' and the phrase '300 people' doesn't.

Similarly '1st' doesn't come up as this contains both numbers and letters.

Someone (cleverer than I) suggested that:

astr = Split(Letters(cell.Value), " ")

may be causing the code to ignore the 300's and the 1st's (and similar). They could be right, in which case I an trying to find out how to amend this to make sure the code picks up numbers too, or they could be wrong, in whcih case any advice on how else to fix this woudl be much appreciated.

Thanks

G