topic RE: adding a number to text problem in Product Forum

adding a number to text problem

oskarjennische1 — Wed, 06 Jul 2022 11:08:00 GMT

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

RE: adding a number to text problem

MichealCharron — Wed, 06 Jul 2022 11:58:00 GMT

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

RE: adding a number to text problem

AndrzejFidos — Wed, 06 Jul 2022 12:03:00 GMT

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

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

RE: adding a number to text problem

oskarjennische1 — Wed, 06 Jul 2022 12:22:00 GMT

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

RE: adding a number to text problem

oskarjennische1 — Wed, 06 Jul 2022 12:23:00 GMT

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

RE: adding a number to text problem

MichealCharron — Wed, 06 Jul 2022 12:51:00 GMT

I'm not getting an error.

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

RE: adding a number to text problem

oskarjennische1 — Wed, 06 Jul 2022 20:00:00 GMT

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

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

RE: adding a number to text problem

AndrzejFidos — Wed, 06 Jul 2022 20:06:00 GMT

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

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