removing character within a cell using a formula in excel 2010

[Joseph Sacchetti]

Hello all,

Can anyone help create an excel formula that would remove the C: in the below path. Please keep in mind that "C" can be any letter. So the idea is to remove the : and any character before it.

Before: Test1C:\Users\name\Desktop

After: Test1\Users\name\Desktop

Thank you

 

[tomas]

Hi Joseph

note that formula works the way 6th and 7th character in string is replaced by empty string

 

[Joseph Sacchetti]

Hi Joseph

note that formula works the way 6th and 7th character in string is replaced by empty string

Hi Tomas,

Thank you for your help. I will like to take this a step further. I am going to attach an excel sheet that has more then one path which contains C:. Is there a way to modify that formula so it can be used on all rows without knowing where the C: is?

 

[bobhc]

Just use find and replace, find C: and replace with nothing, you do not need a formula, try it on one cell if your are happy then select replace all and you are done.

 

[tomas]

As Bobhs suggests you can use find replace, and if you have other letter than C you can use "?:".

anyways find the formula in the attachement

 

[Khalid NGO]

Hi Tomas,

Thank you for your help. I will like to take this a step further. I am going to attach an excel sheet that has more then one path which contains C:. Is there a way to modify that formula so it can be used on all rows without knowing where the C: is?

Hi Joseph,

Can you check this in B2:
=SUBSTITUTE(A2,MID(A2,FIND(":",A2)-1,1),"")
This will remove "C"

or this if you want to replace "C" with "D":
=SUBSTITUTE(A2,MID(A2,FIND(":",A2)-1,1),"D")

copy down.

Regards,

 

[Aby]

Edited Khalid NGO s formula to remove 2 letters from path
=SUBSTITUTE(A2,MID(A2,FIND(":",A2)-1,2),"")
pls check this