8 Replies Latest reply: May 26, 2018 12:02 PM by Steve Br. RSS

    Match() and field with text and numbers

    Steve Br.

      Hi guys,

       

      I have a field in my data that has text and number values:

       

      Group
      22
      AB2
      250
      FD

       

      It looks like some of the numbers are not interpret correctly, so that my match function does not work as expected:

       

      Using IF(MATCH(Group,'22','AB2','250','FD'), 'East') AS Location returns the following:


      LocationGroup
      East22
      -22
      EastAB2
      East250
      -250
      EastFD

       

      Then I tried IF(MATCH(NUM(Group),'22','AB2','250','FD'), 'East') AS Location. This works for the numbers but then the text fields get lost:

       

      LocationGroup
      East22
      -AB2
      East250
      -FD

       

      IF(MATCH(TEXT(Group),'22','AB2','250','FD'), 'East') AS Location returns the same as the first example:

       

      LocationGroup
      East22
      -22
      EastAB2
      East250
      -250
      EastFD


      As you can expect, this is the desired outcome:

       

      LocationGroup
      East22
      EastAB2
      East250
      EastFD

       

      Can somebody please help me here? Thanks!