Convert Ranges to Numbers

  • Thread starter Thread starter Erin
  • Start date Start date
E

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
 
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
 
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. :-)
 
Back
Top