Return part of string AFTER LAST ":"
 

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

Return part of string AFTER LAST ":"
 

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

Return part of string AFTER LAST ":"
 

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

Return part of string AFTER LAST ":"
 

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