Forum Discussion
Autofill sequentially from the right-hand number in a cell
You just need a helper column and my formula :)
lets say your data is in Column A and you put this formula =A2&" "&TEXT(ROWS($A$2:A2),"000") in B2 and Drag down. you will get the sequential numbering as you described.
please see attached workbook example.
Hello,
Thank you for your help. I was able to complete the task using the clues Jamil Mohammad and Stephen Zabiela gave me.
First I put the PROJECTNUMBER PROJECTNAME in the first column, A. I also included a space so I didn't need it in the formula.
Then I put the PHOTONUMBER in the second column, B, and dragged the numbers down to 1049.
Then I alt/option dragged column A down to 1049.
The function I used in column C was CONCATENATE.
=CONCATENATE(A1, "00", B1)
On the way down the column I changed the formula to have a single zero for the double digits and no zero for the triple digits.
=CONCATENATE(A10, "0", B10)
=CONCATENATE(A10, B10)
I wouldn't have figured this out without the clues you both gave me. Thank you.
- Thanks Lady Audio. It is a shame the flash fill did not work for you as it involved no formulae at all. If you wanted to avoid the formula change where you hit photo number 1000 and the need for the leading 0 is no longer, you could do it this way. Type ' 000 in the first row in column B then drag and fill down ... if Mac version is the same as Windows then this will give you a list of text values from 0001 to 1049 preceded with a space so you can then just concatenate column A and B values. [Jamil's formula achieved this using the TEXT function].
You are welcome.
Thanks for the feedback.
