Fill Series - Advanced technique required!

[Matt]

Hi,

I have a spreadsheet that contains series of numbers in the format

00XX0000XX0 where 0 represents a number and X a letter.

When I use the Fill Series command, it naturally advances the last
number by 1 but the number I would like it to change is the four digit
0000 in the centre as the XX0 at the end remain the same. Is there a
way to make Fill Series make the middle four digits the series and
leave the suffix the same without going through several stages of
cutting and pasting or Concatenating?

Thanks, Matt.

 

[Bernie Deitrick]

Matt,

Not automatically.

However, you could set up a quick formula bases on row() or column()
functions.

00XX0000XX0
For example, if the middle four numbers should be 0001, and you are
starting in cell A2, use the formula
="00XX" & TEXT(ROW()-1,"0000") & "XX0"

Of course, you'll need to adjust the logic to get your actual 4
digits.

HTH,
Bernie
MS Excel MVP

 

[Matt]

Thanks for this - I don't quite understand how the formula works, but
it seems to do the job just fine! It would be a useful mod to a later
version of Excel if you coudl specify the section of a string to
advance by one in a fill series.

Cheers,

Matt