Remove leading zeros from a function

I have a spreadsheet that prompts the user to enter an account number. The account number is in the format of C0001234. I am using a function to return the last 4 characters which are then passed to a query. I'm using the "right" function to get these last 4 characters. This works fine except when there are leading zeros in the result. For example the account number may be C0000C60. The function returns "0C60" and I need it to only return "C60". Is there any way to accomplish this? If the original account number was C0000020. I would want it to just return "20".

Originally Posted by elbmag

I have a spreadsheet that prompts the user to enter an account number. The account number is in the format of C0001234. I am using a function to return the last 4 characters which are then passed to a query. I'm using the "right" function to get these last 4 characters. This works fine except when there are leading zeros in the result. For example the account number may be C0000C60. The function returns "0C60" and I need it to only return "C60". Is there any way to accomplish this? If the original account number was C0000020. I would want it to just return "20".

Try this:

=IF(ISNUMBER((SUBSTITUTE(MID(B6,2,4),0,"")&RIGHT(B6,3))+0),(SUBSTITUTE(MID(B6,2,4),0,"")&RIGHT(B6,3))+0,SUBSTITUTE(MID(B6,2,4),0,"")&RIGHT(B6,3))

Enter

Hope this helps.

I tried that function but it didn't seem to work for me.

I did however get this one to accomplish what I needed. I took the last four characters from the original account number and then applied the following formula:

=IF(LEFT(A1,3)="000",RIGHT(A1,1),IF(LEFT(A1,2)="00",RIGHT(A1,2),IF(LEFT(A1,1)="0",RIGHT(A1,3),LEFT(A1,4))))

Thank you for your help.

I'm sorry Snasui I tested your function using the wrong cell (not B6). Your function did work-the only reason it didn't the first time was my mistake.

I really appreciate your help.