Range Breakup

  • Thread starter Thread starter trans
  • Start date Start date
T

trans

I am looking for a way to break up a range into separate cells. For example,
I have a range of zip codes in one cell that looks like this: 99359-99363 I
would like that to break into 5 cells, with values of 99359, 99360, 99361,
99362, and 99363.
 
hi
if the long zip is in A1 then in B1 put...
=left(A1,5)
then in C1 put
=B1+1
copy and paste in D1 and E1

worked for me
Regards
FSt1
 
That does work. However, there are 2000 of these instances, and they do not
all have the same length of range. Any suggestions for a case like this?
 
hi
i assumed that you example was like all the others. post other examples. you
may have to do a different left formula for each unique zip. lets see.

regards
FSt1
 
More examples:
99332 - 99333
99343 - 99350
99356 - 99357
99359 - 99363
99401 - 99403

The problem is that some only need to be broken out twice, others five
times; I could do the "left" method that you talked about, but I would have
to go through all 2000 records individually.
 
Okay, carrying on from FSt1's earlier suggestion, put the LEFT
function in B1, then in C1 put this:

=IF(B1="","",IF(1*RIGHT($A1,5)>=B1+1,B1+1,""))

The formula can be copied across the row for the largest range you
expect (i.e. into I1 in your example), then these formulae from B1 to
I1 can be copied down for as many entries as you have in column A.

Hope this helps.

Pete
 
Try this:

First, put your start/end values in A1:B5
99332 99333
99343 99350
99356 99357
99359 99363
99401 99403

Then....
D1: =MIN($A$1:$A$5)

This formula is broken into sections for readability
D2: =IF(MAX($D$1:$D1)=MAX($B$1:$B$5),"",
IF(ISNA(MATCH(D1,$B$1:$B$5,0)),D1+1,
INDEX($A$1:$A$5,MATCH(D1,$A$1:$A$5,1)+1)))

Copy D2 down as far as you need.

Note: if there are to be leading zeros, you'll need to reformat those cells
to show them.

Using your example, those formulas return:
99332
99333
99343
99344
99345
99346
99347
99348
99349
99350
99356
99357
99359
99360
99361
99362
99363
99401
99402
99403


Is that something you can work with?


--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
That's exactly what I needed. Thanks a lot.

Ron Coderre said:
Try this:

First, put your start/end values in A1:B5
99332 99333
99343 99350
99356 99357
99359 99363
99401 99403

Then....
D1: =MIN($A$1:$A$5)

This formula is broken into sections for readability
D2: =IF(MAX($D$1:$D1)=MAX($B$1:$B$5),"",
IF(ISNA(MATCH(D1,$B$1:$B$5,0)),D1+1,
INDEX($A$1:$A$5,MATCH(D1,$A$1:$A$5,1)+1)))

Copy D2 down as far as you need.

Note: if there are to be leading zeros, you'll need to reformat those cells
to show them.

Using your example, those formulas return:
99332
99333
99343
99344
99345
99346
99347
99348
99349
99350
99356
99357
99359
99360
99361
99362
99363
99401
99402
99403


Is that something you can work with?


--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
Click to expand...
 
You're very welcome.....I'm glad I could help.

***********
Regards,
Ron

XL2003, WinXP
 
hi
sorry it took me so long to get back. something came up i had to take care of.
but i see you have been taken care of by others. i haven't had a chance to
work on your examples but it would seem you have what you need.
sorry
FSt1
 
Back
Top