Remove character from excel cell but ignore some words

[kradclayton]

Hi!
I am wondering if there is a way to build upon the function in excel where you can remove a certain character from cells, but ignore a word that has that character.

The situation is that for a company i database for, they have to use colourways in a specific format in one part of the database but different in another.
Example - greens|blues|greys
In the second part of the databasing we have to remove the "s" from the colours. This is simple with the following formula - =SUBSTITUTE(B3,"s","")
This would leave me with green|blue|grey which is what i need.

However, my dilemma comes to when we name a colourway "sand" - ie sands|greens

If i use the above formula it would return and|green because it removes the s.

Can you use a if statement to stop it removing the s from sands?

Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
Will all the colours always have an s on the end?

 

[kradclayton]

Yes all colours will have an s on the end to begin with

 

[Fluff]

Ok, how about

=LEFT(SUBSTITUTE(B3&"|","s|","|"),LEN(SUBSTITUTE(B3&"|","s|","|"))-1)

 

[kradclayton]

Perfect!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[kradclayton]

On a similar note - would I be able to use a formula to remove / from a cell and change it to a ,
Example - Grass / Silk / Mineral

Needs to become - Grass, Silk, Mineral

Thanks!

 

[Fluff]

How about

=SUBSTITUTE(A2,"/",",")

 

[kradclayton]

amazing! thank you so much

 

[Fluff]

My pleasure.