Arranging the field in a standard form

[nevilledhamsiri]

Hi, Members

I have following issues to be resolved. When I load excel data the code numbers both in Debtor code & Debtors can be not in the same format so I need to arrange them like below.

1st Issue (Example)

Correct format :1017-003

Incorrect format:1017003

I need to arrange the in correctly arranged code to be arranged in correct format.

2nd Issue(Example)

if any code in one of the above fields repeated together like 3086-001;54977, I need to remove the code of which length is equal to 5. Hence the code to be shown should be 3086-001. 

3rd issue

If any code in  one of the above fields has the length over 8, I need to flag it as CGI. I have already flag the codes based on the length as Bro & Ind but if I am to add the CGI under as key flag, let me know how I will expand the if statement in the script

May thanks in advance

Neville 

[sunny_talwar]

Attached sample is without any data, can you either reload it and attach it back or provide Book2.xlsx to be able to reload this with data?

[lockematthewp · Accepted solution]

=left(subfield([Debtor Code], ';', 1), 4) & '-' & right(subfield([Debtor Code], ';', 1), 3) as [Debtor Code]

and for the CGI part:

=if (len([Debtor Code])>8, 'CGI',

if ( … , 'Bro',

if( … , 'Ind',

'None'))) as [Key]

[nevilledhamsiri]

Please find the attachment

[sunny_talwar]

Based on the data provided... can you provide info on what is not correct in the below image?

[nevilledhamsiri]

Yes

I need the following

1050001 to be appeared as 1050-001

3021008 to be shown as 3021-008

Also I need to write a If statement in the script to rename the source from the debtors & the  debtor code like if the length of the Debtors or Debtor code =5 as  "IND", if the length is equal to 8 as "Broker" & if the length in debtor code or debtor >=10 as  CGIL 

Thanks

[nevilledhamsiri]

Can some one to help me out on this.

Thanks

Neville

[nevilledhamsiri]

Thanks, Lokematthewp

Helped your approach to build it to suit my requirement.

Neville