SUBSTITUTE Function

Hi,

I have a cell with 3 characters in that can be either W, L, D or -. eg WLD, WWW, LLL WD- etc.

I wish to replace the W with an L and the L with a W, but I cannot work out how to do it.

I have tried SUBSTITUTE(E3,{"W";"L"},{"L";"W"}) which converts the W to L but not the other way around. Have I got the syntax correct or should I be using a different function.

Many thanks

Try it like this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E3,"L","z"),"W","L"),"z","W")

Note that L is changed temporarily to z.

Hope this helps.

Pete

Hi,

Try

Formula:

Please Login or Register  to view this content.

Hope this helps

Ah! I knew there had to be a simpler way Pete!

Or even simpler:

Use Find and replace

Find: W
Replace with: Lx
Find: L
Replace with: W
Find: Lx
Replece with: L

@Rambo: replacing W with Lx and then replacing L with W will result in every instance of Lx being replaced with Wx, which will not lead to the desired outcome

Originally Posted by ajryan88

@Rambo: replacing W with Lx and then replacing L with W will result in every instance of Lx being replaced with Wx, which will not lead to the desired outcome

Sorry, ajryan88 - you are right

Then try:

Use Find and replace

Find: W
Replace with: xx
Find: L
Replace with: W
Find: xx
Replace with: L

This is for an ongoing project with new data every day so although FIND and REPLACE would accomplish what I wanted the SUBSTITUTE example from Pete_UK was exactly what I wanted.

Many thanks to all.

Originally Posted by Onceageordie

This is for an ongoing project with new data every day so although FIND and REPLACE would accomplish what I wanted the SUBSTITUTE example from Pete_UK was exactly what I wanted.

Many thanks to all.

But then you must use a helper column. Isn't better better to use use Find and replace on the existing data?