Results 1 to 6 of 6

Thread: Return a comment based on values in another cell

  1. #1
    Member
    Join Date
    Jun 2012
    Posts
    80
    Rep Power
    13

    Return a comment based on values in another cell

    Hi Guys,

    I have a file with invoice numbers (over 120k). Each invoice number has a prefix (ex. PM1401-121312). Where 14 is the year 2014 & 01 is January month and the rest is the invoice number. So I want to know a formula which would give me the month (in MMM-YY format) next to the invoice number by just taking 1401 into consideration. The prefixes change and so is the spacing and formatting based on the entry done by employees. However, 1401 or 1402 remains the same.

    Waiting for a reply.

    Thanks

    Siyab

  2. #2
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14
    Can you show the sample with different formatting. It helps to understand your problem well.

  3. #3
    Member
    Join Date
    Jun 2012
    Posts
    80
    Rep Power
    13
    PM1401-121312 - Jan 2014
    CM1402-54543 - Feb 2014
    RXBF1405-222 - May 2014
    CT1412TY-12 - Dec 2014

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Assume your Invoice number in A2, in B2

    =TEXT(TEXT(--("20"&MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01 23456789")),4)&"01"),"0000\/00\/00")+0,"mmm yy")
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Member
    Join Date
    Jun 2012
    Posts
    80
    Rep Power
    13
    Thank you very much, it helped.


  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Admin View Post
    Assume your Invoice number in A2, in B2

    =TEXT(TEXT(--("20"&MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01 23456789")),4)&"01"),"0000\/00\/00")+0,"mmm yy")
    Here is a similar formula that is eight characters and one fuction call less than the formula you posted..

    =TEXT(0+(20&REPLACE(MID(A1,MIN(FIND({0,1,2,3,4,5,6 ,7,8,9},A1&"0123456789")),4),3,0,"-")&"-01"),"mmm yy")

    I would note, though, that one could put this even shorter formula in the cell (saves another 15 characters and one more function call)...

    =0+(20&REPLACE(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8, 9},A1&"0123456789")),4),3,0,"-")&"-01")

    and simply Custom Format the cells using "mmm yy" (without the quote marks, of course)... doing it that way, the cells will contain real dates, instead of Text that looks like a date, and which can be used directly in other formulas if need be.
    Last edited by Rick Rothstein; 05-23-2015 at 09:06 AM.

Similar Threads

  1. Vlookup to Return Multiple Values
    By Admin in forum Download Center
    Replies: 9
    Last Post: 02-17-2017, 07:03 PM
  2. Replies: 4
    Last Post: 09-23-2013, 10:41 AM
  3. Replies: 2
    Last Post: 05-30-2013, 07:28 PM
  4. Replies: 8
    Last Post: 04-16-2013, 02:04 PM
  5. Replies: 1
    Last Post: 04-24-2012, 09:55 PM

Posting Permissions

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