Remove / Subsitute characters.

[shizant]

Hello,

I need to replace various characters, that are located between two special characters.

Example:
Column A has the following:
Sports & Outdoors:Hunting:Knives -- I need to replace anything between the two ":". So, on this line I need to replace ":Hunting:" with ":". I need it to look like this -- Sports & Outdoors:Knives

The characters between the two ":" changes every few lines, so I can't just take "=TRIM(SUBSTITUTE(A1,":Hunting:",":"))" and run it all the way down.

I'd like to know if there is a formula that can be written to remove anything between the two ":" and replace it with just ":".

Thanks in advance...

 

[VoG]

Hi. Try

=SUBSTITUTE(A1,MID(A1,FIND(":",A1),FIND(":",SUBSTITUTE(A1,":","~",1))-FIND(":",A1)),"")

 

[shizant]

VoG -- You are the man!

THANK YOU!