1

I need to create a unique ID number of each new project in the follow format 00-0000-000000 where the middle four digits are a number that increments by one for each new project. I have a list called variables with a column called proj_number which holds a single entry which is incremented by workflow each time a new project site is requested. Here is my issue:

In order to increment the number it must be a number field which has compulsory formatting and won't stay in a four digit (0000) format so I need either

  1. A way to increment a four digit string by 1 using a work flow or
  2. A way of adding 00s to the number column so that is always four digits long.

any thoughts gratefully received.

1

KGlasier's approach works perfectly in my test list. enter image description here

2

You could take a look at this article. It talks about padding numbers with leading zeroes which you may be able to modify with a calculated column.

Below is the general idea, slightly modified to your situation.

=CONCATENATE(
    [Column1],
    "-",
    REPT("0",4-LEN([Column2])),
    [Column2],
    "-",
    [Column3]
)

Column1 is the field of your first two characters. This could also be some formula if you need to derive it from a date field or some such.

Separate that with a dash. Now Column2 is the four digit integer you're looking for. We repeat the "0" string a few times (equal to 4 - length of your integer column), to add the necessary padding. Then add Column2 to the end of it.

Separate that with a dash and add your last 6-character string to the end.

  • works perfectly, thanks for the suggestion :) – JonS Jul 15 at 8:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.