locked
Power Pivot - formula to extract data from ID number field RRS feed

  • Question

  • Hello,

    I have an ID number column called "ID#"...containing data like e.g. AB123N56789 or AB123F56789. I would like to create a calculated column with a formula.

    So, what would be the powerpivot formula to say.....If digit located in position 6 is N.....then want to see NOVEMBER.....Or If digit located in position 6 is F.....then want to see FEBRUARY.

    Regards.

    Sunday, January 21, 2018 8:15 PM

Answers

  • Hi ALEXRK,

    Thanks for your question.

    I would suggest you to use switch to implement this. See below test results:
    =
    SWITCH (
        RIGHT ( LEFT ( Table2[ID], 6 ), 1 ),
        "N", "November",
        "F", "Febraury",
        "J", "January",
        "M", "May",
        "A", "April"
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by ALEXRK Tuesday, January 23, 2018 5:16 PM
    Monday, January 22, 2018 5:53 AM

All replies

  • You could get the 6th character from a string by using the MID function.

    eg.
    MID([ID#],6,1)

    Then you could use either IIF() to test for a single value or SWITCH() to test for multiple values. 

    eg.

    SWITCH(MID([ID#], 6,1)
     , "N", "November"
     , "F", "Febraury"
    )


    http://darren.gosbell.com - please mark correct answers

    Monday, January 22, 2018 3:59 AM
  • Hi ALEXRK,

    Thanks for your question.

    I would suggest you to use switch to implement this. See below test results:
    =
    SWITCH (
        RIGHT ( LEFT ( Table2[ID], 6 ), 1 ),
        "N", "November",
        "F", "Febraury",
        "J", "January",
        "M", "May",
        "A", "April"
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by ALEXRK Tuesday, January 23, 2018 5:16 PM
    Monday, January 22, 2018 5:53 AM
  • Thanks, that worked perfectly.

    I knew how to use a 2 step process with MID and then with IF to convert those to (e.g April, November), but wanted to do it all in 1 step. Thanks.

    Tuesday, January 23, 2018 5:18 PM