Comments on: Excel IF statement with multiple conditions

For powerful data analysis, you may often need to build an Excel IF statement with multiple conditions or use IF together with other functions. This tutorial will show you the most effective ways to do this. Continue reading →

Comments page 36. Total comments: 4557

  1. Hi ,

    Could you please help me to find formula for below Question ?

    if Value of cell B is greater than and equal to 95 , its cell A*3% .
    If Value of cell B is greater than and equal to 85 & less than 95 , its cell A*2.5% .
    If Value of cell B is greater than and equal to 80 & less than 85 , its cell A*2% .
    If Value of cell B is greater than and equal to 70 & less than 85 , its cell A*1% .

    Please help me out .

  2. Sir I recruiting some employees for my office. I recevied many applications and I have prepared a list in excel there is a condition that the applcant shouldn't be over the 40years of age and not less than 18years. I have entered the date of birth in column b and calculated the age in column c now i have the values some thing like 30year 2months and 4days now I need a formala to be entered in column D to find out that if the applicant is overage, within age limit or is he underage....

  3. I am trying to create an IF/THEN from a drop down list.
    C1 would have the drop down LIst: Hamburgers, hot dogs, nachos, pizza

    So the cell with the formula would need to perform: IF C1 is hamburgers, $5. If C1 is hot dogs, $2. if C1 is nachos, $4. if C1 is Pizza, $10.

    Can anyone help give me a framework?

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(C1="hamburgers",5,IF(C1="hot dogs",2,IF(C1="nachos",4,IF(C1="pizza",10,""))))

      In Excel 2016 and above, you can use the IFS function

      =IFS(C1="hamburgers",5,C1="hot dogs",C1="nachos",C1="pizza",10)

      If there are a lot of products and conditions, I recommend searching for the price using the VLOOKUP function.
      I hope my advice will help you solve your task.

      1. Thank you! I will try this out

  4. Hi Everyone
    I am trying to write a formula that has few IF conditions eg:

    if A1>=25 (age), then if B1=>£8.91, B1 otherwise £8.91 (it does work when A1 (age ) is = 25, if I change it to any other age it throws an error. This is one part of a formula

    the full formula should calculate:
    if A1>=25 (age), then if B1=>£8.91, B1 but otherwise £8.91, if A1=21, then if B1>8.36, B1 or £8.36

    is it possible to calculate? I am really struggling with this, any help appreciated

    1. Hello!
      If the number 9 is written in cell B1, then no Excel formula can change the value in B1 to 8.91. This is only possible with a VBA macro.

  5. Our clients have 4 scenarios that produce different outcomes. I am trying to produce a formula for those outcomes -
    Columns I & J are the scenario factors, K is the outcome

    I2=Y J2=N K2=N
    I2=Y J2=Y K2=Y
    I2=N J2=Y K2=Y
    I2=N J2=N K2=N

    I have tried the following, using " " for the Y & N variables, also trying it without the " " and the outcome produces #NAME? with both formulas. I'm sure there is something easy I am missing.

    =IF(OR(AND(I2=Y,J2=N)),N,IF(OR(AND(I2=Y,J2=N)),Y,IF(OR(AND(I2=N,J2=Y)),Y,IF(OR(AND(I2=N,J2=N)),N))))

    1. Hello!
      One simple formula describes your 4 scenarios

      =IF(J2="N","N","Y")

      If you have other questions, don't hesitate to ask.

  6. I'm trying to get two texts with the same number value...
    home=0.5
    office=0.5
    any other entry=0

    any thoughts on how to do that?

    =IF(B5="home"or"office", 0.5,0) didn't work

    1. Hello!
      The formula below will do the trick for you:

      =IF(OR(B5="home",B5="office"), 0.5,0)

      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.

    2. Hello!
      Please try the following formula:

      =IF(OR(B5="home",B5="office"),0.5,0)

      I hope it’ll be helpful.

  7. Hi! Can anyone please help me in the following situation:
    Delivery charges are based on two types of services i.e Overnight & Economy
    Barcode of both are like OVN123456 & ECO123456
    If weight is <=1 kg delivery charges for OVN are 100 & For ECO are 70
    If weight is <=3 kg delivery charges for OVN are 175 & For ECO are 150
    I want that Delivery charges cell check if B3 has code OVN and E3 is having weight <=1 then show 100 OR if B3 has code ECO and weight in E3<=1Kg it shows 70.
    I will be very thankful.

  8. I need to return 100% in a cell for cells B2/A2. If B2 = 0, and A2= 0, I want the result to be 100% as a goal attainment returning 100% in C2.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(AND(B2=0,A2=0),100%,B2/A2)

      I hope it’ll be helpful.

  9. A1=B1 B1=48 C1=62
    A2= (very long formula) B2=A5 C2=(A5 in case A1=C1)
    A3= (very long formula)
    A4= (very long formula)
    A5=A1*sum (A2:A4)

    How can I put a formula for C2 since it is so hard to repeat all the formulas in A2 to A4? Please help me with this.

  10. Hi,

    Can someone please help me with this formula.

    =if(F3="emergency loan"=1*D3,if(F3="short loan"=2*D3,if(F3="normal loan"=3*D3,if(F3="development loan"=5*D3))))

    Essentially, if cell f3 reads a certain type of loan, i'm supposed to multiply it with cell d3 and the figure next to it. I'm stuck and frustrated.

    Any help would be appreciated. Thanks in advance

  11. Hi,

    I'm horrible with formulas and I need help. I need to put a dollar amount in a cell based on data on a different cell. This is what I am trying to do;
    If row D = MB,CB.DB I want row I to show $2000, if row D = MB+,CB+,DB+ I want row I to show $3500 and so on. What formula would I need to use? Thanks in advance!

  12. Can someone help me to create a formula for this, please

    Payment Type - FB
    Amount - 100

    I want to get this if the payment type is FB it should be equal to the amount
    if not FB then 0

  13. I can't seem to figure out how to fix this formula. Any thoughts?

    =IF(AND(F6=”N’,G6=”N”,E6/2, IF(AND(F6=”Y”,G6=”N”,(E6/2)*0.75, IF(AND(F6=”N”,G6=”Y”,E6, IF(AND(F6=”Y”,G6=”Y”,E6*0.875,))))

    If F6="N" and G6="N" then E6/2
    If F6="Y" and G6="N" then (E6/2)*0.75
    If F6="N" and G6="Y" then E6
    If F6="Y" and G6="Y" then E6*0.875

    Example:
    e6=3780 f6=n g6=n then answer should be 1890
    e6=3780 f6=y g6=n then answer should be 1417.5
    e6=3780 f6=n g6=y then answer should be 3780
    e6=3780 f6=y g6=y then answer should be 3307.5

  14. HELLO ! Can someone help me PLEASE.
    i have 6 Names in 3 DIFFERENT CELL: in cell A1"Emo/nonEmo", in cell B1"Bio/nonBio", in cell C3"Sio/nonSio". I want in cell A4 to display automaticaly miscellaneous combination between 3.

    For example =IF(a1=bio,b1=nonBio,c1=nonSio) in A4 display"Anorganic". Thk

  15. Hi All,

    can you help me for below.

    i have 3 condition,

    region,

    manager,

    more then 50 days,

    so i have to find

    region : Delhi.

    manager : Alex.
    then less then 50 days how many business done by alex.
    after (region=delhi)(manager=alex)(days=<50)(total business done).

    i tried if and sumif but i didn't get correct answer

  16. Hello,
    I want to copy a formula to downward cells, the first value to be changed cell to cell but the second should copy with the same value to all down cells:
    Formula: B77-C4, to the down cell the formula should change to B78-B4, B79-B4 etc.
    I wish someone can help
    Regards.

  17. I need to compare grades in two cells. like if D6=A+ and D7=A, than "A+". means show higher grade. grades are A+, A, B+, B, C, D and F. to get final grade, I need to compare two grades. if both grades are same, final grade will be same. if grades are different with one step, final grade will be higher one. if grades are different, with two steps, final grade will be in between. if grades are different and apart from each other, final grade will be in between higher one. examples given below:

    example 1 (same)
    A+ and A+ = A+
    A and A = A
    C and C = C

    example 2 ( different/higher)
    A+ and A = A+
    A and B+ = A
    D and C = C

    example 3 (2 step different/ in between)
    A+ and B+ = A
    A and B = B+
    D and B = C

    example 4 ( apart/ in between higher)
    A+ and B = A
    A and C = B+
    D and B+ = B

    Please guide formula

  18. im trying to see if there is a way to detect a zero in cells b2 to b9 if there is i want it to execute one function or the other how can i do this (started self teaching best to over explain thank you)

  19. HOW TO SOLVE THE PROBLEM ?
    20% ANS = 20
    =TEXT(P2,".00") This correct or ......

  20. Hi!

    I am trying to check for a valid response for a variable (P2), the code/conditions I have are meant for EpiInfo but I am having so much trouble getting the formula translated and working in Excel. Any help is greatly appreciated!

    The conditions are:

    P1=1 AND P2>0 AND P2<8
    OR
    P1=2 AND (P2=0 OR P2="." OR P2=99)

    The returned value is '1' and all ELSE is '2'

  21. Hi can u tell me how to use if and or function to get depreciation value with sln, dB and Syd method for the same data in same cell.

  22. if-or(L59>L60,L59>L61,L59>L62),''yes'',''no'' which button to click on the keyboard in order to get to yes or no. i am learning on line.please help

  23. Hi,
    I am trying to create a formula with 3 different conditions.
    If J3<5 and K3<5 it should calculate K3-J3+1
    If J34 it should calculate 4-J3+1
    If neither is true the result is 0

    I have tried different variations o the following but nothing works:
    @IF(OR(AND(J3<5,K3<5),k3-j3+1,AND(J34),=4-j3+1,0))

    Any advice would be greatly appreciated. Thank you

    1. ERROR in 2nd "If" condition I posted.
      Should read: If J34, it should calculate 4-J3+1
      ERROR in formula I posted.
      Should read: @IF(OR(AND(J3<5,K3<5),=k3-j3+1,AND(J34),=4-j3+1,"0"))

      I copied and pasted so I am not sure what happened. So sorry for any confusion!

      1. OK so strange - after hitting Send the text changes in my post!
        Where it says J34 it should say IF J3 is less than 5 and K3>4.
        Hope the 3rd time is a charm! Thanks again.

        1. NEVER MIND - I figured out by studying at least 1,000 questions already answered here over the past few days.

          Correct formula: =IF(AND(J3<=4,K3<=4),K3-J3+1,IF(AND(J34),4-J3+1,0))

          Thanks to everyone who answers strangers very complex problems on this site. AMAZING!

          1. And once again my formula changed upon posting.

            If anyone needs the correct formula - respond to this and I will try to write it again with some other method

  24. HI
    I NEED A FORMULAR THAT WILL HELP CHECK IF A STUDENT HAVE PASS ALL REQUIRED COURSE.
    THANK YOU

  25. Cell G16 shows total.
    I want G17 to display "Level One" if cell G16 falls between B4 on another sheet (number listed in cell B4 is 10-13). Need this nested with C4 (14-16), d4 (17-19), e4 (20-22), f4 (23-27), g4 (28-38)

  26. i have to design a excel sheet for calculating printing cost, which involves multiple conditions
    categories :paper size, quantity, paper quality (100 GSM), Lamination,etc., i have to calculate paper cost, plate cost, labour cost according to the inputs.

    under paper size category there are diff sizes like
    1.a4
    2.a3
    3.crown
    4.double crown
    5.dimmy
    etc

    under paper quality categery it also varies according to gsm like 100gsm,170gsm,300gsm etc

    by looking up and combining paper size and gsm i have to calculate paper cost and remaining calculations labour printing cost, plate charges are also have to be calculated using combined inputs.

    please suggest me formulas with example to use / provide me a model excel.

    Thanksalot,

  27. does any know
    I want to write an argument statement that if a number in a certain column >-999 or<999 then send me an email.
    does anyone know how I can write this?

  28. Hello,

    I'm working on a project with 3 criteria and my formula seems to super long.

    =IF(AND(K2=12,L2>2),6,IF(AND(K2=6,L2>2,L28,M211,M20,M22,L20,M25,L210,M28,M26,L20),2,IF(AND(K2=3,L2=8,M2>25),2,IF(AND(K2=6,L2=8,M2>25),4,IF(AND(K2=6,L2=8,M2>25),4,0)))))))))))))

    1. Column K is fixed with 3 numbers = 3, 6 and 12
      Column L could be between from 1-12
      Column M could be between from 1-31

      This is my formula so far. It's Super long. Maybe you can help me.

      =IF(AND(K2=12,L2>2),6,IF(AND(K2=6,L2>2,L28,M211,M20,M22,L20,M25,L210,M28,M26,L20),2,IF(AND(B2="KSA",K2=3,L2>2,L26,L28,M28,M20,M212,M22,L25,L28,M28,M20,M22,L22,L22,L22,L22,L22,L22,L225),2,IF(AND(B2="UAE",K2=6,L2=8,M2>25),4,IF(AND(B2="KSA",K2=3,L2=8,M2>25),2,IF(AND(B2="KSA",K2=6,L2=8,M2>25),4,IF(AND(B2="UAE",K2=6,L2=8,M2>25),4,IF(AND(B2="Jordan",K2=6,L2=8,M2>11,M225),4,IF(AND(B2="Jordan",K2=4,L2=8,M2>11),2,0))))))))))))))))))))))))))))))))))))

  29. Hi all looking for help with and IF AND and Or function

    im trying to get it to either times by .25 if both parameters are met or if only one then times by .5

    And if none of the parameters are met then leave as is.

    =IF(AND(J45="Tigress",M45>100000),O45*0.25,O45)*(OR(IF(J45="Tigress",O45*0.5,O45)))

    Any help would be much appreciated

    1. =IF(AND(J45="Tigress",M45>100000),O45*0.25,IF(OR(J45="Tigress",M45>100000),O45*0.50,0))

      This should work

      Explanation on the below

      If J45=Tigress and M45 less than 100000, value in cell O45 will multiply to 0.25
      If J45=Tigress or M45 is less than 100000, value in cell O45 will multiply to 0.50
      If neither of the equation, the answer will be 0 - or you can change it to "Not found" instead of 0

  30. Hello,

    I am trying to use the formula below, but it is not correctly categorizing my data and I cannot figure out why.
    =IF((AND(A2>=TIME(7,0,0),A2<=TIME(18,0,0))),"Diurnal","Nocturnal")

    While some categorizations are correct, others are not. For example one species was caught at 15:36 Army time, but is categorized as nocturnal when it should be diurnal. Any thoughts?

    Also, as a fun bonus question: is there any way I could add a third option for species that come out at dawn out at dawn or dusk?

  31. Aloha,
    Can someone please check this formula? =IF(C3:D3=E3:K3), "BALANCE")

  32. Not entirely sure how the non-sensical formula in my last post managed to get in there. Note to self: Friday afternoon is no time for troubleshooting and analysis. It was clearly Beer O'clock and I was not paying attention to my inner voice. :)
    Feeling bright and bushy tailed on this Saturday morning, I was able to solve the puzzle.
    =IF(OR(AND(I12>F2,I12F2,J12F2,K12F2,L12<G2)),"Y","")

  33. I am having a heck of a time trying to get an IF(OR(AND formula to work correctly. My rows contain maintenance requirements with varying frequencies or recurrence. I then have 15 date columns each holding the next projected date for each maintenance requirement. The statement below captures a search through 3 of the 15 columns to see if the projected maintenance falls prior to a planned maintenance time. I keep getting an #VALUE! error. Any help would be appreciated. I can't help but think it is something simple. I have had attempts where I can get it to return a "Y" for the first occurrence but nothing after that in subsequent years. Thx.
    =IF(OR(AND(I12>$F$2,I12$F$2,J12$F$2,K12<$G$2)),"Y","")

  34. Hi All,
    Need a help in sorting out a problem. I'm trying to use the combination of IFS, AND, OR here.
    (All values are dates)

    Condition 1:
    Today's date (C1) = 04-12-20
    A1 = 04-12-19, 04-12-18
    &
    A1 = 04-02-21, 04-12-21

    I want to merge these conditions into a single cell E2

    If (C1-A1365,"Past 13+ Months") and if(A1-C190,"Fcst 4+ Months")

    Problem is that, it is reading two conditions as same, if the value is 0, which I'm trying to avoid.

    I would really appreciate some help on this by anyone.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF((C1-A1) > 365,"Past 13+ Months",IF((A1-C1) > 90,"Fcst 4+ Months",""))

      I hope it’ll be helpful.

  35. I am preparing examination results, that consist of thirteen subjects each subject has two scores, then i calculated the average for each subject by considering two score i need a help on how to find the result points by considering seven subjects that a student got higher marks.

  36. In Cell I5 Create a drop down list with MON,TUE,WED, THU, FRI, SAT, SUN
    In Cell I5 Select TUE from the drop down list
    In Cell I6 Create a drop down list with BKLYN, QNS, NYC, BX, SI
    In Cell I6 Select NYC from the drop down list
    In Cell J5 Create an IFS formula that will result "# of Drivers working" on the DAY selected in Cell I5 and
    the BORO selected in Cell I6
    I know, how many drivers were working on the selected day and borough as I am following the chart but I have a problem with the formula. I don't know, how the formula will be since it requires me to have a formula in one cell with two different condition. Need help in formula.

    MON in Bklyn, Nyc, Qns, Bx, Si is only 1 driver working
    TUE all the same monday results
    Wed all the same monday results
    Thur all the same monday results
    Fri all the same monday results
    Sat all the same monday results
    Sun all the same monday results
    Only 1 driver was working when the condition met with any options if its Days or Borough they all have a same answer which is 1.

    Thankyou.

  37. Hello
    Rate Quantity Result
    2 2 4
    2 Text Text
    Text 2 2
    Text1 Text2 Text2

    I need the result like this help me with the formula please

    1. 2*2 = 4
      2*Text = Text
      Text*2 = 2
      Text1*Text2 = Text2

      All possibilities should be in one formula

  38. Hello!

    I'm having a bit of a challenge on this issue. I have a list of 2 columns, A and B.
    Column A has duplicates that I need to remove, but I need to "Textjoin" column B first.

    > Forgive extra Parenthesis, I need them to be able to read it better.

    Currently using "=IFS((A2=A3), (TEXTJOIN(", ",TRUE,B2,B3)))

    However, the problem is that it leads to duplicate text joins, and it has a limit to how many values it's joining. I have it set as the first function of the column to start things out.

    Formula #2 I came up with:
    =IF((A2=A3), (TEXTJOIN(", ",TRUE,B2,B3)), (IFS((A2=A1), (TEXTJOIN(",",TRUE,E1, B2)))))

    However, even this doesn't work as I want it to because it's merging cells multiple times. Not all of the groupings are by two. Some are 5 cells, some are 3, etc.

    Formula #3 I came up with:
    For this one to work I had to put in a value for the first cell in Column C. (I just set it =A2).
    "=IFS((A3=A2), (TEXTJOIN(", ",TRUE,C2,B3)), (A3=A4), (TEXTJOIN(", ",TRUE, B3, B4)))"

    Formula #3 produced the best results, but still adding cells incorrectly.

    The reason for this; there are roughly 12K rows and I need to reduce it as much as possible. However, I don't have a month to go cell by cell confirming it joined correctly.

    Hopefully you can pass on some assistance!

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. In which cells do you write these formulas? Please provide me with an example of the source data and the expected result.
      Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
      We'll look into your task and try to help.

  39. English Punjabi Hindi Math Science SST Comp Sci BELOW40% 90%&ABOVE
    16 24 40 8 8 12 20 0 108 45.0 ENG,MAT,SST
    24 28 40 10 18 12 26 0 132 55.0 ENG,PUN,MAT,SCI,SST HIN
    12 6 6 8 10 12 14 0 54 22.5
    16 22 10 4 10 4 22 0 66 27.5
    18 20 30 14 16 18 16 0 116 48.3
    14 22 30 14 16 18 16 0 114 47.5
    WHICH FORMALUA APPLYED IN EXCLE TO GET ANSWER. PLEASE HELP ME

  40. Hi! A non-techy person here and I need help for the formula for this please. (If B2 and C2 is blank then 0, if B2 and C2 has a value then D1+B2-C2) Thanks in advance!

    1. Hi,
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Please use the following formula

      =IF(AND(B2="",C2=""),0,D1+B2-C2)
      or
      =IF(ISBLANK(B2:C2),0,D1+B2-C2)

      I hope it’ll be helpful.

  41. Please i need a formula for the following:
    If "Abbasia" then add 30 minutes to the time (The time is in another cell) and if Maadi Please write the time as it is (The time is in another cell)

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question about IF function.
      To add 30 minutes to the time, use the formula

      A1+TIME(0,30,0)

      I hope I answered your question. If something is still unclear, please feel free to ask.

  42. I want to apply a formula to print purchase order status is active or if print purchase order status is cancel then print cancel. please guide me what is the formula i need to apply to get result

    1. Hi,
      An Excel formula can change the value of a cell. But she cannot start printing the document or cancel printing. To do this, you need to use a VBA macro.

  43. hi all

    i have some data like column a column b column c column d column e
    a1 b1 c1 d1 e1 f1 g1 h1 i1 j1
    1 2 3 4 5 6 7 8 9 10

    if b1="a", then c1 should 2 d1 3 e1 3 like series should continue
    if a1="a", b1="a", then c1 shoud start with 1, d1 2, e1 3 4 5 6 like

    if f1 "a", g1 should start with 6,7,8,9,10............

    A letter it may repeat and continues , but series should be continue.

    ple provide the formula for above logic.

  44. I'm getting a FALSE status on the one logic.

    =IF(AND(V8=1,Q8>7/31/2020,Q8<9/1/2020),R8,"")

    Q8 is the date 9/17/2020
    R8 is a dollar amount

    Logic 3 keeps returning FALSE, but I can't figure out why.

    I'll be copying this down a column where some of the dates fit the parameters.

    1. Hello!
      Here is the article that may be helpful to you: Using IF function with dates
      Please try the following formula:

      =IF(AND(V8=1,Q8 > DATE(2020,7,31), Q8 < DATE(2020,9,1)),R8,"")

      or

      =IF(AND(V8=1,Q8 > DATEVALUE("7/31/2020"), Q8 < DATEVALUE("9/1/2020")),R8,"")

      I hope I answered your question.

      1. They both worked! Thank you so much!!!

        From looking through your site, I had tried the DATEVALUE, but missed using the " marks.

        Thank you again!

  45. I have issues producing a function to fit in my amount cell.
    Ok.
    I have income from 3 sources Beans, Rice and Garri and I can only spend within the income received from each of these items. So I have an income section where I record income received from these items and their totals are in cell (A5, C5, E5 respectively). So For example, if I received 10,000frs as income from Beans, I cannot spend above the 10,000frs.
    I now have an Expenditure section where I have a date column, a searchable list column where I can select the source of income (Beans, Rice, or Garri) i want to spend from at the time being and then a column for amount.
    I want an IF function that when the amount of Expenditure of a particular item is more than the income received for that item, it should give an error message.
    For example. If I received 10,000frs for Beans, and I put the first Expense in the amount of Expenditure section, 5,000frs, choosing Beans as source of income and now if I want to put the second Expenditure on a different date say 6,000frs still choosing Beans as source of income. It should return an error message because is more than 10,000frs income received from that item (Beans)

    Please can someone help me out

    1. Hi,
      Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

      1. Ok. Thanks for the reply Sir. Please How can I get to you may be privately for help in this.

  46. Hello Ma'am

    how can i summary all the fail and pass in the same sheet? I want to separate all the fail and pass after using if function please help. I don't want to manual copy and paste the name the selected fail or pass thank you and GOD bless.

  47. Okay, I have a question. I am trying to pull data from one cell that has a lot of data. IF the data were consistent, I believe it would be easy. But it is not.

    So, I have cell A2 for example the data in the cell is something similar to this:
    Persons Cell Phone Number: 123-456-7890 (not everyone enters it that format and I can't 'force' it to be that way)

    Persons Personal E-Mail Address: (again, not everyone types it out the same)
    -Variations could be Persons Personal E-Mail Address: with or without a space before the start of the email
    Persons E-Mail Address: with or without a space before the start of the email

    All I want to do is easily pull out the persons email address and cell phone number. Here is an actual example of some of the data that could be in ONE cell (the data is not always in this order):

    PERSONS E-MAIL ADDRESS: PERSONS.EMAIL@OUTLOOK.COM
    ________ (SM INITIALS)
    PERSONS CELL PHONE #: (XXX) XXX-XXX
    ________ (SM INITIALS)
    WHERE ASSIGNED TO (SSSSSS) : MR JIMMY SMITS
    OFFICE/CELL PHONE NUMBER: (XXX) XXX-XXXX / (XXX) XXX-XXXX
    E-MAIL ADDRESS: ANOTHER PERSONS EMAIL@OUTLOOK.COM

    IF I AM UNABLE TO CONTACT MY OFFICE, I WILL CONTACT THE HELP DESK AT 1-800-XXX-XXXX.

    1. Hello!
      To extract e-mail from the text, use the formula:

      =MID(A2,SEARCH("PERSONS E-MAIL ADDRESS:",A2,1)+23, (SEARCH("_",A2,SEARCH("PERSONS E-MAIL ADDRESS:",A2,1)+23+1)) - (SEARCH("PERSONS E-MAIL ADDRESS:",A2,1)+23))

      To extract a phone number from text, use the formula:

      =MID(A2,SEARCH("PERSONS CELL PHONE #:",A2,1)+21, (SEARCH("_",A2,SEARCH("PERSONS CELL PHONE #:",A2,1)+21+1)) - (SEARCH("PERSONS CELL PHONE #:",A2,1)+21))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. Thanks, the problem is that only works if the data is all exactly the same. If the way they enter the title of the persons email address is PERSONS E-MAIL ADDRESS then it might work if all of the other data was in the same location, character wise, in each cell. Sometimes, however, they enter the title of the persons email address as PERSONS PERSONAL E-MAIL ADDRESS: sometimes they enter it as PERSONS E-MAIL ADDRESS: and then to top it off, if they put spaces after the : it throws it off even more.

        1. Hello!
          You can remove extra spaces using any of the methods indicated in this article. But if they enter data in the wrong place, the program cannot fix it.
          However, by changing these formulas, you can extract any part of your text.
          If there is anything else I can help you with, please let me know.

  48. Hi,
    I need to help do this correction and needed to add more like this???

    =IF(C4,(G4*H4*2+G4*J4*2)*L4),IF(C5,((((I5+H5)/2)*G5)*2+(G5*J5+0.141*K5))*L5)

  49. Hi, need help to create a formula that can add 7 numbers (my own given numbers) to generate 5 number combinations between 1 and 36.

  50. hello Team, I'm a bit stuck I need your help. So what I'm trying to do is to put 'if' formula in this way:
    I have 10 persons and 8 cars, when I put a person to work i want to be assign to a car, but when that person is off I want another person to be on that car. The problem that I have is that if I put the first person off the second person will take the car but the will double up.
    My formula is in this way: =if(B=1, A, B=0, C). I also tried =if(B=1, A, B=0, C, C=1, D)
    I think I would need a 'but' somehow but I don't get the hang of it.
    Any ideas would be very helpfull

    thank you

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)