Increasing a number in the center of a cell

**lightsandsirens5** · 11-15-2014, 03:18 PM

Hello all,

I have a question. I know how to make excel increase a number as you drag it down, but I need to do that in the middle of a string of numbers. It is easiest to explain it....

I am inventorying stuff and the code each unit is identified by follows the format: "A000AA" So an actual code would be G284XR. I cannot figure out how on earth to make it increase the three digit number in the center as I drag down. I would love to be able to type G200XR, then G201XR and drag it so it automatically increases.

Is there ANY way to do this? Because typing in every single one is going to kill me.

Thanks!

**etaf** · 11-15-2014, 03:30 PM

if the format is always the same - you could put
G200XR in cell A1
then in A2
=LEFT(A1,1)&MID(A1,2,LEN(A1)-3)+1&RIGHT(A1,2)

and copy down

and then Click on the column
Copy
Paste Special
Value
to replace the formula with a number

**pjwhitfield** · 11-15-2014, 03:31 PM

a quick cheat would be to drag a column (we'll assume A) down to get the list of numbers you want then in B1 put ="G" & A1 & XR", drag that down then copy that column, and paste it back in using Paste Special - Values.

then delete Column A

**TMS** · 11-15-2014, 03:33 PM

Or A1:

Formula:

Please Login or Register  to view this content.

and drag down

Regards, TMS

**protonLeah** · 11-15-2014, 03:34 PM

Are these numbers referenced anywhere else in the workbook? When does "G" turn to "H"? Etc.

**alansidman** · 11-15-2014, 03:34 PM

Assume G200Xr is in A1, then in A2 = =LEFT(A1,1) & (MID(A1,2,3)*1+1) & RIGHT(A1,2) and copy down

**AlKey** · 11-15-2014, 04:18 PM

Maybe like this

="G"&TEXT(ROW($A1),"000")&"XR"

Row\Col	A
1	G200XR
2	G201XR
3	G202XR
4	G203XR
5	G204XR
6	G205XR
7	G206XR
8	G207XR
9	G208XR
10	G209XR

**Tony Valko** · 11-15-2014, 04:42 PM

Yet another one...

A1 = G200XR

This formula entered in A2 and copied down:

=REPLACE(A1,2,3,200+ROWS(A$2:A2))

**davesexcel** · 11-15-2014, 06:31 PM

if G200XR was in A1 the formula below could be

Formula:

Please Login or Register  to view this content.

Then when you drag down the inside will change.

**etaf** · 11-15-2014, 06:33 PM

which ever solution you choose to use - make sure it works when the number is over 3 digits 1001

**TMS** · 11-15-2014, 06:56 PM

Please see the attached workbook which demonstrates all the formulae.

Regards, TMS

**Tony Valko** · 11-15-2014, 11:45 PM

Originally Posted by etaf

which ever solution you choose to use - make sure it works when the number is over 3 digits 1001

The OP said:

Originally Posted by lightsandsirens5

I cannot figure out how on earth to make it increase the three digit number in the center as I drag down.

So my suggestion only works correctly up to 3 digits.

Increasing a number in the center of a cell

LinkBack

Thread Tools

Rate This Thread

Display

Increasing a number in the center of a cell

Re: Increasing a number in the center of a cell

Re: Increasing a number in the center of a cell

Re: Increasing a number in the center of a cell

Re: Increasing a number in the center of a cell

Re: Increasing a number in the center of a cell

Re: Increasing a number in the center of a cell

Re: Increasing a number in the center of a cell

Re: Increasing a number in the center of a cell

Re: Increasing a number in the center of a cell

Re: Increasing a number in the center of a cell

Re: Increasing a number in the center of a cell

Thread Information

Users Browsing this Thread

Similar Threads

Printing multiple pages with increasing cell number

Copy formula with increasing cell number

Increasing number in formula when copying/pasting into another cell

Increasing a value in a cell by adding a new number to a connected cell?

[SOLVED] how do I paste without EXCEL increasing the cell number ?

Bookmarks

Bookmarks

Posting Permissions