none
Selecting Text from a Field RRS feed

  • Question

  • I'm very new to Power Query.  I am trying to create a new column where I only show the zipcode from a field with the following in it...Example:

    Unity Transport, LLC, Kearney, MO, 64060, USA

    I can't dup the column and split it using deliminator, because of the extra comma, "Unity Transport, LLC".

    I tried using this below: 

    = if [Drop 1 Country]="USA" then Text.Range([drop1],Text.End([drop1],10,5)) else "**" 

    Using that statement just gives me an Error in every row or an "**".  This seems simple, but I am not figuring this out.  Any help would be greatly appreciated!! 

    Wednesday, March 13, 2019 5:50 PM

Answers

  • try this formula for a new column:

    =List.Reverse(Text.Split([your_column], ", ")){1}


    Maxim Zelensky Excel Inside

    • Proposed as answer by Colin Banfield Wednesday, March 13, 2019 6:53 PM
    • Marked as answer by Creush Wednesday, March 13, 2019 7:00 PM
    Wednesday, March 13, 2019 6:10 PM

All replies

  • try this formula for a new column:

    =List.Reverse(Text.Split([your_column], ", ")){1}


    Maxim Zelensky Excel Inside

    • Proposed as answer by Colin Banfield Wednesday, March 13, 2019 6:53 PM
    • Marked as answer by Creush Wednesday, March 13, 2019 7:00 PM
    Wednesday, March 13, 2019 6:10 PM
  • Hi Maxim,

    Why was it necessary to reverse the list before returning the index?

    Wednesday, March 13, 2019 8:20 PM
  • Hi Colin,

    it is for the cases where there could be or couldn't be an additional comma(s) in the company name or other fields:

    Unity Transport, LLC, Kearney, MO, 64060, USA
    Eat, Pray, Love Inc, iMDB, AZ, 11111, USA
    Ministry Of Sound, Toronto, XZ, 00000, CAN

    if the count of commas in the string always the same and the position of index is the same, there's obviously no need to reverse, just take {4} for example:

    =Text.Split([your_column], ", ")){4}

    I think you know it already :)




    Maxim Zelensky Excel Inside

    Thursday, March 14, 2019 10:07 AM