Getting Rid of Leading Zeroes in Character Field?
Altair EmployeeHello all,
I am using Monarch 13 Classic to pull contract IDs from one report and match it to another with $ amounts. The problem is, on one report there are leading zeroes before some of the IDs and in the other report there isn't so when I go into DPS to match the tables together by Contract ID, it won't match them since it reads it as different. (In my example below it would not match 00000003210 from A to 3210 from B for instance). I cannot change it to a number field because some contract IDs are not numeric and start with letters. Ex:
| Report A Contract ID | Amount | Report B Contract ID | Amount |
|---|---|---|---|
| AB0056789 | 100 | AB0056789 | 100 |
| X005324675 | 200 | X005324675 | 200 |
| 0000003210 | 300 | 3210 | 300 |
| 0000023456 | 400 | 23456 | 400 |
| 1115678 | 500 | 1115678 | 500 |
Where Report A is formatted differently than Report B and they are obviously not yet in the same table. I have tried creating a few different Filter Formulas to get rid of the leading 0's but they always return an error of some kind. Here is an example of one that I have tried:
IF(LEFT([Report A ContractID],1)=0,Val([Report A ContractID]),[Report A ContractID])
Any help with making a formula for this or if I am just missing something simpler, as I am new to this program and mostly self taught, please let me know.