Continuing Numbers with Text

[g4eights]

Hi Gurus,

I am having some problem trying to continue the numbes instead of typing one by one, I am trying to do this,
ISRC-MY-AC1-12-00636
ISRC-MY-AC1-12-00637
ISRC-MY-AC1-12-00638
ISRC-MY-AC1-12-00639
ISRC-MY-AC1-12-00640

a formula which I can copy and paste would be very helpful to copy and paste once the numbers has ran out.

Thank you.

Cheers,
Eights

[Sisyphus]

Hi,

The easiest way of doing this is to format the cell in which you want the number:
Right-click / Format cells and select the Numbers tab.
Click on "Custom" (last item in the list) after making sure the current format isn't Text.
Enter this format mask

Code: Select all

"ISRC-MY-AC1-12-"0000


Click OK

The mask holds 2 instructions. Firstly, it will show any number you enter with at least 5 digits, filling missing digits with leading zeros. If you enter 1 it will appear as 00001. The other instruction is to precede the number by the text between the quotation marks. So, you enter 1 and you get ISRC-MY-AC1-12-00001. Be careful not to enter a blank space between the text and the zeros because it will be included in the output.

If you need to convert the format to real text, repeated in each cell, later you may try Copy / Paste Special. I somehow doubt that it will do what you want, however, because the cell never holds more than the number you enter. In fact, you can automate sequential numbering by counting, like =A10 + 1. written in A11 the cell would show 11 (or ISRC-MY-AC1-12-00011), and you can copy the formula to subsequent rows. So, you never need to write any number at all.

If you do need to convert back and Copy/Paste Special won't do, use this formula. You can use it on any cell holding a number, even if it is formatted as described above in which case the output will look the same as the input but isn't.

Code: Select all

= CONCATENATE("ISRC-MY-AC1-12-",TEXT(A10, "0000"))


The result can definitely be converted to real text using Copy / Paste Special and Values.
What are you going to do with all the time you are about to save?
How about welcoming the New Year with a smile?
Happy New Year!