Remove characters in cells that "may" contain

[mmcoordes]

Hello again excel experts,

I have another request, I have a column that receives data from another source. Some of the data includes "wp05" in the beginning, so I need that deleted in cells located within a column. Now other cell use this cell as a reference in a index and match function. I'm using =right(e3,Len (e3)-5)

WP05 201302500
WP05 201309700
201304600
201307700
WP05 20138800
201302212

If I use this formula in all cells I get;

201309700
4600

What would the formula be not to remove 5 characters in a cell that didn't have "WP05"

[ChemistB]

Try
=TRIM(SUBSTITUTE(A1,"WP05",""))
Does that work for you?
IF you want it to be recognized as a number (if they will all be numbers when done)
=TRIM(SUBSTITUTE(A1,"WP05",""))+0

[martindwilson]

jusy fyi =if(left(e3,5)="wpo5 ",right(e3,Len (e3)-5),e3)
the substitute() version is better

[mmcoordes]

Thanks a lot you guys that works great.

What if? The "WP05 012675" was actually a result of a formula? I tried your formulas nothing happened.

[ChemistB]

Shouldn't matter if it's the result of a formula. Can you upload an example? (Go Advanced > Manage Attachments)

[martindwilson]

I'd guess
=TRIM(SUBSTITUTE(your formula,"WP05",""))

[newdoverman]

I copied the data and tried the formulae and they worked perfectly. Perhaps in the WP05 the 0 (zero) might be an O (letter).