Longest series of consecutive 2 chars in a string

**Eric_25** · 08-23-2014, 04:07 AM

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!

**oeldere** · 08-23-2014, 04:09 AM

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

please also add the desired (expected) result.

**Eric_25** · 08-23-2014, 04:18 AM

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

**oeldere** · 08-23-2014, 04:49 AM

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).

**Eric_25** · 08-23-2014, 05:27 AM

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))))))}

**oeldere** · 08-23-2014, 05:39 AM

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?

**Glenn Kennedy** · 08-23-2014, 05:41 AM

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

**Glenn Kennedy** · 08-23-2014, 05:46 AM

Simplified version...

**Eric_25** · 08-23-2014, 06:10 AM

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

**Glenn Kennedy** · 08-23-2014, 06:23 AM

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.

Longest series of consecutive 2 chars in a string

LinkBack

Thread Tools

Rate This Thread

Display

Longest series of consecutive 2 chars in a string

Re: Longest series of consecutive 2 chars in a string

Re: Longest series of consecutive 2 chars in a string

Re: Longest series of consecutive 2 chars in a string

Re: Longest series of consecutive 2 chars in a string

Re: Longest series of consecutive 2 chars in a string

Re: Longest series of consecutive 2 chars in a string

Re: Longest series of consecutive 2 chars in a string

Re: Longest series of consecutive 2 chars in a string

Re: Longest series of consecutive 2 chars in a string

Thread Information

Users Browsing this Thread

Similar Threads

Displaying longest string in column

Count longest consecutive run of zeros

Length of the longest consecutive data series with values not null

Need help calculating the longest streak in a date series

Longest string in a column

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions