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:






      I would appreciate any help available. Thanks,


        • 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:







          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.