Conversation Re: Autofill sequentially from the right-hand number in a cell in Excel
https://techcommunity.microsoft.com/t5/excel/autofill-sequentially-from-the-right-hand-number-in-a-cell/m-p/178150#M5012
<P>You are welcome.</P>
<P> </P>
<P>Thanks for the feedback.</P>Sat, 31 Mar 2018 22:03:41 GMTJamil Mohammad2018-03-31T22:03:41ZAutofill sequentially from the right-hand number in a cell
https://techcommunity.microsoft.com/t5/excel/autofill-sequentially-from-the-right-hand-number-in-a-cell/m-p/176757#M4935
<P>Using Excel 2011 for Mac.</P>
<P> </P>
<P>I have a column that needs to be renamed in sequential order. The content are the names of photographs. The text in a cell goes as followsâ€¦<BR /><BR />PROJECTNUMBER PROJECTNAME PHOTONUMBER<BR /><BR />So a single cell looks like thisâ€¦<BR /><BR />1234 ANTONIO 001<BR /><BR />I need keep the PROJECTNUMBER the same and have the number on the right-hand side, PHOTONUMBER, go sequentially from 001 to 1049. <BR /><BR />When I drag the cell down from the right-hand corner, the PROJECTNUMBER goes sequentially rather than the PHOTONUMBER.<BR /><BR />How can I get the PHOTONUMBER to go sequentially?</P>
<P> </P>
<P>Thanks!</P>Wed, 28 Mar 2018 03:29:50 GMThttps://techcommunity.microsoft.com/t5/excel/autofill-sequentially-from-the-right-hand-number-in-a-cell/m-p/176757#M4935Lady Audio2018-03-28T03:29:50ZRe: Autofill sequentially from the right-hand number in a cell
https://techcommunity.microsoft.com/t5/excel/autofill-sequentially-from-the-right-hand-number-in-a-cell/m-p/176917#M4937
<P>I don't know about Excel on a Mac but this is how I would do this on a PC ...</P>
<P>in an adjacent column type '001 in the row for the first photo number then copy this down with the fill option to 1049, then position the cursor in the column you want to fill and click on the Fill button in the ribbon and select flash fill. Then you can remove the adjacent column detail.</P>Wed, 28 Mar 2018 12:52:43 GMThttps://techcommunity.microsoft.com/t5/excel/autofill-sequentially-from-the-right-hand-number-in-a-cell/m-p/176917#M4937stephen zabiela2018-03-28T12:52:43ZRe: Autofill sequentially from the right-hand number in a cell
https://techcommunity.microsoft.com/t5/excel/autofill-sequentially-from-the-right-hand-number-in-a-cell/m-p/177179#M4951
<P>Thank you for your reply. My Fill button doesn't have the flash fill option.</P>
<P> </P>
<P>I tried creating a column starting at 001 and ending on 1049 and then merging the cells, but I get a message saying "The selection contains multiple data values. Merging into one cell will keep the upper-left most data only."</P>
<P> </P>
<P>If I click OK it removes rather than append the values from the right column into the left column.</P>Wed, 28 Mar 2018 22:27:29 GMThttps://techcommunity.microsoft.com/t5/excel/autofill-sequentially-from-the-right-hand-number-in-a-cell/m-p/177179#M4951Lady Audio2018-03-28T22:27:29ZRe: Autofill sequentially from the right-hand number in a cell
https://techcommunity.microsoft.com/t5/excel/autofill-sequentially-from-the-right-hand-number-in-a-cell/m-p/177191#M4953
<P>You just need a helper column and my formula :)</P>
<P> </P>
<P>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.</P>
<P> </P>
<P>please see attached workbook example. </P>
<P> </P>Wed, 28 Mar 2018 22:52:39 GMThttps://techcommunity.microsoft.com/t5/excel/autofill-sequentially-from-the-right-hand-number-in-a-cell/m-p/177191#M4953Jamil Mohammad2018-03-28T22:52:39ZRe: Autofill sequentially from the right-hand number in a cell
https://techcommunity.microsoft.com/t5/excel/autofill-sequentially-from-the-right-hand-number-in-a-cell/m-p/178143#M5010
<P>Hello,</P>
<P> </P>
<P>Thank you for your help. I was able to complete the task using the clues Jamil Mohammad and Stephen Zabiela gave me.</P>
<P> </P>
<P>First I put the <SPAN style="color: #333333; font-family: SegoeUI, Lato, 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 300; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: #ffffff; text-decoration-style: initial; text-decoration-color: initial; display: inline !important; float: none;">PROJECTNUMBER PROJECTNAME</SPAN> in the first column, A. I also included a space so I didn't need it in the formula. </P>
<P> </P>
<P>Then I put the PHOTONUMBER in the second column, B, and dragged the numbers down to 1049.</P>
<P> </P>
<P>Then I alt/option dragged column A down to 1049.</P>
<P> </P>
<P>The function I used in column C was CONCATENATE.</P>
<P> </P>
<P>=CONCATENATE(A1, "00", B1)</P>
<P> </P>
<P>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.</P>
<P> </P>
<P>=CONCATENATE(A10, "0", B10)</P>
<P> </P>
<P>=CONCATENATE(A10, B10)</P>
<P> </P>
<P>I wouldn't have figured this out without the clues you both gave me. Thank you.</P>
<P> </P>Sat, 31 Mar 2018 21:43:54 GMThttps://techcommunity.microsoft.com/t5/excel/autofill-sequentially-from-the-right-hand-number-in-a-cell/m-p/178143#M5010Lady Audio2018-03-31T21:43:54ZRe: Autofill sequentially from the right-hand number in a cell
https://techcommunity.microsoft.com/t5/excel/autofill-sequentially-from-the-right-hand-number-in-a-cell/m-p/178150#M5012
<P>You are welcome.</P>
<P> </P>
<P>Thanks for the feedback.</P>Sat, 31 Mar 2018 22:03:41 GMThttps://techcommunity.microsoft.com/t5/excel/autofill-sequentially-from-the-right-hand-number-in-a-cell/m-p/178150#M5012Jamil Mohammad2018-03-31T22:03:41ZRe: Autofill sequentially from the right-hand number in a cell
https://techcommunity.microsoft.com/t5/excel/autofill-sequentially-from-the-right-hand-number-in-a-cell/m-p/178236#M5017
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].<BR />Sun, 01 Apr 2018 13:17:18 GMThttps://techcommunity.microsoft.com/t5/excel/autofill-sequentially-from-the-right-hand-number-in-a-cell/m-p/178236#M5017stephen zabiela2018-04-01T13:17:18Z