Count Unknown Text Strings

[carguy86]

Not sure if I am posting in the right place here...but...

I have a database that reads an imported Excel file which comes from our main management system...the issue I am having is I have a "Removal Box" basically for tracking exceptions...the Database inserts the item number, what store, and the reason for removing it from the list into this field, literally hundereds of lines.

Because we have different people inputting the reasons for this, there is no telling how they will spell it, if there will be spaces, or anything...

The format is:

12345 - Store Name - Reason

Again, any of those values could be any number of characters and will almost never be the same.

None the less what I am after is to be able to count each occurrence of each reason. I know I can't use the "Case" function since the reason is unknown...

I'm thinking maybe there is a way to copy everything from the right up until that first "-" mark, regardless of any spaces or slash marks that may be present, and then turn around and count any other occurrences of it...

Hopefully that makes sense, and hopefully there is a cure, this is driving me crazy!

[David Jondreau]

If there's literally NO consistency, you might be beyond hope. But if there's something, for example, there first two dashes always separates line item and store from the reason, then it's pretty simple.

You can use the functions Position() and Middle() to parse out the reason. Counting reasons is a little more complicated. I use a calc field to capture the reason, sort by that field, then a summary field to count.

[carguy86]

I will try that, sounds like it should work, thanks for the suggestion!

[LaRetta]

For breaking out the reason, an easy calculation (result is text) could be:

Trim ( GetValue ( Substitute ( textfield ; "-" ; ¶ ) ; 3 ) )

Then generate your grouped report as DJ indicates or, if you want the count to display dynamically (and not run a report each time you want to see it), you can use a relationship. We'd need to know how and when you want the totals displayed. :wink2:

[David Jondreau]

For breaking out the reason, an easy calculation (result is text) could be:

Trim ( GetValue ( Substitute ( textfield ; "-" ; ¶ ) ; 3 ) )

I like it, it's eleganter than mine.