Decomposing Text Strings

[DoctorZoom]

Hi folks,

I'm looking to search a specific field in from our trouble tickets - which is a free form text field entered by our agents and is always unique since they are writing in their own words what the customer is reporting.

I have the ability to break these tickets into high level categories but there are still thousands of tickets per day per category.

When trying to determine why we have a spike in tickets or when trying to do a deeper dive into a specific category, I need to be able to find the most common text strings within the field.

I was working with Fishboy but we only got so far.

http://www.mrexcel.com/forum/excel-...t-common-words-phrases-field.html#post4402083

He pointed out some code at:
Find/Count Most Common Words & Phrases In List

But my issue is similar to the 2nd post there that was never answered:

How do I break down the row into larger chunks than just the individual words?

For example, if my row was A B C - I want to return:

A - 1
B - 1
C - 1
A B - 1
B C - 1
A B C - 1

(AC could work but if that is too complicated, then just words that are consecutive)

Or if the row was "The Small Red Fox" - I'm looking to return the following 10 rows:

The
Small
Red
Fox
The Small
Small Red
Red Fox
The Small Red
Small Red Fox
The Small Red Fox

This would help tremendously in finding the most common customer problems from the free form text field entered by our agents.

Ideally a solution that doesn't use VBA would be best but if I need to use, that will be fine but I need to take what exists in the post (in link) to do this further step of breaking out "phrases"

Thanks for your help!
John

 

[Peter_SSs]

Still haven't solved the problem.

Please go to the site I provided and put a paragraph or two of text (from anywhere) and you will see the output. That is the exact output I am looking for but I need to do this in Excel and for thousands of cells.

Thanks!

I did go to that site and test what it did. I don't think trying to reproduce something like that falls into the realm of what you might expect from a free public forum like this. At least not for me.

 

[DoctorZoom]

I did go to that site and test what it did. I don't think trying to reproduce something like that falls into the realm of what you might expect from a free public forum like this. At least not for me.

ok - thanks Peter!