TRIMMING AND RETAINING DIGITS

[laaperex]

how to make this consistent with only 8 digits? tried everything from trim to substring to padleft and right. really need it asap :( it will make duplicates everytime i trim and retain.

11105202
11106000
11106002
12150001
41412003
41412005
41412006
41412803
5151550000
5151550001
5151550002
5151551500
5151552000
5151552002
5151552007
5160600000
5160600001
71830000
71850000

[ShankerV]

Hi @laaperex 

Please find the solution below. Is the output is what you are expecting right?

Shanker V

[laaperex]

hi. yes2. i'd like to try that solution.

[ShankerV]

Solution is in Regex tool, just change the highlighted as in yellow.

Hope your issue is resolved. Feel free to mark helpful answers as a solution, so that future users with the same issue can find them easier!!!!

[laaperex]

hi. thank you. it worked for some. however, i still got the same results, still have duplicates just like these. im really having a hard time.

[danilang]

Hi @laaperex 

It seems that your problem is that you're trying to take a unique 10 digit number and represent it as a unique 8 digit number.  Removing from the left or the right will always leave duplicates in some circumstances. 

Here are some questions that you should answer when trying to perform the transformation

1. Do the outputs have to resemble the inputs in any way?  Can you just take the unique inputs and renumber them sequentially from 000000001 to 99999999

2. Do the output numbers have to be mapped back to the input or can they just be random. 

3. Are the there any rules that apply to the input numbers?  For instance if these are bank account numbers, they could be formatted as AABBBCCCCC where AA is the institution, BBB is the branch and CCCCC is the account number.  You can find the maximum actual values from each section and possibly remove a digit from section B and section C

Dan