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

Altair Forum User

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

Altair Forum User

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.