Nested IF, AND, and OR statements in one Excel formula

user_19210208 0
Viewed 85

I'm taking a cell that people can enter data into, and trying to interpret it in several ways. They can enter a code with between 7 and 20 characters. Depending upon the length, and the leading character, I want it to strip out the excess data and leave just a serial #. The different possibilities of data that people can enter are as follows:

7 character serial #
8 character serial #
8 character serial # with leading L
9 character serial # with leading D or L
10 character serial #
11 character serial # with leading M
12 character serial #
13 character serial # with leading M
20 character serial # with 12 leading useless characters

I've managed to create multiple IF statements that individually can identify each of these, but I'm trying to combine them into a single formula so that one cell shows JUST a valid serial #, after editing the entered data from a separate cell. I've managed to mix a couple of them, but the more AND and/or OR statements I add, the more confusing, and more likely to break the formula gets.

Is there an easier/better way to do this?

As an addendum, thanks to Special Instance:

Picture of the spreadsheet example

I have removed the attempted formulae above, and recreated 4 formulae, posted below, which does each of the above necessary steps, but they're separate, and I need them together:

=IF(AND(LEN(A2)=8,(LEFT(A2,1)="L")), RIGHT(A2,7),A2)
=IF(AND(OR(LEFT(A2,1)="D",LEFT(A2,1)="L"),LEN(A2)=9),RIGHT(A2,8),A2)
=IF(OR(LEN(A2)=11,LEN(A2)=13),RIGHT(A2,LEN(A2)-1),A2)
=IF(NOT(LEN(A2)=20),A2,RIGHT(A2,8))
excel-formula
3 Answers

Making such a complicated formula will result in an unmaintainable mess. Getting it working will be hard, and if you ever later discover a bug, it will be very difficult to unpack your giant formula to solve it.

The programming patterns that gets you out of this is helper columns.

Create 13 columns to the right of your data, and in each, put a formula, just as you have, but make all of the elses "" -- you'll wind up with only one column with text at each row.

Now, for your 14th column, concatenate all of your 13 columns. You'll have all of your serial numbers now in the same column.

Finally, select the 13 helpers columns, right click, and hide them -- they are just helpers, and don't need to display regularly.

user_41511290

You need to nest the IF statements so that they sit like a string of pearls on a decision tree. You may want to map that out on paper first

if( Len(A1)=7,
   if(left(A1,1)="L",
      remove first character
      else use A1
     ) ,closing last IF
   if(Len(A1)=8,
      if(or(left(A1,1)="L",left(A1,1)="D",
         remove first character,
         else use A1
         ), closing last IF  
      if 10 characters
         if first character M
            remove first character
            else use A1
         close last if
         if 12 characters

and so on ... You will end up with many nested IF statements and a bunch of closing brackets at the end.

It may help to use Alt+enter to create new lines when you type the formula, so you can indent with spaces and see the structure better.

Edit: If you have Office 365, you can also use the IFs function, which has a simpler syntax for many conditions and does not require nesting. For the first condition that is met, the following action will be executed, so you need to take care to sort your conditions accordingly. First check for 7 characters AND a leading L, then check for 7 characters only. If you were to reverse these two, you would never get to the second result.

=IFS(
AND(LEN(A1)=7,LEFT(A1,1)="L"),"remove first",
LEN(A1=7),A1,
AND(LEN(A1)=8,OR(LEFT(A1,1)="L",LEFT(A1,1)="D")),"remove first",
LEN(A1)=8,A1,
etc…..)
user_4479700

The formula below consolidates your logic into 3 possibilities

=IF(SUM(N(LEN(A1)={7,10,12})),A1,
 IF(SUM(COUNTIF(A1,"L???????"),COUNTIF(A1,{"L","D"}&"????????"),COUNTIF(A1,"M??????????"&{"","??"})),REPLACE(A1,1,1,""),
 IF(SUM(N(LEN(A1)={8,20})),RIGHT(A1,8),"ERROR")))
  1. where the input entered is valid
  2. where the input requires the 1st character to be eliminated
  3. where only the last 8 characters of the input are valid

which, for the sample provided, yields the correct results: Screenshot illustrating proposed formula (due to the employment of array constants, this will have to be entered as an array formula)

but this answer is purely a function of your sample which, because serial numbers are usually alphanumeric in type, I don't think is realistic, e.g. the logic involves removing leading L, D or M characters, but your data indicate that these 3 same characters can occur in the reformatted data - if the reality is that such characters are never valid then the logic could be simplified by the use of a series of SUBSTITUTE() functions.

user_188247270
Related