Excel Magic Trick 1473: Sequential Number Incrementor for Just The Middle Number: AA-0009-17

Learn about how to create a formula to create a product code where the middle part of the code must increment as it is copied down a column. See the Functions ROWS and TEXT. ROWS is good for incrementing numbers inside formulas – it is better than the ROW function because it allows structural changed to the spreadsheet whereas ROW Function does not. Also see the TEXT Function to format numbers with Custom Number Formatting and convert them to text.

[hrprk]

My you're a wizard. No one does it like you do. Grateful for all your help!

[qasimawan]

This is fantastic! I now understand the "Expandable Range/Array" concept! Thanks for everything you do!

[RobMichaels]

I have often wanted to add custom number formatting inside a text string (for example a graph title which contains a dynamic number) and now I know how to do it. Thanks for all your useful tips and have a happy new year!

[BenDieselBasicAutomotive]

Thanks again.... for a very significant learning.

[zhewaling]

Exactly, this is what I am looking for. Thank god!

[alexandergonzalojimenezcas]

thanks for helping me I have a question how can I do to get a code we say R002, adding up R001+1

[muhammadshiraz]

Very Helpful to understand Your explanation method is Mind Blowing

[cococa]

This is wonderful. Does it let me sort and not lose the number order? When I'm sorting row() stops being 1, 2, 3 and takes on the row it's on 4, 22, 5. From now on it's rows. Thanks

[KingdomCtzen]

YES! I need to comment and like more. Your videos are a lifesaver and my "secret sauce" :) Thank you!

[vida]

Good explanation for the difference between ROW and ROWS

[markrobinson]

Thank you so much! Just what I wanted. I am using Google Sheets and the formula is the same! Saved me so much time not having top type it all out! 🙂

[cindydemill]

If I am starting at 600 and need it to continue thru to 900 what would I change?

[haniqadoos]

Thanks for sharing such info, I was looking for it for quite sometime

[GraceChua-jx]

Helped me a lot! Also, do you have a tutorial where in if the other row is sorted, the sequence "code" goes with the sorted column? Thank you!

[lafinesse]

I MUST SAY .. THANK YOU -- I GOT ALOT FROM YOU

[roberthungerford]

Thank you this video is great, this also works if you swap out row function for column function. Then it works across instead of down. I'm making layouts for data patch panels and wanted to know what you could do to make sequential numbers across and then have the next row down pick up where the last row ended.

[srikanhaglobaltrademart]

Thank you so Much for this video, I was stuck from quite some time. Just small query how we can add this format in auto update while creating multiple invoice?

[johanvandervorst]

Another great trick from the great Mike Girvin!!

[stevenhall]

Brilliantly clear explanation. Thanks very much!

[polygonmap]

Thank you, Mike! This saves me a great time researching.
I once learned how to autofill sequential number only if we press 'Enter' in the row besides the cell but now I forgot how. Do you happen to know how to do it?