Forum Discussion

mdrammeh's avatar
mdrammeh
Icon for Helper III rankHelper III
9 years ago
Solved

Conditional format for Alpha-numerical Columns

I have a column with alphanumerical values identifying the difference between INTERNAL versus EXTERNAL contact. A B MUST1234 EXTERNAL HAVENO INTERNAL NOHAV12 EXTERNAL ...
Need Help
Show and Tell
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago
    if List.ContainsAny(Text.ToList([A]), {"1","2","3","4","5","6","7","8","9","0"}) or [A] = "GLAMRO" then "EXTERNAL" else "INTERNAL"
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
9 years ago
if List.ContainsAny(Text.ToList([A]), {1,2,3,4,5,6,7,8,9,0}) then "EXTERNAL" else "INTERNAL"
mdrammeh's avatar
mdrammeh
Icon for Helper III rankHelper III
9 years ago

I must have missed something in the formula that it's returning an error. Also, the result only returned for "Internal" even though I had values that include a text in the description. 

 

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    mdrammeh

     

    Hi, just add a " " en each number

     

    if List.ContainsAny(Text.ToList([TEXTFIELD]), {"1","2","3","4","5","6","7","8","9","0"}) then "EXTERNAL" else "INTERNAL"
    • mdrammeh's avatar
      mdrammeh
      Icon for Helper III rankHelper III
      9 years ago

      It worked perfect except the error message. The column with the values also had blank rows that was not being picked up by the formula. Is there a workaround? 

      • Vvelarde's avatar
        Vvelarde
        Icon for Community Champion rankCommunity Champion
        9 years ago

        mdrammeh

         

        The formula works also with Blank Rows

         

        I'll try first to made a Trim and Clean to the Column to delete other characters.