+ Reply to Thread
Results 1 to 12 of 12

looking to combine if and mid function---i think

  1. #1
    Registered User
    Join Date
    12-18-2012
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    38

    looking to combine if and mid function---i think

    Three cells lets say A1, B1, and C1.

    A1=2012
    B1=JAN-1-2012
    C1=JAN

    Currently i have it set up to fill in C1 automatically with JAN by using =MID(B1,1,3)
    What i am hoping to acheive is only fill in C1 with JAN if year reads 2012 by getting from A1, if A1 reads 2013 and B1 reads JAN-1-2012 then C1 is blank. What is formula to achieve this??

  2. #2
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: looking to combine if and mid function---i think

    Are these formatted as dates, or just strings that you're using as an example?
    Going for Guru! Click the Star to the bottom left of this post if I helped!

  3. #3
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: looking to combine if and mid function---i think

    Try this in C1:
    =IF(A1=2012,"JAN","")

  4. #4
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: looking to combine if and mid function---i think

    =IF(A1=2012,left(B1,3),"")
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  5. #5
    Registered User
    Join Date
    12-18-2012
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: looking to combine if and mid function---i think

    Perhaps i didnt explain enough A1 will read 2012, B1 will read any date that is entered manually. C1 will only count first three, so JAN, FEB, MAR, ETC. Now if i change A1 to 2013 all C1's will be blank.

  6. #6
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: looking to combine if and mid function---i think

    Hi awest, can you answer Miraun's question... thanks...

  7. #7
    Registered User
    Join Date
    12-18-2012
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: looking to combine if and mid function---i think

    Yes formated as a date

  8. #8
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: looking to combine if and mid function---i think

    Try this...

    =IF(A1=2012,CHOOSE(MONTH(B1),"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"),"")

  9. #9
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: looking to combine if and mid function---i think

    if you are extracting jan by
    "Currently i have it set up to fill in C1 automatically with JAN by using =MID(B1,1,3)"
    then that cell is not a real date but text
    otherwise it would return 409 the first 3 digits of 1/1/2012 date code
    so it must be text then
    =IF(--RIGHT(b1,4)=--a1,LEFT(b1,3),"")
    Last edited by martindwilson; 12-18-2012 at 08:11 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  10. #10
    Registered User
    Join Date
    12-18-2012
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: looking to combine if and mid function---i think

    still not working, please help. I have included attachment. This spreadshee is used as a counter on another spreadsheet. It counts the JAN, FEB, ETC from the L cloumn. But when year changes we have to go in an manually look for the 2012 entries and make column blank so it is not counted. What i would like to do is just change the year in cell A2 to 2013 and have all entries in column L go blank--so they are not counted on corosponding spreadsheet.

  11. #11
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: looking to combine if and mid function---i think

    in l2
    =IF(M2="","",IF(--RIGHT(M2,4)=--$A$2,LEFT(M2,3),""))

  12. #12
    Registered User
    Join Date
    12-18-2012
    Location
    Denver
    MS-Off Ver
    Excel 2010
    Posts
    38

    Re: looking to combine if and mid function---i think

    success---thanks martin

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1