mid substitute formula

I have data coes that need to be converted, basically need to remove 1st and 12th digits, 12th digit only, or 11th digit. I have built spreadsheet with a mid sub formula to do all 3 separately, but cannot figure out how to combine the formula to do all three.

I am attaching the spreadsheet, let me know if you can help, thanks.

I'm a little confused....

If you combine the three formulas then how do you determine what action to take? Can it be based on what's in column C...or should it be based on something else?

Where 11th or 12th digit is dropped is that always the last digit?

Why are you using SUBSTITUTE to remove dashes from the data? None of your examples contain any dashes?

The dashes are from a previous project that I used this formula, that previous project had "-" and in this project having the "-" didn't hurt or help so I left it in.

The formula has to be based on column C.

The 11th and 12th digit are always the last digit.

Let me know if this additional info helps.

Try this formula in row 2 copied down

=IF(OR(C2={"drop digit 12","drop digit 11"}),LEFT(A2,LEN(A2)-1),IF(C2="drop digit 1 & drop digit 12",MID(A2,2,10),""))

That works, thanks much.

Oopss, the formula worked but then I realized you based it on the corrective action, which I took the time to key in for the example, I have 13,000 incorrect codes, this is a sample of all of them, typing in corrective action for all would not be an option.

Realizing you ask me if column c was the basis I said yes, but did not realize it would be inclusive of the formula, HELP!!!!

OK then, assuming column C isn't there then how do you want to determine which digits are removed? For your example the result looks like column B but I presume you don't have column B either?

If there are only three possibilities you show then you can start, presumably, by removing the last character from all codes, but which of the 12 character codes also have the first character removed?

If you can supply the logic for this then I'm sure a formula can be built.

I do not have column B either, I had to manually look up each correct code, just fo the example, the examples I chose start with unique 1st 4 digit codes, for example, there may be a code I want to remove 12th digit as follows:

879011111111
879022222222

So what if in the formula you insert the "8790" drop last digit, and insert the unique 4 digits of the other 2 actions as well.

If you can figure out this way, you can enter in one of the unique 4 digits and I can compete formula by adding remaining, what do you think?

OK, so assuming that all codes beginning with 8790 are 12 digit codes that need the last letter removed, all codes beginning 8888 are 11 digit codes which need the last letter removed and those beginning 1234 need 1st and 12th digit (of 12) removing then you could use this formula

=IF(LEFT(A2,4)="8790",MID(A2,1,11),IF(LEFT(A2,4)="8888",MID(A2,1,10),IF(LEFT(A2,4)="1234",MID(A2,2,10),"check code")))

To extend this you only really need two categories

Cat 1 - remove last digit
Cat 2 - remove first and last digit

If you list all possible 4 digit codes in Y2:Y100 and then in Z2:Z100 the relevant category number (1 or 2) then you could use this formula

=IF(ISNA(MATCH(LEFT(A2,4)+0,Y$2:Y$100,0)),"check code",MID(LEFT(A2,LEN(A2)-1),VLOOKUP(LEFT(A2,4)+0,Y$2:Z$100,2,0),99))

Note that the 4 digit code is always assumed to be the first 4 digits, even when first digit needs to be removed, I'm not sure if that works for you, might you need to look at digits 2 to 5?

=IF(LEFT(A3,4)="8790",MID(A3,1,11),IF(LEFT(A3,4)="8888",MID(A3,1,10),IF(LEFT(A3,4)=("1819","1844"),MID(A3,2,10),"check code"))

I believe this will work, I am trying to add the 4 digits in their perspective slots and cannot seem to get it to work, am I missing a ) or , ?

If you want to include more than one number for any condition then you need an OR like this

=IF(LEFT(A3,4)="8790",MID(A3,1,11),IF(LEFT(A3,4)="8888",MID(A3,1,10),IF(OR(LEFT(A3,4)={"1819","1844"}),MID(A3,2,10),"check code")))

Ok, thanks that works and I actually like creating a table on the side to reference and then just add the different lead numbers there, thanks for making this work.