string function exlude exceptions

[#1]

Hi,

Please can some one help.

I am struggling to think of the string function that will allow the following.

I have a list of Customers e.g.

SPMC12345

SPDD12345

SPF12345

SPBB12345

600001

600002

PP4567

Basically all I what to do is take the first 4 letters but exclude the 600001 & 600002 and PP4567 so I end up with w list such as:

SPMC

SPDD

SPF

SPBB

The expression I have used at the moment is:

left([CO Customer No], index([CO Customer No],'0') -1)  

This gives me the following:

SPMC

SPDD

SPF

SPBB

6

PP

But I want to exclude the 6 and PP?

I cannot use a where statement as I do not wish to exclude them altogether just from this calculated field.

Any help welcomed.

Thanks,

Nikki

[Gysbert_Wassenaar]

if(purgechar(left([CO Customer No],4),'0123456789')<>'PP',purgechar(left([CO Customer No],4),'0123456789'))

[#3 · Accepted solution]

hi,

try the follwoing script

if(wildmatch(Fieldname,'600001','600002','PP4567')=0, purgechar ( Fieldname,'0123456789' )) as NEW_FIELD_NAME

[CELAMBARASAN]

If(not Match(left([CO Customer No], index([CO Customer No],'0') -1)  ,'6', 'PP'), left([CO Customer No], index([CO Customer No],'0') -1) )

OR

if(not wildmatch([CO Customer No], '6*', 'PP*'), left([CO Customer No], index([CO Customer No],'0') -1) )

this might helps