1 Reply Latest reply: Sep 7, 2017 1:35 PM by Steve Caiels RSS

    How do I pull out an alpha prefix from an alpha-numeric chr field?

    Diane Miller

      I have a character field (client account number) that is made up of an alphabetic prefix followed by numbers. Since I need to leave the client account number intact, I trying to find how to create a calculated field that pulls in only the alpha prefix. The length of the alpha prefix varies from 2 to 5 characters.

       

      Below are a few examples:

      DOT032440

      PC14888

      RHEA789442

      LAWNV7894457

       

      I would appreciate any help available. Thanks,

      Diane

        • Re: How do I pull out an alpha prefix from an alpha-numeric chr field?
          Steve Caiels

          Hi Dianne,

           

          If the alpha characters are always at the front, then a combination of the Extract and Strip function so do what you need.

           

          Strip("ABCDEFGHIJKLMNOPQRSTUVWXYZ",[Client Account Number]) will result in the following values:

           

          032440

          14888

          789442

          7894457

           

          Embedding the strip within the following extract function will extract from the start of the string to whatever the strip function returns.

           

          Extract([Client Account Number],””, Strip("ABCDEFGHIJKLMNOPQRSTUVWXYZ",[Client Account Number])).

           

          There will no doubt be a more concise RegEx expression that will offer greater flexibility if this doesn’t do what you need.  Please let me know and we can look into that if required.

           

          Regards,

          Steve.