?
Solved

If Expression in Excel

Posted on 2012-08-17
24
Medium Priority
?
722 Views
Last Modified: 2012-08-20
Can any one please help me understand this below Expression.. especially the IF part

=RIGHT(G4,LEN(G4)-IF(OR(MID(G4,1,1)="0",MID(G4,1,1)="9"),
IF(OR(MID(G4,2,1)="0",MID(G4,2,1)="1"),
IF(OR(MID(G4,3,1)="0",AND(MID(G4,3,1)="1",MID(G4,1,3)<>"001"),MID(G4,1,3)="900"),
IF(OR(MID(G4,4,1)="0",MID(G4,1,4)="9011",AND(MID(G4,1,5)="00021",LEFT(R4,2)="BR")),
IF(OR(MID(G4,5,1)="0",AND(MID(G4,1,5)="00021",LEFT(R4,2)="BR")),
IF(MID(G4,6,1)="0",6,5),4),3),2),1),0))

As i understand
1st IF - looking to see if 1st character is 0 or 9 then remove 1st character from the string
2nd IF - if 2nd charcter is 0 or 1 Then remove 1st 2 characters from the string
3rd IF - if 3rd char is 0 or (3rd char is 1 and 1st 3 characters = 001) or 1st 3 chars = 900 then remove 1st 3 chars from the string

Ex: 00039019746598 should result in 39019746598

Is that right? appreciate your help
0
Comment
Question by:vdr1620
  • 12
  • 6
  • 3
  • +1
24 Comments
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38305276
sure this is a huge text manipulation string.  


=RIGHT(G4,LEN(G4)-IF(OR(MID(G4,1,1)="0",MID(G4,1,1)="9"),
IF(OR(MID(G4,2,1)="0",MID(G4,2,1)="1"),
IF(OR(MID(G4,3,1)="0",AND(MID(G4,3,1)="1",MID(G4,1,3)<>"001"),MID(G4,1,3)="900"),
IF(OR(MID(G4,4,1)="0",MID(G4,1,4)="9011",AND(MID(G4,1,5)="00021",LEFT(R4,2)="BR")),
IF(OR(MID(G4,5,1)="0",AND(MID(G4,1,5)="00021",LEFT(R4,2)="BR")),
IF(MID(G4,6,1)="0",6,5),4),3),2),1),0))

instead of breaking it all out

right(text to look at, number of characters to take)
left(text to look at, number of characters to take)
mid(text to look at, first character to look at counting from left hand side, number of characters to take)

len(text to look at)  gives the length in characters of this text.

and(x,y,z)  is the same as   -  x and y and z      in normal programming.  all of them must be true or false is returned.

or(x,y,z) is the same as - x or y or z   in normal programming.  If any of them are true, the entire set is true.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38305298
For if commands it's if(condidtion, value if true, value if false)  - if value if false is omitted a 0 is returned.

Lets break it down:

we'll start at the lowest level and work back.

IF(MID(G4,6,1)="0",6,5)


if G4 character 6 = 0 then the value is 6 if not the value is 5.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38305323
IF(OR(MID(G4,5,1)="0",AND(MID(G4,1,5)="00021",LEFT(R4,2)="BR")),
see above ,4)

Now lets look at this one:

If G4 character 5 = 0 or ( G4 Character 1-5 = 00021 and the left 2 characters of cell R4 = BR) then see above otherwise the value is 4.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38305336
IF(OR(MID(G4,4,1)="0",MID(G4,1,4)="9011",AND(MID(G4,1,5)="00021",LEFT(R4,2)="BR")),
see above ,3)

IF G4 character 4 =0 or g4 char 1-4 = 9011 or (G4 char 1-5 = 00021 and left two chars of R4 = BR)  then see above, otherwise the value is 3.
0
 
LVL 16

Author Comment

by:vdr1620
ID: 38305344
Thank you.. i do understand the syntax but i am basically trying to convert this to SQL or SSIS Expression which is resulting in wrong values compared to excel .. so just wanted guidance in understanding the expression

IF(OR(MID(G4,3,1)="0",AND(MID(G4,3,1)="1",MID(G4,1,3)<>"001"),MID(G4,1,3)="900")

3rd IF - if 3rd char is 0 or (3rd char is 1 and 1st 3 characters not equal to 001) or 1st 3 chars = 900 then remove 1st 3 chars from the string

is the above statement correct?
0
 
LVL 11

Assisted Solution

by:ScriptAddict
ScriptAddict earned 300 total points
ID: 38305350
IF(OR(MID(G4,3,1)="0",AND(MID(G4,3,1)="1",MID(G4,1,3)<>"001"),MID(G4,1,3)="900"),
see above ,2)

if G4 character 3 = 0 or ( G4 character 3 = 1 and G4 chars 1 to 3 don't equal 001) or G4 character 1 to 3 equals 900 then see above otherwise the value is 2
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38305358
IF(OR(MID(G4,2,1)="0",MID(G4,2,1)="1"), see above ,1)

if G4 character 2 =0 or G4 Character 2 = 1 see above, otherwise the value is 1
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38305367
IF(OR(MID(G4,1,1)="0",MID(G4,1,1)="9"),see above,0)

if G4 char 1 = 0 or g4 char 1 = 9 then see above otherwise the value is 0.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38305377
RIGHT(G4,LEN(G4)-see above)

Take the text in G4, and take x characters starting from the right side.

 len (g4)  this is the number of characters total in the cell G4

and then the massive equation above tells you how many characters to reduce that by.

Hope that helps if you have any other questions just post.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38305388
Yup,

Sorry, didn't see the question until the end.  But you got it.
0
 
LVL 10

Accepted Solution

by:
mark_harris231 earned 750 total points
ID: 38305394
Ultimately, the formula is attempting to determine how much to "cut off" the left of string, G4 based on a number of conditions.  The following provides a decision tree to see the effect at each IF statement on the "root" formula: =RIGHT(G4, <length of G4 - some number>).  Each level of the decision tree is letter coded (e.g., A:, B:, C:, etc.):


IF 1st postion is "0" OR "9",
  A: TRUE: IF 2nd position is "0" OR "1"
    B: TRUE: IF 3rd postion is "0" OR [3rd position is "1" AND 1st-3rd positions is NOT "001"] OR 1st-3rd postions is"900"
      C: TRUE: IF 4th postion is "0" OR 1st-4th postions is "9011" OR [1st-5th postions is "00021" AND left 2 characters of R4 is "BR"]
        D: TRUE:IF 5th postion is "0" OR [1st-5th position is "00021 AND left 2 characters of R4 is "BR"]
          E: TRUE: If 6th position is "0"
            F: TRUE: =RIGHT(G4,LEN(G4)-6
            F: FALSE: =RIGHT(G4,LEN(G4)-5
          E: FALSE: =RIGHT(G4,LEN(G4)-4
        D: FALSE: =RIGHT(G4,LEN(G4)-3
      C: FALSE: =RIGHT(G4,LEN(G4)-2
    B: FALSE: =RIGHT(G4,LEN(G4)-1
  A: FALSE: =RIGHT(G4,LEN(G4)-0
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 38305404
Oops...was working on the decision tree and didn't see ScriptAddicts entries until I posted.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38305421
Hey I read your comment a little closer.  Your oversimplfying it.  

if that level is true, you could be removing from 6-3 characters from the front.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38305425
Man that's a nice tree!  He'll likely find that helpful
0
 
LVL 16

Author Comment

by:vdr1620
ID: 38305432
Thanks Mark..

    F: FALSE: =RIGHT(G4,LEN(G4)-5
          E: FALSE: =RIGHT(G4,LEN(G4)-4
        D: FALSE: =RIGHT(G4,LEN(G4)-3
      C: FALSE: =RIGHT(G4,LEN(G4)-2
    B: FALSE: =RIGHT(G4,LEN(G4)-1
  A: FALSE: =RIGHT(G4,LEN(G4)-0

I am little confused with the above ...

What would A - F result in if they are true ? Is the below conversion right?
F: TRUE: =RIGHT(G4,LEN(G4)-6
          E: TRUE: =RIGHT(G4,LEN(G4)-5
        D: TRUE: =RIGHT(G4,LEN(G4)-4
      C: TRUE: =RIGHT(G4,LEN(G4)-3
    B: TRUE: =RIGHT(G4,LEN(G4)-2
  A: TRUE: =RIGHT(G4,LEN(G4)-1
0
 
LVL 16

Author Comment

by:vdr1620
ID: 38305455
basically i am trying to convert it to a case statement based on what happens when the condition is true
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 38305491
yes - that's it, but remember that each TRUE level is a combination of all the decisions that came before.  Your CASE logic is going to have to handle quite a few more variations than the IF structure.

You can't say CASE 6th position = "0", then strip off 6 characters, because it doesn't take into account all the other preceding decisions that made that 6th character relevant to the decision.
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 38305518
Thanks for the compliment, Script - sorry to step on your toes.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38305595
wouldn't use a case statement here.  I'd used a nested if.  otherwise you'd have to build that whole tree into each case option.  Yuck.
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 38305599
@ScriptAddict:  Enthusiastically agree   Not a good case for CASE   (pun intended)
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 450 total points
ID: 38309983
I think we might approach this by negating all of the conditions.  That way, we do a positive evaluation of the length, rather than a negative calculation of the length.

Here are the negated conditions and length value adjustments.  There is a final (else condition) length adjustment value of 5.
NOT(OR(MID(G4,1,1)="0",MID(G4,1,1)="9"))  ==>0
NOT(OR(MID(G4,2,1)="0",MID(G4,2,1)="1"))  ==>1
NOT(OR(MID(G4,3,1)="0",AND(MID(G4,3,1)="1",MID(G4,1,3)<>"001"),MID(G4,1,3)="900"))  ==>2
NOT(OR(MID(G4,4,1)="0",MID(G4,1,4)="9011",AND(MID(G4,1,5)="00021",LEFT(R4,2)="BR")))  ==>3
NOT(OR(MID(G4,5,1)="0",AND(MID(G4,1,5)="00021",LEFT(R4,2)="BR")))  ==>4
MID(G4,6,1)="0"  ==>6

Open in new window


Using this format, you should be able to construct a CASE statement in T-SQL.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38312870
I edited the code snippet to correct the last condition, which should not have been negated.
0
 
LVL 10

Expert Comment

by:mark_harris231
ID: 38313085
+1 aikimark.  Once focused on explaining the IF formula, it didn't occur to me to work from the other direction.  Nicely done.

There is a follow-up question tied to this (the actual SQL) you may want to "cash-in" on:

Convert Excel function to SQL
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38314940
Thanks, Mark.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question