Little Syntax trouble shorten names

[#1]

Hello you all,

Situation: I've got 3 regions and I want to shorten the region names. Now they are called region losser, region almelo and region enschede and I want to get rid of the word region. My script only works for 1 region. Can someone help me with the correct syntax?

IF

(Trim([DTZC Region])= 'Region Losser', 'Losser',[DTZC Region]) , IF(Trim([DTZC Region])= 'Region Almelo', 'Almelo',[DTZC Region]) , IF(Trim([DTZC Region])= 'Region Enschede', 'Enschede',[DTZC Region]) as [DTZC Region],

[#2]

Check out 'map' and 'mapping' in the help.

Regards,

Gordon

[jonathandienst · Accepted solution]

Richard

If you don't want to go the mapping approach (which would be better for many regions, but not so critical for 3), then use this expression:

If (Trim([DTZC Region])= 'Region Losser', 'Losser',
If(Trim([DTZC Region])= 'Region Almelo', 'Almelo',
If(Trim([DTZC Region])= 'Region Enschede', 'Enschede','')))

OR even

Replace([DTZC Region], 'Region ', '')

Jonathan

[#4]

Thx Jonathan, this works perfect!

[#5]

Got another one. It seems that I do not have 3 Regions but 4. The fourth is called Amsterdam (without the word Region). With the formula you gave me, I now only see Region Losser, Almelo and Enschede. The fouth one is missing. Is there a way to get it without naming it into the formula? I do not want to name it because in the futuren there will be more regions without the word Region.

thx in advance

[jonathandienst]

Richard

Use this:

If (Trim([DTZC Region])= 'Region Losser', 'Losser',
If(Trim([DTZC Region])= 'Region Almelo', 'Almelo',
If(Trim([DTZC Region])= 'Region Enschede', 'Enschede',[DTZC Region])))

Jonathan

[hector]

Hi, if the field value is always "Region XXXX" there are other options

1. SubField(FIELD,' ',2) as [DTZC Region]
2. right(FIELD,Index(FIELD,' ',1)-1)

just to expand the options 😉

rgds