Generate serial numbers with decimals
- Thread starter nagovind
- Start date
Yes it can but the one I come up with has a limitation of 1000 records (i don't know how many you really need).
my solution is to use up to 4 columns (columns A, B, C for formulae and column D for result.
cells A1, B1 and C1 showing as 0.
cell D1 formula: =A1&"."&B1&"."&C1
Cell A2: =IF(B1=9,IF(C1=9,A1+1,A1),A1)
Cell B2: =RIGHT(IF(C1=9,B1+1,IF(B1*C1=81,0,B1)),1)*1
Cell C2: =IF(C1+1>9,0,C1+1)
Auto copy formulae from rows 2 on columns A to C and row 1 from column D downwards.
You are good up to 1000 records.
my solution is to use up to 4 columns (columns A, B, C for formulae and column D for result.
cells A1, B1 and C1 showing as 0.
cell D1 formula: =A1&"."&B1&"."&C1
Cell A2: =IF(B1=9,IF(C1=9,A1+1,A1),A1)
Cell B2: =RIGHT(IF(C1=9,B1+1,IF(B1*C1=81,0,B1)),1)*1
Cell C2: =IF(C1+1>9,0,C1+1)
Auto copy formulae from rows 2 on columns A to C and row 1 from column D downwards.
You are good up to 1000 records.
That's a custom number format. It defines a 3 digit number (w/ leading zeros if applicable) with a apostrophe between each digit (purely cosmetic). The SUBSTITUTE function then replaces all those apostrophes with periods (OP's desired request).
You could technically use any cosmetic feature, such as this:
=SUBSTITUTE(TEXT(A1,"0f0f0"),"f",".")
You could technically use any cosmetic feature, such as this:
=SUBSTITUTE(TEXT(A1,"0f0f0"),"f",".")
Please correct me if I misunderstand. If A1 has a figure 456
In Text(A1,"0f0f0"), the zero (0) is the digit? So the first zero from the left represents 4, middle zero represents 5 and the zero on the right represents 6, whilst at the same time Excel added a "f" (or whatever character we use) among the figures to show up as "4f5f6", right?
then the substitute part have the "f" replaced by ".", am I correct in understanding this?
In Text(A1,"0f0f0"), the zero (0) is the digit? So the first zero from the left represents 4, middle zero represents 5 and the zero on the right represents 6, whilst at the same time Excel added a "f" (or whatever character we use) among the figures to show up as "4f5f6", right?
then the substitute part have the "f" replaced by ".", am I correct in understanding this?
Pretty much correct. Technically, the controlling factor is decimal place(if defined).
The number 456 with custom format "0f.0f0f0" would just look like 456f.0f0f0
Furthermore, by placing a 0, we're stating that XL must display that digit. If it's optional, you can use the # sign. In our same example, format of "0f.#f#f#" would only show:
456.ff
Hope that helps!
The number 456 with custom format "0f.0f0f0" would just look like 456f.0f0f0
Furthermore, by placing a 0, we're stating that XL must display that digit. If it's optional, you can use the # sign. In our same example, format of "0f.#f#f#" would only show:
456.ff
Hope that helps!