MacMost Q&A Forum • View All Forum QuestionsAsk a Question

How Do I Combine Text and Numbers, and Then Make the Numbers Incremental?

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

Comments: 4 Responses to “How Do I Combine Text and Numbers, and Then Make the Numbers Incremental?”

    2 years ago

    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

    Gali Nirmal Kumar
    2 years ago

    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.

    2 years ago

    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

    Gali Nirmal Kumar
    2 years ago

    Thanks, Gary. The updated RIGHT function resolves the problem.

Comments Closed.