Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts

    last occurence of a character in string (2003)

    I have a column with text. The text may have no colon, might have 1 colon separating other text, might have 2 separating blocks of text.

    Is there a non-vba formula that would give me the text if there are no colons, and the LAST block of text after the last colon?

    AAA would return AAA
    AAA:BBB would return BBB
    AAA:BBB:CCC would return CCC

    etc.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: last occurence of a character in string (2003)

    Let's say the text values are in A1 and down.
    In B1, enter the following array formula (confirm with Ctrl+Shift+Enter):
    <code>
    =MID(A1,MAX((MID(A1,ROW($1:$25),1)=":")*ROW($1:$25 ))+1,25)
    </code>
    The number 25 is just a number larger than the length of the longest text string; you can change it if needed.
    Fill down the formula as far as necessary.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: last occurence of a character in string (2003)

    A non-array formula :

    =TRIM(RIGHT(SUBSTITUTE(A1,":",REPT(" ",99)),99))

    Regards
    Bosco

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last occurence of a character in string (2003)

    I'm quite possibly missing something really obvious here.... But would =Right(A1,3) not suffice? Unless the text is not 3 characlers long.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: last occurence of a character in string (2003)

    I assumed that the parts would be of variable length, and that the AAA, AAA:BBB etc. were just examples.

  6. #6
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last occurence of a character in string (2003)

    And of course, your assumption is most probably correct.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Re: last occurence of a character in string (2003)

    Yes, Hans' assumption was correct and I should have, for clarity, provided a sample like AAA:BB:CCCC . Thanks for the solutions.

Posting Permissions

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