Text right of : from a cell

[king10001]

I have some text that has near the end of this text "This is loads more text before this part and its not the same length Publisher: :01 First Second"

I need to take everything after : so 01 First Second

I have tried =RIGHT(F1,LEN(F1)-FIND("Publisher: :",F1))

With F1 being where the text is.

[Tony Valko]

Maybe this...

=MID(F1,FIND("^",SUBSTITUTE(F1,":","^",2))+1,999)

[Olly]

You could use this formula:

Please Login or Register  to view this content.

Or you could use a small UDF:

Please Login or Register  to view this content.

Which can be used as:

Please Login or Register  to view this content.

[Olly]

Maybe this...

=MID(F1,FIND("^",SUBSTITUTE(F1,":","^",2))+1,999)

Neat - but won't work if there are more (or less) than 2 colons in the original text.

[Tony Valko]

Neat - but won't work if there are more (or less) than 2 colons in the original text.

How many colons do you see in the provided sample?

I try to not account for things that may not exist. If there might be more semi-colons then the OP will post back and tell us.

[king10001]

=RIGHT(F1,LEN(F1)-FIND("|",SUBSTITUTE(F1,":","|",LEN(F1)-LEN(SUBSTITUTE(F1,":","")))))

worked fine

on another note how would i take all the text to the left from

"This text is what I want that is to the left of" Publisher:

Thanks guys

[Olly]

try:

Please Login or Register  to view this content.