cancel
Showing results for 
Search instead for 
Did you mean: 

adding a number to text problem

oskarjennische1
Level 3
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
------------------------------
7 REPLIES 7

MichealCharron
Level 8
@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
------------------------------
Micheal Charron
RBC
Toronto, Ontario
Canada

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

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

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
------------------------------

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
------------------------------

I'm not getting an error.
28470.png


------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------
Micheal Charron
RBC
Toronto, Ontario
Canada

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


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

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

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