Convert Ranges to Numbers

[Erin]

I once saw a complex formula that took a range of numbers
and converted them to each number within that range in one
cell. The formula I am referring to took a list of
beginning and end numbers and then in one cell listed all
the numbers within that range.

For example one row might be:
Row A Row B Row C (formula result)
Beg End
AA0001 AA0008 AA0001, AA0002, AA0003, AA0004, AA0005,
AA0006, AA0007, AA0008

Notice every number ended up in one cell - but I actually
believe it took two or three columns to achieve this.

This formula could be copied down to an entire list of
Beg/End numbers in Excel:
Beg End
AA0001 AA0008
AA0009 AA0011
AA0012 AA0019
Etc.

I know Excel's Concatenation function was one part of the
formula. This was a very nifty formula and I can not
believe I did not hold onto it.

Can anyone come up with a formula for this - some of these
ranges could contain thousands of numbers.

Thanks,
Erin

 

[Jason Morin]

There's probably multiple ways, but here's a quick way
that I came up with off the top of my head:

1. Assuming "Beg" in A1 and "End" in B1, select cells
C1:K1.

2. Insert this formula and press ctrl/shift/enter:
=TRANSPOSE("AA"&TEXT(ROW(INDIRECT(RIGHT(A1,4)&":"&RIGHT
(B1,4))),"0000"))

3. Copy down the formulas down and do copy and paste
special > value over the formulas.

4. Select the columns C through K and replace #N/A with
nothing.

5. Put this in L1 and copy down:

=C1&", "&D1&", "&E1&", "&F1&", "&G1&", "&H1&", "&I1&", "&J1
&", "&K1

Of course this only works if the range for the 2 numbers
is no bigger than 9. You'll have to extend the formulas an
and ranges to get more.

HTH
Jason
Atlanta, GA

 

[Ken Wright]

You don't by any chance mean something like the following:-

Range of numbers, say A1:K10

In any other cell, type = and then select the range A1:K10, and then whilst
still in edit mode, (ie before you hit Enter), hit F2, then F9, then hit Enter.
Now take a look in the cell.