adding a number to text problem

[oskarjennische1]

hi. i have a string that looks like "AB00000001" that i want to add a +1 to. 


by using Trim("AB" & ToNumber(Right[number];8))+1) i get AB2, but i miss all the zeros in between. how do i solve this? i have been trying to solve this for a very long time..

thanks in advice

------------------------------
oskar jennische
------------------------------

[MichealCharron]

@OskarJennische

You can use an old padding trick to accomplish this where there is no padding or formatting functions available.. With the following formula:

Left([number],2) & Right("0000000" & ToNumber(Right([number],8))+1,8)​

What it does is:

• grabs the first 2 letters (I know you hardcoded the "AB", I just made it a little more dynamic)
• grabs the last 8 digits, converts it to a number and adds 1 to it (as you did)
• concatenates 7 zeros to the start of your new number
• grabs the last 8 digits removing extraneous zeros
• concatenates the 2 letters to the zero padded new number

​

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

[AndrzejFidos]

Can it happen that you exceed the number 9 and with the next calculation loose one zero and etc. ?

------------------------------
Andrzej Fidos
------------------------------

[oskarjennische1]

thanks. i tried this but i get this error: "incorrect number of parameters to funktion 'Right'"
it seems like it is complaining on the outher right function

------------------------------
oskar jennische
------------------------------

[oskarjennische1]

yes, that will happen. the program  will add +1 for each customer that is created. it will be upwards of hundreds of customers added at some later point

------------------------------
oskar jennische
------------------------------

[MichealCharron]

I'm not getting an error.

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

[oskarjennische1]

i found the problem. i cannot use comma sign "," i need to be using ";" for it to work 

------------------------------
oskar jennische
------------------------------

[AndrzejFidos]

Depending on local settings you need to either use ";" or ","

------------------------------
Andrzej Fidos
------------------------------