+ Reply to Thread
Results 1 to 10 of 10

Longest series of consecutive 2 chars in a string

  1. #1
    Registered User
    Join Date
    06-25-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    15

    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!
    Last edited by Eric_25; 08-23-2014 at 06:10 AM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    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.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    06-25-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    15

    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
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    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. #5
    Registered User
    Join Date
    06-25-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    15

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

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    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. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

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



  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Longest series of consecutive 2 chars in a string

    Simplified version...
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    06-25-2012
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    15

    Re: Longest series of consecutive 2 chars in a string

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

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    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: copy to clipboard
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Displaying longest string in column
    By lolgiggleman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-13-2014, 06:19 AM
  2. Count longest consecutive run of zeros
    By TheRobsterUK in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-17-2013, 08:02 AM
  3. Length of the longest consecutive data series with values not null
    By jacknobody in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-24-2013, 09:42 AM
  4. Need help calculating the longest streak in a date series
    By SeanMulholland in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-11-2010, 07:13 PM
  5. [SOLVED] Longest string in a column
    By Jeff Kantner in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-01-2005, 02:00 AM

Tags for this Thread

Bookmarks

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