Trim a Value

[Always Learning]

Hi Guys,

Can you help me please.

If the value in Cell A1 = LOC:OCTWM:KIDS
How do I split from the second : (Colon) so that my value is now KIDS

I can do it if there was only one colon using search but I can not for the
life of me find how to Right Trim from the second colon.

Your Help is appreciated.

Best Regards,

Steve Wilson.

 

[Niek Otten]

Hi Steve,

=RIGHT(A1,LEN(A1)-FIND("^",SUBSTITUTE(A1,":","^",2)))

 

[Always Learning]

Hi Niek,

That's fantastic. It works perfectly.
Thanks for such a quick reply.

In my never ending quest to learn would you mind explaining how it works
(Why it works)

Your help is greatly appreciated.

Best Regards,

Steve Wilson.

 

[daddylonglegs]

Here's another way

=REPLACE(A1,1,FIND(":",A1,FIND(":",A1)+1),""

 

[Niek Otten]

Hi Steve,

The substitute function replaces the second instance of ; with ^. The find
then locates the position of that ^. Len is the total length (in characters)
of A1; subtract the position of the ^ and you know how many characters from
the right side of A1 you need.

 

[Always Learning]

Hi Guys,

Thanks for your help. The solutions work great.
One thing I am having a problem with though is that some values only have 1
: (colon) and some have 2.
Is there a way that I can check if there is 1 or 2 : (colons) and then use
the appropriate solution. Like,
If 1
=RIGHT(A1,LEN(A1)-FIND("^",SUBSTITUTE(A1,":","^",1)))
If 2
=RIGHT(A1,LEN(A1)-FIND("^",SUBSTITUTE(A1,":","^",2)))

Why is there always a little bit extra.

Thanks for your help & time.

Best Regards,

Steve Wilson.

 

[Niek Otten]

Just to add a bit to what you've learned already and make you do it
yourself:

If you need the nth occurrence of a character from the right (in your case,
the first one from the right):
use substitute to replace all : characters with nothing and determine the
length of that string with LEN. Subtract that length from the original
length and you know how many there are. Now you can easily locate the nth
from the right.
Post again if you can't get it done.

 

[daddylonglegs]

Here's another method to give you the (trimmed) text to the right of the
final ":", however many there are

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

 

[Always Learning]

Hi Niek,
I like your style. The only way to learn is to actually think about it and
code it.

Thanks for all your help your a star.

Best regards,

Steve Wilson.

 

[Always Learning]

Hi there,

Thanks for the reply.

Great solution.

Thanks for your help, I appreciate it.

Best Regards,

Steve Wilson.

"daddylonglegs" <[email protected]>
wrote in message
news:[email protected]...