Hi Gary,
BACKGROUND:
Am working on a Numbers document where I have to combine ‘Text’ and ‘incremental Numbers’ for 1,000 entries.
EXPECTED OUTPUT:
Row 1, Column 1 – GNK (text)
Row 1, Column 2 – Science (text)
Row 1, Column 3 – GNK-0001-Science (text-number-text)
Row 2, Column 1 – GNK (text)
Row 2, Column 2 – Mathematics (text)
Row 2, Column 3 – GNK-0002-Mathematics (text-number-text)
…and so on…
I have used the function ‘Concatenate’ to get the result (GNK-0001-Science) as shown in Row 1, Column 3, where I have manually included the number.
PROBLEM:
When I apply the above ‘Concatenate’ function to autofill Row 2, Column 3, I get the result “GNK-0001-Mathematics”, instead of “GNK-0002-Mathematics”.
LIMITATION:
The number (0001) included in the ‘Concatenate’ function in Row 1, Column 3 should be independent and thus doesn’t have reference to any other cell.
QUERIES:
Q1. Is it possible to keep the number of digits to 4 always, with preceding 000’s (like 0001) in a ‘Concatenate’ function?
Q2. How to make the number in Q1 incremental (like 0001, 0002,….) in a concatenate function and at the same time follows the LIMITATION?
Thanks in advance.
—–
Gali Nirmal Kumar
Use ROW() to get the row number and subtract if you need to. So if row 1 is actually row 2 (you have a header row, right?) then ROW()-1 will get you 1, 2, 3, in cells C2, C3 and C4, and so on.
Use RIGHT to get the right 4 characters of some text. So this will give you numbers like 0001 and 0002:
RIGHT(10000+ROW()-1)
Then use & instead of CONCATENATE. So something like this:
A2 & "-" & RIGHT(10000+ROW()-1) & "-" & B2
Thanks, Gary.
While I am able to succesfully recreate 'incremental numbers', am still unable to get the four digits. Please the image https://freeimage.host/i/HYXvgja.
Gali: Ah, sorry, RIGHT needs a second parameter, the number of characters to use from the right side.
A2 & "-" & RIGHT(10000+ROW()-1,4) & "-" & B2
Thanks, Gary. The updated RIGHT function resolves the problem.