Log In
Register
Home
January 10, 2020
Offline
Hello,
Could you please help me on this?
I need to add a suffix of numbers to a number in a particular cell and the sequence should be for a quantity specified in another cell.
Example:
Cell A1 contains number - 12345
Cell B1 contains number - 5
Suffix sequence - 01, 02 ~~~ until the count reaches B1 cell quantity
In another cells I need sequence numbers as 1234501, 1234502, 1234503, 1234504, 1234505
Request your kind help.
Thanks,
Mohith
See Answer
Trusted Members
December 20, 2019
Offline
Try this
=IF(ROWS($A$1:C1)>$B$1,"",$A$1&IF(ROWS($A$1:C1)<10,"0","")&ROWS($A$1:C1))
Rows with an expanding range - this turns formula off if the count is more than B1
IF(ROWS($A$1:A1)>$B$1
This joins A1 with a 0 if the suffix is less than 10
$A$1&IF(ROWS($A$1:C1)<10,"0","")
This joins the suffix incrementing number
ROWS($A$1:A1))
Let me know if it works for you
January 10, 2020
Offline
Hello,
Thanks for your suggestion. I tried with the formula suggested and its working.
But the suffix incrementing to the count of B1 - 5 is not working.
The suffix increment should be for the count of 5 like 1234501, 1234502, 1234503, 1234504, 1234505. These 5 values should be in different individual cells.
Could you please help.
Trusted Members
December 20, 2019
Offline
Seems to work for me - did you lock the first part of rows with the $ sign?
See attached
Its always better to add a work book with some data so we can see what is going on
Purfleet
Answers Post
1 Guest(s)
