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
"trans" wrote:

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.

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?

"FSt1" wrote:

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
"trans" wrote:

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
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

"trans" wrote:

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?

"FSt1" wrote:

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
"trans" wrote:

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.

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.

"FSt1" wrote:

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

"trans" wrote:

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?

"FSt1" wrote:

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
"trans" wrote:

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.

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

On Nov 30, 8:10 pm, trans wrote:
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.



"FSt1" wrote:
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

"trans" wrote:

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?

"FSt1" wrote:

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
"trans" wrote:

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.- Hide quoted text -

- Show quoted text -

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)

"trans" wrote in message
...
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.

That's exactly what I needed. Thanks a lot.

"Ron Coderre" wrote:

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)

"trans" wrote in message
...
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.

You're very welcome.....I'm glad I could help.

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

XL2003, WinXP


"trans" wrote:

That's exactly what I needed. Thanks a lot.

"Ron Coderre" wrote:

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)

"trans" wrote in message
...
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
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

"trans" wrote:

That's exactly what I needed. Thanks a lot.

"Ron Coderre" wrote:

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)

"trans" wrote in message
...
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.