Excel 2007 counting numbers

  • Thread starter Thread starter urlocaljeweler
  • Start date Start date
U

urlocaljeweler

In Excel 2007 I have a question...

I have a cell in a column B1:
MJTB1001

I need to paste to approximately 235 new rows in the column B1 as follows

MJTB2002
MJTB3003
MJTB4004... etc.

What formula should I use to accomplish this task?

Also I have the cell MJTB2044 and I need to make 235 new cells in column to
fit like this...

MJTB2044
MJTB3044
MJTB4044
MJTB5044 ... etc... 235 times....

Any thoughts?

Thank you.
 
In B2 put


="MJTB"&1001+(ROWS($B$1:B1)*1001)


copy down 235 rows, select the range and copy and paste special as values in
place



for the second use


="MJTB"&2044+(ROWS($B$1:B1)*1000)


repeat the copy and pasting.


Note that $B$1:B1 has nothing to do with the firs value being in B1 it just
generates row numbers

--


Regards,


Peo Sjoblom
 
one way.................

create 4 columns MJTB 2 00 2

extend MJTB all the way down
extend 00 all the way down
extend the other two columns from 2 to 235

concatenate the 4 columns into one
do the edit, copy, edit, paste special, values routine

similar solution for the second question

Greetings from New Zealand
 
Since you neglected to show what row 235 should be I can only guess.

Do you want the "00" retained all the way down?

="MJTB" & ROW() & "00" & ROW() entered in B1 returns 23500235

Or simply format to "000" as you progress?

="MJTB"&ROW()&TEXT(ROW(),"000") returns 235235

I would guess the latter.

For second scenario of MJTB2044 same guesses.


Gord Dibben MS Excel MVP
 
Back
Top