# Thread: last occurence of a character in string (2003)

1. ## 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. ## 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. ## Re: last occurence of a character in string (2003)

A non-array formula :

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

Regards
Bosco

4. ## 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. ## 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. ## Re: last occurence of a character in string (2003)

And of course, your assumption is most probably correct.

7. ## 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
•