Trimming 'n' number of characters from a string where 'n' is variable between records

[jschuck]

Hi all, 

Hoping someone can help with this.  We have an automated process that is throwing errors can combining records.  I'm tying to implement a manual fix until our dev team can fix the automated process, but running into a slight hiccup.

Within the output file, the last field is numerical while the first field is string (combination of numbers and letters).  The error is causing the the last field in a record to be truncated and the following record to be appended to the error record, concatenating the last field and the first record from the next record.

I've managed to work out a process that fixes all of it except one piece - the first field record that erroneously is concatenated.

Ideally, the first record would look something like 'BOOT37-2432', however, when the error occurs, the record then becomes something like '8842BOOT37-2432' or '92483BOOT37-2432' or '228BOOT37-2432'.

Normally, I would use a formula like Right([first_field], Length([first_field])-4) to solve if there were consistently 4 characters concatenated to the value, but since there are 3, 4, 5, or even 6, not sure there is a relatively simple way to solve this.

Any advice would be appreciated.

Thanks!

[JohnJPS · Accepted solution]

I believe if you do a ToNumber([FirstField]) it will give you the numeric digits... then utilize that knowledge to remove them; e.g. the number of digits should be: Length(ToString(ToNumber([fieldName])))

[jschuck]

Thank you! Just ran it through a couple of bad files and looks like the issue is resolved.