Comments on: Excel: If cell contains formula examples

One of the most common tasks in Excel is checking whether a cell contains a value of interest. What kind of value can that be? Just any text or number, specific text, or any value at all (not empty cell). Continue reading →

Comments page 4. Total comments: 244

  1. Hi,

    Kindly help please
    A have a row for the report headers and below it a a row that says it is Mandatory or optional.
    How so i check if the mandatory columns have value?

    Thank you.

      1. Hi! Thanks for the response.
        But how do i make it dependent on the mandatory/optional row?

        For example if the following column headers are the following: name, address,mobile,birthday.( last column is the checking, row complete?)

        And then on the row below , all the fields are mandatory except for the birthday.
        * row complete value should be TRUE if the mandatory cells are populated.

        Thank you!

          1. Hi,

            Thanks.

            It would somehow look like this.
            But i dont get how will the code be dependent on the second row. (By checking “Mandatory “)

            Name Address Birthday Mobile Row complete?
            Mandatory Mandatory Optional Mandatory
            James ABC street 8171777 True
            Alice Aaa streeat 10/10/1970 81666 True

              1. Hi, the problem is that i need to check for row 2 as well( mandatory optional)
                Starting from row 3( i need to check if there are blank values from the cells tagged as mandatory)

                I actually have around 20 headers ( lets say from a1 to t1) and from a2 to t2 it says mandatory or optional.

  2. Hello,
    I am trying to use a formula on a sheet with roughly 7900 rows that are constantly being added on to. I have part numbers/model numbers of different styles from different departments. Ones that are in the format of ###AAAAA* (3 numbers followed by letters of various lengths) to show in the column "1" as "INDST". I would also like the part numbers that are ####-###* (4 numbers followed by a dash and more numbers of various lengths) to show in column "1" as AERO. If the part numbers do not fit these requirements, I would like them to show as "ASSY"

    Additionally,
    A separate situation I Have is that there are (4) Statuses that I Have in Column "2" they are complete, Firm, Released, Stopped. I would like to have them all on the original sheet as well as separate sheets for each "Status".

    Finally,
    I Have due dates in Column "3" that I would like to change color from "Green" if the date has not passed, to "Red" if they are late, and I would like them to retain their color, but not update again if the job is in the "Complete", "Status" for record keeping later on.

    If there is a way that I can have this continue to update when new information is added/Changed, since each day rows are added and some change statuses from "released" to "complete" or another "Status". I have basic knowledge of VBA, but not with a Data Dump of this size each day. I know there is a lot here, I would appreciate any and all help on this one as I am new to this job and am working with unfamiliar with these part numbers. I have included a small part of the data below and have started to manually put in Dpmt. names

    Dpmt. / Status / Due Date / last transaction / Job Date / Job / item

    INDST Complete 1/12/2021 4/12/2021 E291200-03 316BSZ-A213
    AERO Complete 2/3/2021 6/25/2021 E291204-01 7200-8739-RM
    AERO Complete 2/3/2021 7/19/2021 E291204-02 7200-8739-SZ
    AERO Complete 2/3/2021 6/8/2021 E291204-03 7720-8740-RH
    AERO Complete 2/3/2021 6/8/2021 E291204-04 7720-8740-RM
    AERO Complete 2/3/2021 6/24/2021 E291205-01 7200-8741-RM
    AERO Complete 2/3/2021 6/23/2021 E291205-02 7200-8741-SZ
    AERO Complete 2/3/2021 6/10/2021 E291205-03 7720-8742-RH
    AERO Complete 2/3/2021 6/10/2021 E291205-04 7720-8742-RM
    INDST Complete 1/14/2021 3/18/2021 E291210-02 311BRK7-A335
    DIGI Complete 1/14/2021 3/30/2021 E291210-03 340CRF6-A392
    INDST Complete 1/18/2021 3/31/2021 E291213-01 317BRH7CAFNGJ
    DIGI Complete 1/19/2021 4/6/2021 E291220-01 240CUQ6-A461
    CABLE Complete 2/1/2021 4/6/2021 E291220-03 MEC-CA
    Complete 1/15/2021 3/30/2021 E291224-01 241DRX7CAFJGJ
    Complete 1/15/2021 3/25/2021 E291226-01 340CPP2CAFK
    Complete 1/15/2021 3/31/2021 E291226-02 340CSZ3CAFK

    1. Hello!
      To automatically populate column 1 based on part numbers, you can use regular expressions. The formula will be something like this:

      =IF(RegExpMatch(F1,"\d{3}[A-Z]"),"INDST",IF(RegExpMatch(F1,"\b\d{4}-\d"),"AERO","ASSY"))

      You can find the examples and detailed instructions here: Excel Regex: match strings using regular expressions.
      I also think this article will be useful: Excel conditional formatting for dates & time.
      I hope my advice will help you solve your task.

  3. I want to use the IF function where the logical test references a cell/column that looks up a value on a separate spreadsheet. The logical test returns the value in the cell (which is the simple lookup formula) rather than returning the value that is looked up. How do I solve for this?

    1. Hi!
      Sorry, I do not fully understand the task.
      The value in a cell that is looking for a value in another table is the value being looked up. Describe an example of what you want to do.

  4. Hi Alexander,

    I been going crazy trying to get this formula, if you could help me that me very appreciated.
    What I am trying to do is

    (b1+b2)/2 if b1 or b2 aren't entered don't divide just give me the number that was entered in b1 or b2

    Thank you in advance

      1. Hi Alexander,

        Thank you so much for your help it help

  5. Hi,

    I need to find a formula where the number is contained within text in a different cell. For example:

    Column A Column D

    21 Address 21 London Road London

    There are 2253 numbers which I need to find within 4955 cells, please help!

    Many Thanks

      1. Hi,
        The only issue is this is only taking it from the first column, I would like it to look in the whole of column D to find the matching one?

        Many Thanks

        1. Hi!
          Copy the formula for each cell you want to extract numbers from. It is impossible to do this with a single Excel formula. If this is not what you wanted, please describe the problem in more detail.

  6. I am trying to write a formula that allows me to do the following:

    Column I has either USD or CDN dollars,

    If I has USD then take Colum G Total price and times it by currency rate listed in T2 or the rate 1.20

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

      =IF(I1="USD",G1*T2,G1)

      1. Thank you so much, that worked. So kind of you.

      2. That was perfect - thank you so much I tried over 8 different variances of IF / OR and AND trying to get this to work. Your the kindest, thank you.

  7. Greetings, I’m trying to do the same as Cecile and Rasit, add some categories to my checking account info.
    I have one table named “Raw” that contains the raw data from my checking account. On a second tab (in the same file), I created another table named “IDtranslate”. I want to search for key words in the Raw Description and bring back a Short Description as a new column in my Raw table.

    This formula seems really close to what I need:

    =IF(SUMPRODUCT(
    --ISNUMBER(SEARCH(IDtranslate[search text],[@Description],1)))=0,
    [@Description],
    "found")

    Keep an eye on that value-if-false, “found” because that is the problem. The formula is in the “Short Description” column of my Raw table.

    Here is a sample of my Raw table (I think if you copy and paste into a blank Excel table, it will parse itself out correctly):

    Description Short Description
    Withdrawal POS #759507, MEMO: LOWE'S #2681 630 W NFIELD DR BROWNSBURGCard found
    Withdrawal POS #8886, MEMO: Wal-Mart Super Center 2786 WAL-SAMS AVONINCard found
    Withdrawal Debit Card MASTERCARD DEBIT, MEMO: SUN CLEANERS AVON INDate 12/18/21 Withdrawal Debit Card MASTERCARD DEBIT, MEMO: SUN CLEANERS AVON INDate 12/18/21
    Withdrawal Transfer To Loan 0001 found
    Withdrawal ACH VONAGE AMERICA, MEMO: ID: CO: VONAGE AMERICAEntry Class Code: WEBACH Trace Number: Withdrawal ACH VONAGE AMERICA, MEMO: ID: CO: VONAGE AMERICAEntry Class Code: WEBACH Trace Number:
    Withdrawal ACH BRIGHT HOUSE NET, MEMO: ID: CO: BRIGHT HOUSE NETEntry Class Code: TELACH Trace Number: found
    Withdrawal Bill Payment #91, MEMO: AMAZON.COM3 SEATTLE WACard 5967 found
    Withdrawal Debit Card MASTERCARD DEBIT, MEMO: ALDI 4405 AVON INDate 12/19/21 found

    Here is a sample of my IDtranslate table:

    search text ID category
    aldi Aldi grocery
    amazon Amazon Amazon
    amica Amica insurance
    bright house Bright House utilities
    loan 0001 car loan car loan
    lowe's Lowe's Home maint and improve
    meijer Meijer grocery
    mnrd Menards Home maint and improve
    panda express Panda Express restaurant
    paypal PayPal PayPal
    vectren Vectren utilities
    wal-m Wal-Mart grocery

    What I want to do is replace the “found” term in my formula with the correct value from the “ID” column of my IDtranslate table. The very first line in the Raw table where the “search text” lowe’s was correctly found needs to bring back “Lowe’s” from the ID column.

    I’ve tried replacing the “found” term with variations on IDtranslate[ID] (with and without @ tossed in there), but I keep getting spills or other errors.

    If I can get that Short Description formula to work, then adding a category column to my Raw table with a vlookup will be easy.

    Thoughts?

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

      =IFERROR(INDEX(E2:E12,MATCH(1,--ISNUMBER(SEARCH(D2:D12,A2)),0)),"")

      Column E - ID
      Column D - search text
      Column A - Description
      Hope this is what you need.

      1. Yes!!! That works. I'm glad to see you used MATCH. I had played with SWITCH a little bit but I failed at that. Makes feel like I sort of had the right idea!

  8. I am using excel to convert manual testing scenario sheets to automated xml files to test the Covid vaccine schedule and ensure our vaccine forecaster is functioning properly with the new rules. I need to find out if a formula within a cell is calling the DOB or the date of the last vaccine for the forecast and then use that to fill in the test description so I can more easily spot patterns in what causes unexpected forecasting returns. Basically I need a formula that says IF the formula in GN2 (earliest forecast date) contains a reference to E2 (DOB) then True else false. Is there anything that can do that for me?

    1. Hello!
      I recommend using the FORMULATEXT function. It will extract the formula from the desired cell and write it down as text. Then apply the SEARCH function

      =ISNUMBER(SEARCH("E2",FORMULATEXT(F5)))

      I hope my advice will help you solve your task.

  9. Hello,
    I am trying to figure out a formula that will tell me if one cell partially contains the same info as another cell.
    Example:
    If A2 has "PleaseHelpMe" and B2 has "Please"
    I want a formula that will do the following IF A2 contains B2 = "yes" or "no"

    Hopefully that makes sense.

      1. Alexander,
        You are awesome! Thank you, this will help out tremendously on a project I am working on.

  10. Hello! I have a large spreadsheet (300k rows) with clients details, unfortunately the data is from a form where people were simply asked to enter their City & Country. So they may have entered Christchurch NZ, or Auckland New Zealand, or Los Angeles USA etc etc

    We now wish to be able to add a new column that specifies the country ONLY for each client.

    What is the best approach for this?

    Ideally we would like to be able to have one formula that can search for multiple countries, so for example, if cell A2 contains "NZ" OR "New Zealand" the value in the new column shows as = New Zealand, if the A2 contains "United States" or "US" or "USA" or "America" the value in the new column shows as = USA. everything I have tried so far says it is too long, so I assume I need to work out how to use Vlookup? Is this what it will do!?

    Obviously there is a huge array of possibilities, is it possible to have SO many variables? Thank you!

    1. Hello!
      You will be looking for a piece of text in a cell. Therefore VLOOKUP cannot be used here.
      Try this formula:

      =INDEX(F1:F30,MATCH(TRUE,ISNUMBER(SEARCH(E1:E30,A1,1)),0))

      Column F - correct country names (e.g. New Zeland)
      Column E - arbitrary country names (e.g. NZ)
      Column A is your data (e.g. Christchurch NZ).
      I hope I answered your question. If something is still unclear, please feel free to ask.

      1. Thank you. your formula assisted me in resolving my pain area.

  11. I have a chart with 2 columns, 1 column - Month 2nd column - Amounts
    how do I create a formula that pulls out or subtract out the amounts for a certain month.
    EXAMPLE: February amount needs to be subtracted from Jan, Mar, April.

    January 150
    February 200
    March 500
    April 2000

    I know to sum up everything and then subtract, but I don't know how to create the formula to do this on its own.

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

      =SUM(B1:B4)-B2

      If this is not what you wanted, please describe the problem in more detail.

  12. Hello,
    I would like to match 3 cells in two separate tabs (same file), cells A (tab 1), and cells B & C (tab 2).
    Cells A & C are a string of words. Cell B is one word.
    If one of the words in cell C is found in cell A, then the formula returns cell B.
    I'm going through a bank statement and doing some budgeting based on categories I've created.
    For example:
    1/ Tab 2: Category "Utilities" (cell B) = Water, Council, BT (cell C)
    2/ Tab 1 (bank statement) (cell A): DIRECT DEBIT PAYMENT TO WATER REF 400000214, MANDATE NO 0125

    I haven't been able to work out the formula to use to bring back the value of cell B in a new column added to the statement (tab 1).
    Might you be able to help please?

    Many thanks,
    Cecile

      1. Hi Alexander,

        Thanks for your feedback. I was hoping for a shortcut, never mind!
        Once I've split the text, shall I then use the following formula:
        =IF(SUMPRODUCT(--ISNUMBER(SEARCH(Tab2CAtegoryList!$a$2:$a10,Tab1Statement!C2))),"categoryName1","No")

        Thanks,
        Cecile

        1. Hello!
          Yes, you can use something like this.
          IFS function can be used to choose from several categories

          =IFS(SUMPRODUCT(–ISNUMBER(SEARCH(Tab2CAtegoryList!$a$2:$a10,Tab1Statement!C2))),"categoryName1", SUMPRODUCT(–ISNUMBER(SEARCH(Tab2CAtegoryList!$b$2:$b10,Tab1Statement!C2))),"categoryName2")

  13. Hello

    I have an incident where Students are graded in a subject per week from week one to week 16. where each week is in its own column from Column B to Column P. where I grade each student with words "Passed" and "Excelled". I'm crediting their overall performance in another sheet which fetches from my primary sheet and I would like help with an excel function that searches for a word "excelled" in any week from week one to 16 and returns "promoted" if it finds the word "excelled" any where. Thanks!

    1. Hello!
      To search for values in the desired line, I recommend using such a formula:

      =SUMPRODUCT(--(A2:A20=N1)*(B2:P20= "Excelled"))

      N1 - name.
      If the formula returns the number more than 0, then the desired word is found for this student.
      I hope it’ll be helpful.

  14. in excel want a cell contains formula should remain as it is if changes made manually in the same cell.

    for example Customer A 50 (formulated cell)
    Manual changes done is the count cell as 40
    If I selecect customer B from drop down the formulated cell should remain same

    Is there any possibility to do this

  15. I.e. I have cell K2 containing 2 different categories (OWN & LEASE). If cell K2 is "OWN" I want to add values from cell P2+R2+S2 or if cell K2 is "LEASE" then I only want to add values from Q2

  16. =index(A:A,MATCH(C:C,A:A,0))

  17. my column A contains the following formula:
    =INDIRECT("'"&$D$2&"'!"&Parameters!N$2&$C22)
    The formula gets values from another tab based on certain parameters.
    Using conditional formatting I want to make sure the indirect formula exists in all the cells and no one has tempered with the formula. Is there a way to conditional format the column A to highlight cells using the Indirect formula?
    Thanks

    KK

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. For the same reason, I cannot check her work.
      I recommend using the FORMULATEXT function. It will extract the formula from the desired cell and write it down as text. You can compare this text with your formula if you also write it as text.

  18. Hi there, hoping I didn't miss this explained above or in the comments but here's what i'm trying to figure out:
    I have a list of titles in multiple rows of column I (Ex. I2 contains Associate, Manager, Senior Manager, Vice President. I3 contains Associate, Senior Manager, Vice President).
    I am using the following formula to separate each title into separate columns for each Row, if it is listed in "I" : =IF(ISNUMBER(SEARCH("Associate",I2)),"Associate") but I'm finding that when using the formula for Manager it is giving me a false positive because "Senior Manager" contains the word "Manager" (is should result in "FALSE").

    Basically, is there a way to add an exclusion for the word "senior" in the formula?

    Thanks!

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

      =IF(ISNUMBER(SEARCH("Manager",I3)), IF(ISNUMBER(SEARCH("Senior Manager",I3)),FALSE,"Associate"))

      I hope this will help

  19. Hi, upon some extensive research I couldn't find any answer to my problem. Here it is. Every month, I breakdown my transactions (from bank statements) according to their nature such as food, shopping and entertainment. So I prepared a key word mapping with natures as follows:

    Column A - Column B

    - Walmart - shopping
    - Netlix - entertainment
    - McDonalds - food

    in two column in excel (list is pretty long, examples are simplified). Each transaction records includes many words and details of the transaction. What I want is, if a transaction includes the key word of **NETFLIX**, I need to bring word **ENTERTAINMENT** as nature next to the transaction column in excel. Example: if transaction is "*...June charge of **NETFLIX** obtained from credit card 12345...*" the key word **NETFLIX** is included in the transaction details, then bring **ENTERTAINMENT**. Had I kept my list short, I would have done it with =if(isnumber(search(... formula but my list is looong.

    PS. Just extracting out the key word next to the transaction columnn would be fine, too. As the rest can be done by Vlookup.

    I need your help.

  20. Hello,
    I have a table in which I'm trying to match cities to markets. For example if I have a certain "City Name" in column G, I want a certain "Area name" in column J. I have tried all sorts of variations on this formula but am still a beginner and none of them seem to work.
    Noting that there would me multiple cities in column G to match the same area in column J.
    Any bit of help would be much appreciated. Thank you!

  21. Hi, Please can someone assist with a formula that checks 2 separate columns and if the text is an exact match then it must return a a text from the separate column and also a value to the correct column.

  22. -3.1441E-07x5
    + 9.9711E-05x4
    - 1.2506E-02x3
    + 7.4496E-01x2
    - 1.4013E+01x
    + 8.0642E+01
    I am trying to use =IF(ISNUMBER(SEARCH("x",A1)), "LEFT(A1,FIND("x",A1)-1","A1") formula to look at each set of data and see it there is an "x" in the data string. If there is, I need to delete everything on the left of the "x", including the "x". However, if there is no 'x' I need the formula to sinply copy the data string as is, to the next cell. I may not have everthing just right but I now that the ISNUMBER(SEARCH is correct, I get TRUE, FALSE as I should. I have not been able to pair the formula with the rest, due to excel assuming that the "x" in the Left/Find statement is supposed to be a "*". Is there a way around this?
    Thank you

  23. HI, I need help with totaling up a number meeting a certain percent and the ablity to excluding any zeros. I can do the countif to obtain the percentage that achieves the required percent but I don't know how to exclude the zeros.
    example: % achieving 80% w/o "0"
    100
    85
    0
    80
    80
    0

  24. I have a budgeting document that reflects a few sub-budgets. I want there to be an overall balance column (G) and then 3 sub-budgets (I/J/K). I'd like a formula in I/J/K that states the following:

    If column B includes the following partial text xxx (the budget indicator code), then subtract F from the row above it. (F is the amount spent).

    The column B will include things like TS1, TS2, TS3 or HS1, HS2, HS3. (The TS and HS are the partial texts I want it to look for - with columns I and J being the TS and HS balance columns.)

  25. Hi , I have a list of products with the cost for each product is written in the adjacent cell . what I want is that when I type the name of the product in another worksheet , the value of the cost appears automatically . Can anybody please help me to find the exact equation for that ? thanks in advance

  26. Hello
    Is there a way of using the below formula, but rather than have it search for the specific text only within a cell, it can search a sentence containing "apple" or "banana" etc then return the value based on the sentence content? I need the formula to be able to search for multiple fruits and return the value in another cell depending on what fruit it found within the sentence.

    For example, cell A1 contains the sentence, "Mr Smith ate an apple".
    cell B1 should then return Apple. However, if cell A1 contained, "Mr Smith ate a banana", cell B2 should return "Banana".

    =IF(A2="apple", "Ap", IF(A2="avocado", "Av", IF(A2="banana", "B", IF(A2="lemon", "L", ""))))

    Hope this makes sense!

    Thank you

    1. Hi Rhys,

      COUNTIF with wildcards in the criteria works a treat:

      =IF(COUNTIF(A2, "*apple*")>0, "Ap", IF(COUNTIF(A2, "*avocado*")>0, "Av", IF(COUNTIF(A2, "*banana*")>0, "B", IF(COUNTIF(A2, "*lemon*")>0, "L", ""))))

      1. Thanks so much! Worked perfectly.

  27. what would I use in the formula to lookup if a cell has text or number? (replacing the ISNUMBER) ISTEXT will not work as the cell can contain text or a number.
    =IFERROR(IF(B17="","",IF(ISNUMBER(INDEX(T_E,MATCH(I_E,L_E,0),MATCH("ACT "&B17&" DT",L_H,0))),"R",CHAR(163))),"")

    1. In your MATCH formula, what is the T_E , I_E and L_E? I believe that should be a range, but what range is it referring to?

  28. Hello,
    I have numbers on Column A1 that I need B1 to return with a name if the number matches
    For example
    A1 is 118 and B1 needs to be Chad
    A1 is 132 and B1 needs to be Mike
    A1 is 109 and B1 needs to be Tuan
    A1 is 110 and B1 needs to be Kevin
    A1 is 115 and B1 needs to be Carlos
    A1 is 105 and B1 needs to be Mark
    A1 is 107 and B1 needs to be Curtis

    and so on, I have been fighting this all afternoon.

    1. Use VLOOKUP formula

  29. hi,
    I came across an interesting problem need help to solve.
    I have some text in Column A ( SKU ) and text to be searched in Column C ( Contains ), I need to search in SKU ( Column A) if any of the text listed in Contains ( Column C) need to insert value of Contains in Column B ( Print contains ) if none of the values in Contains ( Column C) is part of SKU ( Column A )then need to print No.
    Expected result as below sample.
    A B C
    SKU Print contains Contains
    --- ---------------- -----------
    Dress-Blue-S dress dress
    Tshirt-White-XL NO skrit
    Skirt-Pink-XS skrit jeans
    Skirt-Yellow-L NO
    Tshirt-Black-M NO
    Skrit-Yellow-L skrit
    Jeans-Blue-XS jeans
    Dress-White-S dress

    Thanks in advance.

  30. Hi, i found this platform very useful in my daily work. Kudos to the guys managing this site.

    I came across a problem that I am unable to find solutions or rather i might not know how to search the problem.

    I have 2 workbooks(Report and Checklist)
    In Report I have 2 columns, Item, Person
    In Checklist I have Columns for Items. (5 Items, Apple, Grapes, Banana, HoneyDew and Orange), I have rows for Adam, John and Tom

    In the Report Workbook. It shows this (the pipe symbol is to separate the columns)
    Item | Person
    Apple | Tom
    Apple | Adam
    Orange | Adam
    Orange | JOHN
    ...

    Expected outcome (In Checklist Workbook)
    I want to match in the column of Adam and Apple to show as "Yes" and so forth.

    Thanks in advance

  31. Hi, i need to find the amounts (column B) through finding the partial match (A2)to the long text (c2) and display the appropriate amount (column D).
    LIST AMOUNTS LONG TEXT AMOUNT
    HJA13784 ? abcd-HJA17561 09 082019 1,000,000
    HJB02731 ? qwertyu-HJA13784 2019 08 2,500,000
    HJA17561 ? plantferqfas sdsd ,HJA13784 3,000,000
    SE18120347 ? asdfg sdg-SE10007894 4,000,000

    Please help me on this? Thanks!

  32. I have an order sheet containing, amongst other things, a description column and a value column. I need to put a comment such as "authorisation needed" in the description column when a value entered in the value column, in the corresponding line, is over £500.
    I have tried conditional formatting and data validation but cannot get them to work together!
    thinking I need some sort of "IF" formula but not well up on writing formulae.
    any ideas would be appreciated - thanks

  33. hi
    I want to find if the cell contains NZ and then have the 3 numbers after the NZ as the result.
    Example in cell D2 is DOL1003194 NZ101-05 in cell F2 I need the result to be 101
    cell D3 is VOL10402 NZ102-077 in cell F3 I need the result to be 102
    cell D4 is 51151317618 NZ112 in cell F4 I need the result to be 112

    Be grateful for help

  34. This is realy good if you have for example car models and you need to know what car it is
    =IF(ISNUMBER(SEARCH("GO";A1));"MINI VAN";IF(ISNUMBER(SEARCH("YE";A1));"Bus";IF(ISNUMBER(SEARCH("L";A1));"Luksery line";"other")))))
    and you can extend it as fares you need + it can have nr. of what year it was realised like you have L1 L2 L32 L13 L62 and its not important what nr. it is

  35. if a cell contains 12 Digits, then I want it to return specific text.
    Example:- If A2 contains 12 Digits then, B2 should say "Good".
    Can you help?

  36. I need a single formula that will say if there is a value in cell B1 then show 316, however if there is a value in C1 then show me 5000. I can get a formula that is on separate lines however, cannot get the formula in a single cell.
    1 2
    A Meter Number Amount
    B 10HD00548 316
    C 10HD00548 5000

    Thank you.

  37. Sorry, =MID(A1,FIND("USPM",A1),15)

    The 15 or 19 should match the length of the text sting you want to return.

  38. Found this very helpful - thanks

  39. Is the following possible and if so what formula would I use to pull this off?
    - Column A has rows of Summary data from problem tickets which will contain the problem ticket ID and other text.
    - The problem ID will always be 15 characters in length
    - The format of the Problem ID is USPM followed by the number for example USPM12345678911

    Is there a formula that will look at for example cell A2 for *USPM* and return everything within the * * IN CELL b2? For example A2= USPM12345678911 the formula looks at A2 to see if it contains USPM and if it does it returns USPM and the next 11 charters to its right.

    1. =MID(A1,FIND("USPM",A1),19)

      1. Robert - You are my hero!!! Thanks so much. This worked like a charm :)

  40. Hi! I am need help building a formula. I have a spreadsheet to be filled in with data. Account codes are across the top. I trying to find a formula string that will recognize which cell across has any text, then return the account number at the heading of column. Not sure if that explanation makes sense. I'm sure there has to be a formula to avoid doing this manually.

  41. "which is found in B4" sorry for typo, I mean B2

  42. Help, please! I need a solution
    Problem: B2 contains "IT2". In B7 I want to be shown that number which is found in B4, so B7 should contain "2". What is the correct logical formula?
    So again: if a cell (B2) contains a number, show in another cell (B7) THAT number.

  43. Good morning
    What i am trying to achieve is to count the number of full stops in a cell and return a number based on that, i.e if i have 1 full stop then it will return a 1 and if it has 3 full stops then it will return a 3, it may very well be that there are up to 10 full stops in a cell. The formula from your examples i am using is =IF(ISNUMBER(SEARCH($C$1,A1)),"1","")
    This works great where C1 contains a full stop

  44. HI,

    I Have a column, M, named "Qualifications". It contains different strings of Academic qualification data. But I just need to pick the specific qualification. E.g If the string reads " Masters of Education", I just need "Masters" If it reads "Certificate of Secondary Education", I need KCSE, If it reads "Bachelors degree in Medicine", I just need "Bachelors".
    Tried using the formula below but didn't work. PLease HELP

    =LOOKUP(M2{"*Doctor*";"*Master*";"*Bachelor*";"*Diploma*";"*Secondary*";"*CSE*";"*EACE*";"*Primary*";"*CPE*";"*N/A*"} "PhD";"Masters";"Bachelors";"Diploma";"KCSE";"KCSE";"KCSE";"KCPE";"KCPE";"N/A"})

    1. Teddy:
      Wildcards can be used in some functions, but not in others. If you need to use the * in the formula you'll need to use VLOOKUP or an INDEX/MATCH formula.
      Here's how to write a nested IF statement for the samples you provided:

      =IF(A74="Doctor","PHD", IF(A74="Master","Masters",IF(A74="Bachelor","Bachelors", IF(A74="Secondary","KCSE", IF(A74="Diploma","KCSE", IF(A74="CSE","KCSE", IF(A74="EACE","KCSE", IF(A74="Primary","CPE", IF(A74="N/A","N/A")))))))))

      You can use this as the basis for a huge IF/OR statement, but it would get crazy long.
      Read the VLOOKUP or INDEX/MATCH articles here on AbleBits and see if that helps.

  45. Hi,

    I have two data tables with multiple row and column data. in table 1, i have alphanumeric code and dates while in table 2 i have similar alphanumeric code. i wanted to search the table 2 for any part of the alphanumeric code from table 1 and on locating the same, fetching me the date againt the said code.

    1. Rajat:
      Do you have sample data from each table you can post here? It's easier to try and help if I can see what you're working with.

  46. Your suggestion on how to handle a cell that contains a specific string and do a partial match using combination of SEARCH, ISNUMBER and IF works like a charm! For example, my raw data input string in cell one is Apple, Ball, Cat, Dog and cell two is Apple, Dog. etc. etc. So I listed four separated columns in my modified data to store the results (1 or 0) if Apple, Ball, Cat or Dog is present or not in the string. I then reference the columns as tables and use a sumif to report on the respective tables. Works nicely. However I would like to pivot on the modified data and I want ONE field, not four. I would like one field showing table of Apple, Ball, Cat, Dog, Apple and Dog. One field name to be used by pivot table with six entries. How would I take the separated string results and put them back into a table so I can use a pivot table on that single table name?

  47. I scrolled thru your samples but did not find a match for what I'm really trying to accomplish

    Column A contain a scroll option box
    If Scroll option in cell A1 matches cell J3-J6 then B1=Good
    If Scroll option in cell A1 matches cell J7-J12 then B1=Bad

    Thanks

  48. Hi, Mind appears simply, however i have tried several funtions to know avail,HELP

    What i am trying to do is,
    DATE OF APPOINTMENT DATE DUE

    so todays date, then in date due, i want the date to show 90 days.

    =IF(D3<(TODAY()+90),"<<<","")

    help

    regards Debbie

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

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 :)