Hi there,

Following problem on which I could not find a solution ...


In cell A2 there could be a string as follows:

abcd:lalala:dedsfs:Sige

or it could be:

dsfsfs:hrthr:TomOgilvy

What I would like to return is the part of the string AFTER the last ":"
In my first example there are 3 ":" in muy second it are 2 of them.

But it could be any number... (at least1!)

Someone who can sort me this out?

Sigeeeeee

newText holds the resultant string you require

Dim myText, newText As String
Dim char As String
Dim i, counter, textLen As Integer

myText = ActiveCell.Value
textLen = Len(myText)

counter = textLen
Do Until char = ":"
char = Mid(myText, counter, 1)
counter = counter - 1
Loop

newText = Mid(myText, counter + 2, textLen - counter + 2)

Regards
Rowan

"SIGE" wrote:

Hi there,

Following problem on which I could not find a solution ...


In cell A2 there could be a string as follows:

abcd:lalala:dedsfs:Sige

or it could be:

dsfsfs:hrthr:TomOgilvy

What I would like to return is the part of the string AFTER the last ":"
In my first example there are 3 ":" in muy second it are 2 of them.

But it could be any number... (at least1!)

Someone who can sort me this out?

Sigeeeeee

Assuming that there is at least one : in the string, you can use:

a="dsfsfs:hrthr:TomOgilvy"
debug.print mid(a,1+instrrev(a,":"))


"SIGE" wrote:

Hi there,

Following problem on which I could not find a solution ...


In cell A2 there could be a string as follows:

abcd:lalala:dedsfs:Sige

or it could be:

dsfsfs:hrthr:TomOgilvy

What I would like to return is the part of the string AFTER the last ":"
In my first example there are 3 ":" in muy second it are 2 of them.

But it could be any number... (at least1!)

Someone who can sort me this out?

Sigeeeeee

Here is a way with just worksheet functions

=MID(A2,FIND("~",SUBSTITUTE(A2,":","~",LEN(A2)-LEN(SUBSTITUTE(A2,":",""))))+
1,255)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SIGE" wrote in message
om...
Hi there,

Following problem on which I could not find a solution ...


In cell A2 there could be a string as follows:

abcd:lalala:dedsfs:Sige

or it could be:

dsfsfs:hrthr:TomOgilvy

What I would like to return is the part of the string AFTER the last ":"
In my first example there are 3 ":" in muy second it are 2 of them.

But it could be any number... (at least1!)

Someone who can sort me this out?

Sigeeeeee