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

Altair Forum User
Altair Forum User
Altair Employee
edited September 2017 in Community Q&A

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

Tagged:

Answers

  • Altair Forum User
    Altair Forum User
    Altair Employee
    edited September 2017

    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.

Categories