GL Account Codes missing leading zeros with diff # of characters by unit

[RMB33]

I am trying to create a 3 yr GL Code to Tax Master Code for the group with a unique line for each Unit and GL Code. However, I am facing the following challenges:

- Duplicated GL Account Codes on separate line items bc certain years have GL codes are presented differently with missing leading or trailing spaces
- Units have all different # of characters for GL Codes for different units (for example, some have 4, some of 6, others have 8)

I looked into RegEx, fuzzymatch tools but wondering if anyone has seen this before and have a solution. I used the unique tool as well but because of missing leading zeros and different # of characters in account codes, I am having difficulties coming up with solution.

[Christina_H · Accepted solution]

Try using PadLeft([Account Code],8,'0') to make all account codes the same length

[OllieClarke · Accepted solution]

Hi @RMB33 

How's this:

Ollie

[RMB33]

@OllieClarke Perfect solution!!!!  Thank you so much.  all simple tools too! 

[RMB33]

@Christina_H   Thank you!  this solution worked as well when I added @OllieClarke tools after the formula tool with PadLeft formula!