Replace Character and Convert to Proper Case

[Barieq]

Hi,

Can anyone help me to solve to replace the old text with new. Below is the text for example. I have attached the file for more information.
Old Text- 749100- -Vehicle Running-25115:FUDDRUCKERS ANDALUS-2, CUTTK
Replace with - 749100- -Vehicle Running | 25115-Fuddruckers Andalus-2, Cuttk

Regards,
Barieq

[JohnTopley]

Try

=SUBSTITUTE(SUBSTITUTE(A2,"-"," | ",3),":","-")


EDIT: missed second substitution.

[JohnTopley]

=proper(substitute(substitute(a2,"-"," | ",3),":","-"))

[Barieq]

Thanks, Excellent. Could you please convert to proper only after this part | 25115-Fuddruckers Andalus-2, Cuttk

[JohnTopley]

Converting all to proper leaves the part before "| 25115-Fuddruckers Andalus-2, Cuttk" unchanged based on your sample.
.

[Barieq]

But when I Convert this text 192300- -EMA-25115:FUDDRUCKERS ANDALUS-2, CUTTK, it replaces into this 192300- -Ema | 25115- -Fuddruckers Andalus-2, Cuttk.

Actually, I don't want EMA to Ema

[JohnTopley]

I used 2 steps:

in B2

=SUBSTITUTE(SUBSTITUTE($A2,"-"," | ",3),":","-")

in C2

=LEFT($B2,FIND(" |",$B2))&PROPER(MID($B2,FIND(" |",$B2),255))

[Barieq]

Thanks, Is there any way to combine the formula to one

[Barieq]

Dear John,

Is it possible to get the result in macro

[JohnTopley]

A "monster" ....

=LEFT(SUBSTITUTE(SUBSTITUTE($A2,"-"," | ",3),":","-"),FIND(" |",SUBSTITUTE(SUBSTITUTE($A2,"-"," | ",3),":","-")))&PROPER(MID(SUBSTITUTE(SUBSTITUTE($A2,"-"," | ",3),":","-"),FIND(" |",SUBSTITUTE(SUBSTITUTE($A2,"-"," | ",3),":","-")),255))

[Barieq]

Thanks John for effort, you are right indeed it is a monster. I have posted the thread to VBA.

[JohnTopley]

Are there always 5 digit number before the colon ?

[Barieq]

yes, it is always 5 digit

[JohnTopley]

Try

=LEFT(A2,FIND(":",A2)-7)&PROPER(SUBSTITUTE(SUBSTITUTE(MID($A2,FIND(":",A2)-6,255),"-"," | ",1),":","-"))

EDIT: new formula

[Barieq]

Thanks, for your excellent formula.