CODEWITHSUNDEEP
excelexcel-formula
wittman
wittman

Reputation: 305

Combine 2 excel formulas

in column C i have a text with "xxx has been deleted because... " or "xxx have been deleted because... " . I have 2 formulas:

=IF(C3<>"",IF(FIND("has been deleted",C3),LEFT(C3,FIND("has been deleted",C3)-1),"DDDD"),"AAAAAAA")

and 

=IF(C5<>"",IF(FIND("have been deleted",C5),LEFT(C5,FIND("have been deleted",C5)-1),"DDDD"),"AAAAAAA")

I want to combine the two but it doesn't work. Here is what i have tried :

=IF(C4<>"",IF(FIND("have been deleted",C4),LEFT(C4,FIND("have been deleted",C4)-1),IF(FIND("has been deleted",C3),LEFT(C3,FIND("has been deleted",C3)-1),"DDDD")),"AAAAAAA")

=IF(C4<>"",IF(FIND("have been deleted",C4),LEFT(C4,FIND("have been deleted",C4)-1),LEFT(C3,FIND("has been deleted",C3)-1)),"AAAAAAA")

Upvotes: 0

Views: 88

Answers (2)

Lana B
Lana B

Reputation: 496

formula: 

=IF(C1="",  "AAAAAAA",
    IF(  AND( ISERROR( FIND("has been deleted",C1)), 
              ISERROR( FIND("have been deleted",C1))),
        "DDDD",
        LEFT(C1,  IFERROR( FIND("has been deleted",C1), 
                           FIND("have been deleted",C1)) -1))
 )

and here is my test based on the sample you described:

TEST

please note formula needs pasting into D1 and dragged down. as in picture

Upvotes: 2

LKDG
LKDG

Reputation: 41

=IF(C1="","AAAAAA",IF(OR(ISERROR(FIND("have been deleted",C1)),ISERROR(FIND("have been deleted",C1))),IF(ISNUMBER(FIND("has been deleted",C1)),LEFT(C1,FIND("has been deleted",C1)-1),IF(ISNUMBER(FIND("have been deleted",C1)),LEFT(C1,FIND("have been deleted",C1)-1),"DDDDD"))))

Please enter this formula into D1 and it will show result based on value C1. I hope we understood your question properly.

Upvotes: 2

Related Questions