how do i replace and remove character at the same time in excel

hi i have a bunch of product code that i wish to shorten and add some character all at the same time. would that be possible?

eg:
811C994/2082I
811C1000/CT220

i would like to remove the the first 3 character of the product code (i.e.811) and all character after "/" (inclusive of "/" as well)

eg:
811C994/2082I -> C994
811C1000/CT220 -> C1000

after which i would like to add a bunch of "0" after the first character to create a 7 character product code

eg:
C994 -> C000994
C1000 -> C001000

Please help. thanks!

Is this in one cell or two????

811C994/2082I 811C1000/CT220

Try:

=LEFT(MID(LEFT(A1,SEARCH("/",A1)-1),4,255),1)&TEXT(MID(MID(LEFT(A1,SEARCH("/",A1)-1),4,255),2,255),"000000")

Or.... (simpler):

=MID(A1,4,1)&TEXT(MID(MID(LEFT(A1,SEARCH("/",A1)-1),4,255),2,255),"000000")

Like this:

=MID(A1,4,1)&RIGHT("0000000"&MID(MID(LEFT(A1,SEARCH("/",A1)-1),4,255),2,255),6)

Maybe this:
=MID(A1,4,1)&RIGHT("0000000"&MID(MID(LEFT(A1,SEARCH("/",A1)-1),4,255),2,255),IF(ISNUMBER(--MID(A1,SEARCH("/",A1)-1,1)),6,7))

Here is a UDF
- see attached file

OldCodeNewCode.jpg

Put in Standard module

Please Login or Register  to view this content.

Not very pretty

Formula:

Please Login or Register  to view this content.

I started with Phuocam's elegant solution before the "B" complication

Formula:

Please Login or Register  to view this content.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Yep. Try this one:

=MID(A1,4,1)&LEFT(MID(A1,5,255),IF(OR(MID(MID(A1,5,255),7,1)={"B","C"}),7,6))