Remove text and then trim remainder from excel cell

[itsmythg]

I have a file with cells containing 9932/0009 i need to convert this to 320009 so what needs to happen is remove the "/" then remove first 2 characters (99) leaving me 320009. I figured out =SUBSTITUTE(D3,"/","") (with the help of this site) and to use =RIGHT(G4,(6)) to get the right results but i was hoping to combine into one step to simplify process.

Thanks for your help.

 

[Scott Huish]

=right(substitute(d3,"/",""),6)+0

 

[itsmythg]

Thanks for your help Scott!!!!!!!!!!!!

 

[itsmythg]

One more question it is removing the 00 i need the field to maintain the 6 characters because the data i'm validating has the 00 and it fails the query if it doesn't.

 

[Scott Huish]

It shouldn't be removing the 00 from the sample you gave because it's in the middle. I'm assuming your talking about a different example. Does this fix your problem?

=TEXT(RIGHT(SUBSTITUTE(D3,"/",""),6),"000000")

 

[itsmythg]

yes thanks

Roxanne

 

[Rick Rothstein]

It shouldn't be removing the 00 from the sample you gave because it's in the middle. I'm assuming your talking about a different example. Does this fix your problem?

=TEXT(RIGHT(SUBSTITUTE(D3,"/",""),6),"000000")

You should not need the TEXT function call... just removing the +0 from your original formula should be good enough...

=RIGHT(SUBSTITUTE(D3,"/",""),6)

Assuming there are always four digits in front of the slash, here is an alternate that should also work...

=REPLACE(A1,1,5,MID(A1,3,2))