adding a number to text problem
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
06-07-22 12:08 PM
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
------------------------------
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
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
06-07-22 12:58 PM
@OskarJennische
You can use an old padding trick to accomplish this where there is no padding or formatting functions available.. With the following formula:
------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------
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
RBC
Toronto, Ontario
Canada
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
06-07-22 01:03 PM
Can it happen that you exceed the number 9 and with the next calculation loose one zero and etc. ?
------------------------------
Andrzej Fidos
------------------------------
------------------------------
Andrzej Fidos
------------------------------
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
06-07-22 01:22 PM
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
------------------------------
it seems like it is complaining on the outher right function
------------------------------
oskar jennische
------------------------------
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
06-07-22 01:23 PM
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
------------------------------
------------------------------
oskar jennische
------------------------------
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
06-07-22 01:51 PM
I'm not getting an error.
------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------
------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------
Micheal Charron
RBC
Toronto, Ontario
Canada
RBC
Toronto, Ontario
Canada
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
06-07-22 09:00 PM
i found the problem. i cannot use comma sign "," i need to be using ";" for it to work
------------------------------
oskar jennische
------------------------------
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
06-07-22 09:06 PM
Depending on local settings you need to either use ";" or ","
------------------------------
Andrzej Fidos
------------------------------
------------------------------
Andrzej Fidos
------------------------------