I Need a Formula to Auto-fill Phone Numbers in a Range

[twd3lr]

My spreadsheet looks like this (from A1-D1):

NPA(area code) NXX(prefix) Start(xxxx of 1st phone#) End(xxxx of last
phone#)

555 828 8900 8905

How can I take that data to create (in another set of columns)
something that looks like this:

555 828 8900
555 828 8901
555 828 8902
555 828 8903
555 828 8904
555 828 8905

Keep in mind that I will have anywhere from 20 to 200+ ranges of phone
numbers and that the ranges will consist of anywhere from 5 to 100+
numbers. I have a macro that will do the opposite (take individual
numbers and roll them up into ranges), but I am not at all experienced
with these types of functions and desperately need the help, which
would be greatly appreciated!

Thanks!

 

[Guest]

Not sure, you might need a VB macro for something that like that.

 

[DC]

You can try:
A1(NPA)=555
B1(NXX) = 222
C1(Range_Start) = '0001
D1(Range_End) = 9999
E1 =CONCATENATE(NPA,NXX,Range_Start)

A5 (formatted as a Phone Number)
=IF(--(LEFT(E1,4))<=Range_End,E1+1,"")

Then copy A5 to A6, A7, A8... for as many numbers as you need. Note
that A6 should automatically change the formula to
=IF(--(LEFT(A5,4))<=Range_End,A5+1,"")

This may not help but I think it's a start...
dc

 

[Niek Otten]

In E1:

=IF(ROW()>INDIRECT("$D"&"$"&COLUMN()-4)-INDIRECT("$C$"&COLUMN()-4)+1,"",INDIRECT("$A$"&COLUMN()-4)&"
"&INDIRECT("B$"&COLUMN()-4)&" "&INDIRECT("$c$"&COLUMN()-4)+ROW()-1)

Copy 100+ rows down, 200+ right (be aware that 256 columns is the max)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[twd3lr]

Awesome! Thank you very much for your help (as well as everyone else's
suggestions)!