# Longest series of consecutive 2 chars in a string

1. ## Longest series of consecutive 2 chars in a string

Hi,

I'm looking for a formula which returns the longest consecutive series of occurrences of 2 chars in a text string.

In the case I'm trying to count the longest consecutive number of nine-spares (9/) in a bowling game.

For example:

|7/|X|9/|9/|9-|9/|9/|9/|9/|X8/| longest series of 9/ would be 4.
|9/|9-|9/|9/|7/|9-|(8)/|X|7/|9-| longest series of 9/ would be 2.
|9/|(8)1|X|X|72|63|7/|8/|8/|9/9| longest series of 9/ would be 1.

Thanks a lot for any help!

2. ## Re: Longest series of consecutive 2 chars in a string

you get better help if you add an small excel file, without confidential information.

please also add the desired (expected) result.

3. ## Re: Longest series of consecutive 2 chars in a string

Added a quick example sheet.

Column A contains the data, column B the expected result (longest series of consecutive '9/'s).

I managed to find a formula for a single char, by 2 chars seems to be more of a challenge

4. ## Re: Longest series of consecutive 2 chars in a string

is there a special reason putting this in 1 cell?

maybe we can split the cell to columns (text to columns)

after that it will be something with frequency (Dutch Interval).

5. ## Re: Longest series of consecutive 2 chars in a string

The values are stored in one cell in the spreadsheet, therefore I need to base the formula on a single cell.

I have added another version of the excel sheet in which I have added 2 columns.
Column B is the sting without the separators.
Column C count the longest series of strikes (X) based on an array formula. This formula works, but is based on a single char (X) instead of 2 chars (9/).

Formula to count the longest series of strikes is:
{=MAX(FREQUENCY(IF(MID(B1,ROW(INDIRECT("1:"&LEN(B1)))*1,1)="X",ROW(INDIRECT("1:"&LEN(B1)))),IF(MID(B1,ROW(INDIRECT("1:"&LEN(B1)))*1,1)<>"X",ROW(INDIRECT("1:"&LEN(B1))))))}

6. ## Re: Longest series of consecutive 2 chars in a string

Maybe I can't help you enough with this one, but maybe you could explain to the forummembers why the result should be
4
2
1

This is based on what criteria?

7. ## Re: Longest series of consecutive 2 chars in a string

You've just solved it yourself... Substitute your string 9/ for any single digit e.g. |, and use your equation...

8. ## Re: Longest series of consecutive 2 chars in a string

Simplified version...

9. ## Re: Longest series of consecutive 2 chars in a string

Thanks a lot, that fixes it!
I guess I was over complicating things

10. ## Re: Longest series of consecutive 2 chars in a string

Thanks for that... but here's an even simpler solution from Ron Coderre:
Post 9 http://www.mrexcel.com/forum/excel-q...thin-cell.html

Formula:
`Please Login or Register  to view this content.`

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1